Two tables, many to many relationship, append fields (1 Viewer)

[PeterZ7]

Hi everyone,
I hate to be a bother but I'm in need of assistance. I have two Access tables (extracts from off the shelf software that I've imported into my database).

The tables share a common field (Rx_#) in a many to many relationship. The first table, (I'll call Table A) is currently used to do a multitude of financial analysis. Data is appended from a canned extract (that cannot be modified) each morning, queries and reports are run and management is happy.

We are now trying to further segment the existing analysis by adding two fields found in the second table (Table B). The fields are (Dr. Name and Office and for each Rx_# in Table B the values are consistent.....ie. each instance of a particular Rx_# has a specific Dr. Name and Office, ) What I'm trying to do is simply append the field values for Dr. Name and Office from Table B to Table A . When I try to do this in Access, it is adding new records to Table A which makes all the analysis incorrect.

Can someone provide suggestions on how I can accomplish this without making major changes to all existing queries.

Thanks so much for any guidance you can provide.
-Peter

 

[plog]

Can you demonstrate your issue with data? Provide 2 sets:

A. STarting data from your tables. Include table and field names and enough data to cover all cases.

B. Expected results. Show what you expect to end up with when you feed your system the data in A.

 

[isladogs]

1. To avoid problems, you should not use spaces or special characters in your field names. So both of these need altering:
'Rx_#' & 'Dr. Name'

What I'm trying to do is simply append the field values for Dr. Name and Office from Table B to Table A . When I try to do this in Access, it is adding new records to Table A which makes all the analysis incorrect.

2. What you want to achieve should be done using an update query
i.e. update the 2 fields in table A to the values stored in table B

However unless you are then intending to remove the fields from table B, you will end up with unnecessary duplicated data.

So therefore why not leave them in table B & just use a query to pull the data from both tables for your daily reports etc

 

[PeterZ7]

Ok. Per your request, I've thinned down the data with TableA, TableB, TableCDesiredResults and added my Access query and what results that query produces. I hope this is what you wanted from me and find it useful. As you can see, my query turns 29 records into 43. Doesn't seem like much, but I'm working with 500k records.

Thanks so much for your feedback.
Peter

 

[isladogs]

OK you've responded to Plog's post but apparently not to mine

Field names etc unchanged you really should modify these - if not now then later.

As you can see, my query turns 29 records into 43.

No I can't as you haven't included your query (append? / update?)

Also it isn't clear what you want to add from table B to table A to make table C

Back to you for more info

 

[PeterZ7]

Sorry, when I replied to plog I had not seen your questions. Thanks for getting back to me. Attached is an updated file with my problem.
Any suggestions would be appreciated.
Thanks!
Peter

 

[Galaxiom]

What I'm trying to do is simply append the field values for Dr. Name and Office from Table B to Table A . When I try to do this in Access, it is adding new records to Table A which makes all the analysis incorrect.

Records are appended. Values are updated. No surprise that you are getting extra records if you are running an append query. (I have not looked at your sample.)

Ridders already hinted where you are going wrong in point 2 of his first post.

If you want to add the fields to the table you would need to do that in Design View (or use an ALTER TABLE query) and then Update the values.

But also heed the advice not to duplicate the fields in the other table (unless you have a very good reason).

 

[PeterZ7]

Thanks for the input, but Im not sure I understand. I have changed table names in my test scenario (and tomorrow in all the tables in the production database) but both my queries are duplicating values in TableA which moves from 29 records to 43 in both query results. Im not attempting to do an append query. I simply want to add the results of those two fields from TableB to ether TableA or a new table, but I guess it's not quite that simple.

I appreciate your and everyone else's feeback and suggestions.

 

[Galaxiom]

both my queries are duplicating values in TableA which moves from 29 records to 43 in both query results. Im not attempting to do an append query.

An update query with an outer join will also append records if the matching record does not exist in the destination table. Maybe there is trouble with join criteria?

 

[plog]

Based on the data you have provided you will need a 2 queries to achieve what you want. Here's the first:

SELECT TableB_Subset.[Rx#], TableB_Subset.[Tx#], TableB_Subset.Adjudicated, TableB_Subset.Delivery_Method, TableB_Subset.Last_Name
FROM TableB_Subset
GROUP BY TableB_Subset.[Rx#], TableB_Subset.[Tx#], TableB_Subset.Adjudicated, TableB_Subset.Delivery_Method, TableB_Subset.Last_Name;

Paste that into a new query and name it '_sub1'. It makes all the records in TableB_Subst unique--you have a bunch of duplicate records. Next, to get the desired results, use this SQL:

SELECT TableA_Subset.rx_number, TableA_Subset.Adjudication_Complete, TableA_Subset.Product, TableA_Subset.TP_Code, TableA_Subset.TP_Name, TableA_Subset.Billing_Method, TableA_Subset.TP_Total_Amt_Paid, TableA_Subset.PT_Pay_Amt_Total, TableA_Subset.acq_cost, [_sub1].Delivery_Method, [_sub1].Last_Name
FROM TableA_Subset INNER JOIN _sub1 ON (TableA_Subset.Adjudication_Complete = [_sub1].Adjudicated) AND (TableA_Subset.rx_number = [_sub1].[Rx#]);

The above works on the data you provided. IF on the actual dataset it does not work, you will need to provide me more sample data to demonstrate any issues.