HELP!!! My Query returns multiple copyies of the same record 2

[bgreenhouse]

This is fairly complicated, so here goes: I have find unmatched queries that find data that is in one table, but not in another.&nbsp;&nbsp;From these queries, I have queries that find fields that have a date that is older than five days ago.&nbsp;&nbsp;I have about seven of each of these queries, and now I want to summarize them all in a report. To do this I created another query that pulls the data from the previous queries (files older than 5 days), and groups it together.&nbsp;&nbsp;This query however returns the first record but many many copies of it.&nbsp;&nbsp;I get more than 4000 rows of the same record over and over, when I should get about 150 DIFFERENT records returned.&nbsp;&nbsp;Sorry if this is garbled, I can clarify if needed.<br><br>Ben

 

[DougP]

The reason you are getting so many records is you have a many to many relationship going on.<br>Access is finding every mathc in one table or query with every match of another. Thus 4000 records.<br>Try an intermediate step of running a query and appending records to a new table. So each query adds records to the same table.<br>That way you are working with one table instead of 5 queries.<br>Maybe???

 

[bgreenhouse]

Good idea, but the problem is that I need the records sorted according to which table they originally came from.&nbsp;&nbsp;If I appended them into a new table, wouldn't I lose that function?&nbsp;&nbsp;I was hoping to be able to pull the data from original query from the summary query to put it into one report...&nbsp;&nbsp;I think you're right about the relationship though, although how to get around it, I don't know....Any more suggestions?<br><br>Ben

 

[elizabeth]

So when you create the table, include an expression in each (such as FromTableName: Table1) to create a new field with the table name. Alternately you can build relationships between tables and queries in the query table.

 

[bgreenhouse]

Elizabeth:<br><br>That sounds promising, but I am not sure how to go about doing that..&nbsp;&nbsp;Would I create a field in the original data table (where all the data is stored) with the above expression, or would I make it in the queries drawing from the tables, or would I put it in the make-table query?&nbsp;&nbsp;I don't have much experience using expressions other than cliches.<br>

 

[elizabeth]

Key the expression on the first line of an empty column in the query grid. The part before the colon (no colon jokes please) is the name of the query's column, the part after the colon is the literal value you want in there. So that query has a column named FromTableName and every row/record/item in the column will have a value of Table1. Change the literal to match the name of the table you are running each query against, each time you run it. Clear as mud?&nbsp;&nbsp; <br><br>BTW, go look at your queries in SQL View (View menu) after you build each one. You may find this an easier way to work with them once you see the pattern.

 

[bgreenhouse]

Crystal Clear!&nbsp;&nbsp;Thanks<br><br>Ben

 

[bgreenhouse]

Alright, I may be asking some downright stupid questions at this point, ones I could probably figure out on my own in a day or two, but you are all so darn helpful that i'd rather ask it here.&nbsp;&nbsp;When I do what Elizabeth suggests (as above), I get asked to input a parameter for Table 1 (because Access insists on changing FromTableName: Table1 to FromTableName: [Table1]).&nbsp;&nbsp;I don't know how to set a default parameter value in a query.&nbsp;&nbsp;I know how to do it in a datasheet, but not in a query.&nbsp;&nbsp;Any suggestions?

 

[elizabeth]

I'm sorry, I just haven't done this in awhile and gave you bogus instructions because I thought I remembered it too well to test first (bad! very, very bad!). The literal value should be enclosed in quotes:<br>FromTableName:

 

[elizabeth]

I'm sorry, I just haven't done this in awhile and gave you bogus instructions because I thought I remembered it too well to test first (bad! very, very bad!). <br>The literal value should be enclosed in quotes:<br>FromTableName: &quot;Table1&quot;

 

[bgreenhouse]

Alright Elizabeth, what you suggested works, and is a much better idea than my original idea of building a report off of a query.&nbsp;&nbsp;Unfortunately, the make-table (or append) query I run to make the large table still has the problem of an infinite number of repetitions of the first row of the first query listed in the append query (the original problem).&nbsp;&nbsp;DougP's suggestion about the relationships sounds like it's on the right track, but I can't seem to find anywhere that there are relationships defined between these queries.&nbsp;&nbsp;The queries I wish to build this report off of don't have this problem (infinite repetition), so I don't think it is a fundamental problem with relationships in the tables containing the underlying data.&nbsp;&nbsp;It just comes into play when I try and group the data amassed by the seven queries telling me which of the unmatched records are older than five days using another query (either a make-table, append or select, it doesn't matter).&nbsp;&nbsp;Any suggestions? (I really appreciate all the help so far)<br><br>Ben

 

[elizabeth]

You can define new relationships between queries (or tables, or both) in the query grid's window. Just like in the db relationship window, click on the PK in one table or query and drag to the FK in the other. If you need more specific advice, pasting the queries that are not working here it might be helpful, because all the details about the steps that may now be working are kind of clouding things up for me.

 

[bgreenhouse]

Elizabeth, sorry I know that the last message wasn't exactly crystal clear.&nbsp;&nbsp;I understand how to define relationsips in queries, it's just that (as far as I know) there are none needed for what I want to do.&nbsp;&nbsp;Everything is working fine, and then I create a query that simply amasses the data from seven queries and puts it into one place (a table or report say).&nbsp;&nbsp;When I create this query (with no relationships between the previous ones as far as I know), I get infinite duplicates of the first row of the query.&nbsp;&nbsp;I'm not sure that it does have to do with relationships, but what DougP suggested sounded reasonable...Is this any clearer?

 

[elizabeth]

To make it simple, don't try to use one query to get the data from 7 other queries into one table. Create one append query (after you've created a new empty table). Modify and run it seven times.