Combine multiple Excel sheets in Access

[Adcock1969]

Hi All,



First I'd like to say that I am new to Access.

I have two reports that I import into Excel.They both show the locations in the warehouse where our product is supposed to be located. Over years of lack of maintenance they no longer match.

What I would like to do is link these two sheets in Access so that the information can be updated as I progress and correct the information.
Both have a common row or field called Locations. The problem is that some of the locations doesn't exist in both of the sheets. One of the sheets also has duplicated records for the same location.

I assume that I need some type of query to accomplish this; but have no idea where to start.

i have copied and pasted some samples of the information in the spreadsheets as well as the results that I am looking for.

I would appreciate any help someone could give me.

Sorry, my copy and paste didn't look right due to word-wrap. I have attached sreenshots of the two spreadsheets and the needed results in Access.

[June7]

Are you able to establish links to the worksheets? If so, then try Find Unmatched query. The Query Wizard can help you build this.

[Adcock1969]

Sorry, I had to look up "find unmatched query" to see what it was. That looks similar to what I need; but I would also like to see the "matched" results as well.
Any suggestions?

[alansidman]

Create a query with the fields you wish to display and join the two tables on the common field. Run the query, and the results will be all situations where the record appears in both tables.

Alan

[Adcock1969]

Sorry to sound so green.
but could you tell me how I would do that?

[alansidman]

Its time to do some research. Here are a couple starting points.

http://www.databasedev.co.uk/queries.html

http://www.techonthenet.com/access/queries/index.php

Post back with issues you have outlining specifically what problems you are encountering.

You may want to get a book on Access and pore over it diligently.

Alan

[Adcock1969]

I tried joining the two tables but I end up with only the results that match in the common field.
I have come close to the results I need with a Union Query. But with that I am still missing the record entries for the common field (Locations) in the right side of the join. Here is the SQL of the Union Query I ran. Any suggestions.


SELECT [WMS Locations].[P CAP], [WMS Locations].[WMS DESCRIPTION], [WMS Locations].[WMS ITEM], [WMS Locations].Location, [JBA Locations].[JBA Item], [JBA Locations].[JBA Description], [JBA Locations].[U/M]
FROM [WMS Locations] LEFT JOIN [JBA Locations] ON [WMS Locations].Location = [JBA Locations].Location
UNION
SELECT [WMS Locations].[P CAP], [WMS Locations].[WMS DESCRIPTION], [WMS Locations].[WMS ITEM], [WMS Locations].Location, [JBA Locations].[JBA Item], [JBA Locations].[JBA Description], [JBA Locations].[U/M]
FROM [WMS Locations] RIGHT JOIN [JBA Locations] ON [WMS Locations].Location = [JBA Locations].Location
WHERE [WMS Locations].Location IS NULL;

[alansidman]

I tried joining the two tables but I end up with only the results that match in the common field.

I thought that was what you were asking for in the above thread.

but I would also like to see the "matched" results as well.

If you are looking for something different, then please be specific and indicate what fields you have and what tables and what you want the end result to look like. Perhaps attaching your database with a clear explanation would be beneficial.

Alan

[Adcock1969]

I'm sorry if I wasn't clear enough previously. I also want to thank you for all the advice and help. What I need is the matching & non-matching records from my "Locations" field from both tables ('WMS Locations' & 'JBA Locations'). Or if it's simpler to understand, I would like ALL of the records from the "Locations" fields in both tables. I have attached a sample database that has the "WMS Locations" table and the "JBA Locations" table and a table labeled "WMS & JBA Locations" with the required results. As always I appreciate any guidance that you can give me.

[June7]

This query will return ALL records from both tables, including records that are duplicates for the selected fields. Remove the ALL keyword to eliminate duplication.

SELECT "WMS" As Source, [P CAP], [WMS DESCRIPTION] As Description, [WMS ITEM] As Item, Location FROM [WMS Locations]
UNION ALL
SELECT "JBA" As Source, Null, [JBA DESCRIPTION], [JBA ITEM], Location FROM [JBA Locations];

Doesn't quite match your example output which returns only 5 records from the total 6 and does more than just return records from the two tables.

[Adcock1969]

Hi June7,
We use two different programs (WMS & JBA) that stores the locations of our products. Over years of no maintenance the locations in both systems no longer match. We will be using the results from the query so that we can compare the locations and products in both programs to see what needs to be corrected. As we progress, I will re-run the reports so that I can see the progress.
Thank's for the time and the query info. I will see if I can get that to work and will post with results.

[Adcock1969]

June7,
That's great. The Union you specified gives me all of the records that I need in one place. But is there a way for it to sort the information from the WMS table and the JBA table basically side-by-side as in the example that I've attached? And again thank's for all the help.

[June7]

Maybe. Your data sample shows that Location is unique in WMS Location but not in JBA Location. Is this consistent?

Try a Union query that will return all unique locations then join the two tables to the Union query. Here it is as one nested SQL.
SELECT AllLocations.Location, [WMS Locations].[WMS DESCRIPTION], [WMS Locations].[WMS ITEM], [JBA Locations].[JBA Description], [JBA Locations].[JBA Item]
FROM [WMS Locations] RIGHT JOIN ([JBA Locations] RIGHT JOIN
(SELECT Location FROM [WMS Locations] UNION SELECT Location FROM [JBA Locations]) As AllLocations
ON [JBA Locations].Location = AllLocations.Location) ON [WMS Locations].Location = AllLocations.Location;

[Adcock1969]

June7,

Sorry I'm just now replying. That was exactly what I needed. I can't thank you enough. I am now in the process of searching the net for sites where I can learn SQL. If you have any suggestions for a beginner please post or email me at Adcock1969@yahoo.com Again, Thank you for all your help.
Thanks to alansidman for his assistance on my journey too.
I will mark this thread as solved later tonight.

[June7]

Google: SQL tutorial

Here's a couple
http://www.w3schools.com/sql/default.asp
http://www.sql.org/sql-database/sql-tutorial/