Query against 4 reports with Weeke Ending dates

[unc2plo]

I am making a vendor scorecard that tracks several metrics about each of my vendors. I have append table queries that track all the data, and individual make table queries that pull the data for each vendor and assign week ending dates to it. When I run the make table queries I get exactly what I am looking for. The 5 weekending dates and the data associated with each date.

But when I try and pull the metrics from each table into a query to make a report from I get hundreds of lines instead of just teh 5 dates and teh data from each table.

Am I missing something where weekending dates are concerned?

Thanks,
David

 

[Corticus]

Hi unc2plo,

Sounds like your ending up with a Cartesian product, which is basically every possible combination of data from the multiple tables. You need to join the tables, that is, look at your query in design view, and identify a primary key in your 'main' table, the table that has most the info you're interested in. Then click on the field and drag it over to the related field in the other query, now you should just get values from each table where they are equal on that field.

For mor info on joins and avoiding cartesian products:

http://www.web4data.com/dbmgmt/activityguide/keyterms08.html

or check Access's help for:
query, joins, cartesian

Also, consider using the query wizard, it's helpful when you're learning this stuff.

If you care to post your SQL, that might help. Open your query in design view, then click on the little drop-combo where you select between data-sheet and design view for the query (right under 'file'), and select SQL. Then just cut and paste your SQL into your post.

HTH,