Query using the same primary keys.

[Franuzz]

Hello, first post here!


I tried searching around the forum before posting this.
Basically, I got a new job and inherited a database that is all over the place. They are using Access 2003(upgrading to 2007 is coming soon).

I need a query that will combine multiple tables that have the same primary key.
Some of the tables and some fields.
Billing: jobID, Month, Hours, BillableRate (created a query to multiply these and sum per month)
Jobs:jobID, month, client,
Expenses: jobID, month, expenseAmt,

Basically I need the new query to columns with Client,JobID, Month, Bill (from the query where I multiplied BillableRate and Hours), expense, and a few others but these will do as a starting point. A lot of these tables have different dates associated with them, so I would have to return a 0 value if there is no expense associated with a jobID for the same month. So if jobID and month are the same, return a value for expense, Bill, and other things.

I tried many things, fiddling with dsum, and after many hours, come up empty. Is there something I'm doing wrong or overlooking?

Thank you in advance!

[jzwp11]

If there are multiple billing records with the same jobID in the Billing table (since you are summing them), that would imply that the JobID is a foreign key not a primary key. The problem comes in with the month field. First, the word month is a reserved word in Access, so it should not be used as a field name. Now for those jobs that span multiple years how do you know if the record with a particular month is for this year, last year of 5 years ago? You mentioned you had date fields, but you did not say in what tables. Also, I do not see the purpose of the month field in the job table.

In terms of getting the data you want, I would probably suggest that you create a new query that has the job table and your summing query and do a left join between the job table and the query using both the jobID and month fields. But, I don't know if that will work because of the year issue I mentioned above.