combining tables

[suepowell]

Hi everyone

I don't know if what I want to do is possible without writing code to create a new recordset, but it is this

I have a query with job id, month, and hours in the month and another with job id, month and amount invoiced.

I need to combine these two tables linked on month and job id.

My problem is that I need to report for all months where there are hours AND OR amount invoiced, but neither query will contain all of the required months.

ie can I combine

job 1 april 5
job 1 june 10

with

job 1 may £100
job 1 june £200

to give?

job 1 april 5 0
job 1 may 0 £100
job 1 june 10 £200

Thanks for any thoughts.

sue

 

[Pat Hartman]

Why not just do 1 query with job id, month, hours, and amount invoiced?

 

[Nouba]

I setup a quick example. In real life you should use Autonumbers and primary keys in your tables.

 

[suepowell]

Hi
thanks for the answers

Pat
I can't just do 1 query as I have the same problem, in that neither of the tables holds an entry about each month, and as far as I understand it when linking tables / queries in a query you can force all records from either one of the tables in the join or only those records with the joined field equal. What I want to do is force all records from both tables joined on job no, and month where this is equal in both tables.

Hope that made sense!

Nouba

Thanks for the download, I do in fact use autonumbers and primary keys for my tables, but I am having trouble knowing how to link things properly.

I have tried your sample, and it seems to do what I want.
If I understand correctly, do I have to create a table containing all the months I will possibly need , this database will obviously grow with time. If this understanding is correct it is a bit messy, in that it needs either loads of months, or checking every now and again.

Anyway thenks for the help

Sue

 

[Pat Hartman]

Union the two separate queries together. You'll need to add a dummy field to each part of the union since the recordsets need to be identical. So add a dummy hours field to one query and a dummy amountInvoiced to the other. Make sure these fields are the correct format. For example -

Select ..., AmountInvoiced, 0.0 as YourHours
...
Union Select .., 0.0 as AmountInvoiced, YourHours
..

Remember the columns need to be in the same order in both queries and the data types need to be consistant within a column.

Then use the union query to make a totals query where you sum the AmountInvoiced and YourHours columns. That will get you a single row for each instance of job, id, and month with the two numeric columns summed.

 

[suepowell]

Thanks Pat,

That makes sense, and should be just what I want.

Sue