sometimes you forget the basics!

I linked up two tables in a query by PO#. Lets say table 1 has 3 occurances of a PO# and table 2 only has one occurance. In the resulting linked query the PO# will show up 3 times, how can I serparate the results on the query so that I'll know that table 1 has 3 occurances and that table 2 only has one.

If it means anything, the other rows in the query show the dollar amount for each table.



Thanks for any help!!!

Edited by: motorola1 on Thu Jun 2 10:59:57 EDT 2005.

Without jumping thru a coupla hoops, I don't think you can get there from here. I believe that Access creates a temporary table upon the join, and then works on the resulting data. If this is so, then the data from table 2 WILL be replicated X times depending on the number of key values in table 1.

However, having said this, could you create a select query which simply collapses the keyed fields in both tables and gives you a count of the field

SELECT [t1].f, Count([t1].f) AS Countf FROM [t1] GROUP BY [t1].f;


SELECT [t2].g, Count([t2].g) AS Countg FROM [t2] GROUP BY [t2].g;

then put these into your join query as tables and show the Countf and Countg.

It'll slow things down (based on the number and size of the records) but it should work.


HTH

I think you're going to need a UNION SELECT for this. You want the rows in the same recordset, yet you still want to be able to tell which table each row came from.

SELECT PO# (# is not a good idea in a field name), blah, blah, blah, "TableA" As Source
FROM TableA
UNION
SELECT PO#(ditto), blah, blah, blah, "TableB" As Source
FROM TableB;