Matching records in tables using query (1 Viewer)

[ReneeB]

Ok, I had this working once but now I can't replicate it.....
I am comparing 2 tables, trying to find matches on a specific field then list the information from the second table. My issue is that each occurance returns data, not just the first occurance. I did a little research and found that I could use Unique Values on the join - so I set it to Yes. I think this worked once, now I'm not sure.....

Here's what I did

Table 1 joined to Table 2 on MR# (Not a key field in either table)
Join properties - Unique Values: Yes

Table 1:
64820 06/01/2014 356911765 06/02/2014
64820 09/20/2014 375921754 09/25/2014
64820 12/04/2014 385601865 12/15/2014

Table 2:
64820 3/14/2014 323401765 3/15/2014

My result:
64820 3/14/2014 323401765 3/15/2014
64820 3/14/2014 323401765 3/15/2014
64820 3/14/2014 323401765 3/15/2014

When I had this working there were 33 records returned, now there are 45. The extra 11 are from this lovely issue.

I'm sure this is pretty easy but for some reason I'm not seeing it. Any help is greatly appreciated.
Thanks!
Renee

 

[CJ_London]

suggest post your full sql - bits of it are not enough to offer a solution. So far as I am aware 'unique values' is not a join property but a query property.

Also not clear of the purpose of your query - the result is showing data from table 2 only, so why do you need to join it to table1?

 

[plog]

Make it an aggregate query--cluck the Sigma/Summation symbol. GROUP BY every field.

 

[ReneeB]

Thank you both for answering.

CJ -
I am doing this in a query, I didn't write any code.
I am looking at patients that were referred during a specific time frame - say, 3 months, which is the data in Table 1. I am then comparing the MR# to those in Table 2, which is all of the referrals during the 6 months prior. If the MR# is found in Table 2 I want the detail (from Table 2) returned. I'm getting the correct information, just too much of it. So, if the patient was referred 3 times in Table 1 but only once in Table 2 I get 3 lines of the same data (again, from Table 2) as a result. How do I filter Table 1 within the query to eliminate duplicate MR#s?

Plog - same result. I changed the Total field to First, also same result.

Thanks again,
Renee

 

[CJ_London]

if by 'doing it in a query' means you are using the query GUI, all this does is create SQL code - click on the sql option (top left of the ribbon) and you will see the code, just copy and paste it into your post

 

[plog]

Plog - same result. I changed the Total field to First, also same result.

What Total field? Your fist post mentions nothing of the sort.

 

[BitsOfBytes]

Table 1 joined to Table 2 on MR# (Not a key field in either table)
Join properties - Unique Values: Yes

Table 1:
64820 06/01/2014 356911765 06/02/2014
64820 09/20/2014 375921754 09/25/2014
64820 12/04/2014 385601865 12/15/2014

Table 2:
64820 3/14/2014 323401765 3/15/2014

My result:
64820 3/14/2014 323401765 3/15/2014
64820 3/14/2014 323401765 3/15/2014
64820 3/14/2014 323401765 3/15/2014

When I had this working there were 33 records returned, now there are 45. The extra 11 are from this lovely issue.

I'm sure this is pretty easy but for some reason I'm not seeing it. Any help is greatly appreciated.
Thanks!
Renee

Unique values will not work with all the fields you have chosen, because the MR# values are the same, but the rest of the fields are are actually all unique (the dates and such).

You can create two queries which might make it easier. Create one [query1] that links table one and table two by MR# only. Only select the MR# from table two as your results and leave unique values on. This will return all the MR#s that have a match from table one and display them one time.

You could then create a second query which has the MR#s from the first. Link [query1] MR# as a relationship with table two MR# and select all the fields you wish from table 2 on this query.

 

[ReneeB]

CJ - don't have that option on my ribbon, don't know what GUI is.
Plog - you suggested using Group By, it has a "First" option on the total line. Neither worked.
Bits - even though I had it working once I took your suggestion and made a second query to pull the duplicates out of Table 1. It worked perfectly, thank you.

 

[CJ_London]

CJ - don't have that option on my ribbon, don't know what GUI is

GUI stands for graphic user interface. You say you have a ribbon in which case it will look something like this - SQL is quite clear on the left. Perhaps you are using an old version of Access that predates the ribbon?

 

[plog]

Plog - you suggested using Group By, it has a "First" option on the total line. Neither worked.

It has lots of options. I never spoke of any but GROUP BY.