duplicates throwing off update query

[xopherira]

I need to update a template but duplicates are throwing off update query. Source data can have mutilple part numbers of same, but he serial number is unique. The proble is with multiple of the same, the same serial number is updating to multiple part numbers that are the same, need to map to correct part number.

UPDATE roll_template INNER JOIN roll_source_data 
ON roll_template.[Part Number] = roll_source_data.PN SET roll__template.[Serial Number] = [roll_source_data].[SN];

pn  sn
______
xyz1   0001
xzy2   0002 
xyz2   0003 
xyz3   0004
xyz5   0005
xyz5   0006

[orange]

You really have to join on things that are equal/related in each table.

Do you understand Normalization?
Do you have a clear description of what you are trying to do in plain English/business terms?

[xopherira]

I somewhat understand normalization, but have ony been using access for 2 weeks. In english i want to use source data to fill in excel template with serial numbers and have them match to corrrect part number iwth no duplicates. I thought I was joining on equal things? How do I get rid of the duplicate serial numbers and keep duplicate parts. Should I query into a third table? Thanks

[orange]

If you are only 2 weeks into any database work --congrats!

Step back from Access and related jargon.

What would you do in plain English to resolve the issue? That is, what are the steps you would use (forget that you may not know how to code them for Access). You need a plan!

Look at this from the position of a reader of your post.

-we don't know your business
-we really don't know you or your experience (we know more now then we did at post 1)
-we haven't seen your data

If you could show us the table designs you've been given that may help.

I'm going to suggest you work through this tutorial before going further. The tutorial shows you a process for converting a "Business description" into a designed database to support that business.
You have to work through it but you will learn. The tutorial comes with a solution.

Spend 30-60 minutes on it and you'll learn the basics of database design. This may be the best use of 30-60 minutes you can make.
Good luck.

[xopherira]

Thanks orange. Is there a way to export and post table design, how would you post table design?

[orange]

You could do a screen capture (prtsc) which saves screen to clipboard, then paste into a graphics program or word or a pdf. Get you design on the screen--prtsc--paste to empty word doc -- save

To post you go to the Go Advanced button at the bottom of the reply screen, then manage attachments

Another link to Normalization and Access info for new users.

[xopherira]

Thats a problem considering its sensative data. Might need to mock up some fictitional data and post. Also forgot to mention, table is being created using the ImportExportSpreadsheet macro in access. Does this make a difference? Or do you mean the design view minus the data?

[orange]

Don't post anything confidential.
You can use terms like Donald Duck, Porky Pig....Hesa Payne for names,
Apple street, A Street, B Blvd... for addresses/cities to be safe.

We really don't need the exact data, nor a whole load of data ----just enough to show the issue and a few records for context.

As long as the data is in an Access database it should be fine.

I have downloaded 500 sample records from here for testing in the past.

[xopherira]

template_table.pdfsource_data_table.pdf


dummy sample of tables, source file contains serial numbers that need to be added to template for export.

[ssanfu]

If you could show us the table designs you've been given that may help.

Table design: open the table in design view

OR

post your relationship window.

BTW, shouldn't use special characters, punctuation or spaces in object names.
Be aware of reserved words (ex "Date", "Year", "Name") - shouldn't use as object names.

[orange]

?? I'm not sure what I'm looking at????

Do you have a series of Parts and each of those Parts has a specific serial number? That is each part is uniquely identifiable?

eg:

12.5 cf Maytag Refrigerator Model:KT-ChefMaster-SS Serial Numbers: 12015000 thru 12015999

Can you post a picture of your relationships window?

[xopherira]

The larger table is my template table that I need to update. In this table the serial number is blank and needs to be filled by source data table. As you can see from template table there are five models with same part numbers. The source data table needs to add correct serial number in template and keep relationship.

[xopherira]

orange, thanks for your help. I'm taking since you are asking for relationships, this is something I should create? I had a chance to look at tutorial but not completely as of yet. Did you see explanation of tables, is that enough info?

[orange]

I suggest you work through the tutorial mentioned earlier. Getting your problem/opportunity clearly defined is important. Then the process demonstrated in the tutorial can be applied to give you a design that matches your business requirements.