How JustAnswer Works:
  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site. Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.
Ask flopcat98 Your Own Question
flopcat98, Consultant
Category: Microsoft Office
Satisfied Customers: 9077
Experience:  degree in comp sci. 20+ years install work with networks for small and large companies
Type Your Microsoft Office Question Here...
flopcat98 is online now
A new question is answered every 9 seconds

Im trying to merge two data sets - one with serial numbers

Customer Question

I'm trying to merge two data sets - one with serial numbers listed multiple times for different dates of use, and one with product inventory (so, serial numbers listed only once) and the entity to which they belong.

Data set 1:

Date Serial Number
11/22/2012 XXXXXXXXXX
11/23/2012 XXXXXXXXXX
11/22/2012 XXXXXXXXXX

Data Set 2:

Serial Number Entity XXXXXXXXXX 1 XXXXXXXXXX 5

What I want to end up with is:

Date Serial Number Entity
11/22/2012 XXXXXXXXXX 1
11/23/2012 XXXXXXXXXX 1
11/22/2012 XXXXXXXXXX 5
Submitted: 4 years ago.
Category: Microsoft Office
Expert:  flopcat98 replied 4 years ago.
I see no easy way to do that if it is listed the way you say it is.

that is if you have multiple fields that have to turn into multiple records.

the way it should have been set up is
1 record for the inventory

multiple records for the sales

xxxy price vender

xxxy sold 10 yy/mm
xxxy sold 20 yy/mm

from THAT
you can do a query and click on the join where you link the tables
and check the box for all records from sale and only those from inventory that match. a 1 to many join is easy to do.

if you have a table that says
xxxy sold 10 sold 20 sold 30
xxyy sold 13 sold 0 sold 40

that is just bad data design. it wont work in a database its the way people do excel because they dont know how to do a database and it has tons of limitations. You just ran into one.

about the only thing that you can do is export the data to a new file one field at a time. putting field a into the sold field to one table
then putting field b into the same stucture in another table sold field then field c etc. and them appending the 3 tables into 1 table and using that new table to do the simple match
Customer: replied 4 years ago.

Unless I'm misunderstanding your answer, I think my tables actually do match the first example you gave - one table contains Serial Number and Location (i.e. inventory), the other contains Serial Number and Date Used (i.e. each line contains 1 date and 1 serial number)


So, how do I do a 1-to-many match? I've already created 1-to-many relationships among the tables using serial number - I'm just missing the last step of joining. I've tried joining once before, and just got a drop down box, which is not what I want. I want a result with all records listed and the field fully populated.


Thank you for your help...

Expert:  flopcat98 replied 4 years ago.
create a query and add both tables to it

then just click on the field you want to match on in one table and drag it to the field that you want to match with it on the other table

a line will show up connecting the two tables
Customer: replied 4 years ago.

Thanks - I've already created the relationship. I want to merge the tables so that I end up with one table that has all fields (Serial Number, Date Used, and Location).


When I run a query, I'm able to get a result that has those fields but has only drop down boxes instead of populated fields in a table. I don't want to use this for look-up, I want a summary table that I can run data analytics from. What am I doing wrong?

Expert:  flopcat98 replied 4 years ago.
create it as a select query first and make sure that it works and gets all of your data. then turn it into a make table query

NOTE If you already have a select query that produces the data that you need, go to the next steps.

On the Create tab, in the Other group, click Query Design.

Convert the select query
Open your select query in Design view, or switch to Design view. Access provides several ways to do this:
If you have the query open in a datasheet, right-click the document tab for your query and click Design View.
If the query is closed, in the Navigation Pane, right-click the query and click Design View on the shortcut menu.
On the Design tab, in the Query Type group, click Make Table.
The Make Table dialog box appears.
In the Table Name box, enter a name for the new table.
Click the down-arrow and select an existing table name.

Do one of the following:
Place the new table in the current database
If it isn't already selected, click Current Database, and then click OK.
Click Run , and then click Yes to confirm the operation.