Query help

Phil McF · May 16, 2004

This is a last desparate attempt to write a single SQL query that will
satisfy the following.

From the BillinData table, select all JobNumbers that have had charges
entered since the last invoice date, the last invoice date and the sum of
all charges for each job since the last invoice job.

The DB is Access 97 and can't be changed.The BillingData table has, in part,
the following fields:
JobNo, Date, Code, Charge
The Code field contains the various chargeable codes as well as an "INV"
code when an invoice is entered.

I have searched the net, I have asked this question before in various
newsgroups, Ihave read what ever Help I could find in an attempt to resolve
this myself. All to no avail. I need help.

Thanks
Phil

PS : What do I do for a job that has never been invoiced ?

Jeff Boyce · May 16, 2004

Phil

From the BillinData table, select all JobNumbers that have had charges
entered since the last invoice date, the last invoice date and the sum of
all charges for each job since the last invoice job.

From where will this query obtain "the last invoice date" as a criterion?

Define "last" (you and Access may not mean the same thing).

The DB is Access 97 and can't be changed.

If needed, you could leave the data in that db and create a new one with its data, right?

The BillingData table has, in part,

the following fields:
JobNo, Date, Code, Charge
The Code field contains the various chargeable codes as well as an "INV"
code when an invoice is entered.

Does this mean more than one INV could be entered for a JobNo? What primary key are you using on this table?

I have searched the net, I have asked this question before in various
newsgroups, Ihave read what ever Help I could find in an attempt to resolve
this myself. All to no avail. I need help.

What have you already done in the way of writing queries? What worked/didn't?

Thanks
Phil

PS : What do I do for a job that has never been invoiced ?

Your description didn't include any information about where "job" information is stored -- do you keep anything before you have a BillingData record?

Phil McF · May 17, 2004

Jeff

By last I mean the latest date that an invoice was entered.
Creating a new DB is not a practical consideration as the DB is accessed by a VB6 programme using DAO 3.51
More than one invoice can be entered for any given job. Also a job may not yet be invoiced
The primary key is RecID
I have basically achieved a result by multiple queries

Find last invoice date SELECT BillingData.JobNo, Max(BillingData.Date) AS LastInvDate
FROM BillingData
WHERE (((BillingData.Code)="INV"))
GROUP BY BillingData.JobNo;

Use previous query SELECT BillingData.JobNo, InvDate.MaxOfDate AS Expr1, Sum(BillingData.Charge) AS SumOfCharge
FROM BillingData INNER JOIN InvDate ON BillingData.JobNo = InvDate.JobNo
WHERE (((BillingData.Date)>[InvDate]![MaxOfDate]))
GROUP BY BillingData.JobNo, InvDate.MaxOfDate;

This realy doesn't work. I have used information from MSDN articles, Google searches and any other source I could think of. I believe I need some sort of subquery but all I get is error messages, usually to do with the EXISTS keyword.

Cheers
Phil

Phil