Hi All,

I need (again) some help with populating a invoice table. Please read
first some table info:

Tables:

Timecard with fields (ID, customerno, date, hours....).
Invoice with fields (Ordernum, invoicenum, customer, amount....).

Sample data in Timecard table:

1, 11111111, 01-02-2013, 5
2, 11111111, 02-02-2013, 2
3, 11111111, 03-02-2013, 1
4, 22222222, 01-02-2013, 2
5, 22222222, 02-02-2013, 1
6, 12121212, 05-02-2013, 7,5
...

This is what i want to do in code (SQL?) (populate invoice table):

Invoice table:

Orderno, invoiceno, customerno, amount
--------------------------------------
1000, 2000, 11111111, 8
1001, 2001, 22222222, 3
1002, 2002, 12121212, 7,5

I hope it's clear what i want

Thanks in advance

Regards,
Hüseyin A.

Hüseyin


Where do Ordernum, invoicenum come from?

Regardless of that a good start point would be to use a script. The first line would be something like

SELECT customerno, SUM(Hours) INTO Memory\TempInvDetails FROM Timecard GROUP by customerno;

That would give you a temporary table with the summarised details for hours by customer to use in the invoice. The rest of it I can't help with until you answer the question

Roy Lambert [Team Elevate]

Hi Roy,

Great tip, I now know exactly how to deal with this issue

Btw. ordernumber + invoicenumber are max(value) +1 from the tables.

Thanks again.

Regards,
Hüseyin A.

Roy Lambert wrote:

> Hüseyin
>
>
> Where do Ordernum, invoicenum come from?
>
> Regardless of that a good start point would be to use a script. The
> first line would be something like
>
> SELECT customerno, SUM(Hours) INTO Memory\TempInvDetails FROM
> Timecard GROUP by customerno;
>
> That would give you a temporary table with the summarised details for
> hours by customer to use in the invoice. The rest of it I can't help
> with until you answer the question
>
> Roy Lambert [Team Elevate]