Adding a Line number to duplicate lines with a one to many relation

[cathstorey]

Hi,

I am new to this Forum and I am thankful it is here! Anyway, I have uploaded a CSV file from a 3rd party company for our orders. In the file is a field OrderID Number which is duplicated based on the number of items on the sales order. There are no line numbers in the file. I was going to use the item number but about 35% of all orders have duplicate item numbers also, which is fine. I need a way to give each duplicate OrderID number an individual line number. I do not want to use the Access unique ID field and I would like for each 1st line to be either a 0 or a 1.

The Orders, in some instances, have multiple lines. The issue is that I have many Order ID numbers with multiple lines. In the CSV file the columns are all totals so, if I have 3 different items with varying quantities, I have a Total Price column, a Total taxes column and a Net Total column. The issue is, the total column is the total for the entire order, not the line; shipping cost and loss columns are the same way. This results in an overstatement. So if the order total was $120.00 and I had 3 items, $20.00, $30.00 and $40.00 each line would say that the shipping loss is $12.00 and taxes were $18.00 for the entire order, the total column states $18.00 for each line. When I run my query the Order total columns are $120 for order Total, Shipping Loss $36.00 and $54.00.

I would greatly appreciate any help with this. My skills in SQL and VB are very poor and I am not comfortable using them however, I am fairly strong in access. If this was not a repeatable download and append situation, I would use Excel.



Thanks so much and have a great day!

[orange]

What do you really intend to do with the data in Access?
What is the layout of the CSV file?
Post some sample data and the layout.

You could put the CSV data into a "holding table".
-design tables to contain the Orders and OrderDetails
- build queries to move the proper data to the proper tables, you will need multiple queries to populate the tables

[cathstorey]

Hi Orange,

It is very complicated and it involves 3 different shopping carts with multiple formats. I have all of the issues resolved and everything mapped, it comes over and merges well. My issue is that I have duplicate shipping loss as a result of not being able to remove the shipping loss from these duplicate lines. I need the shipping charges on one line only not on 3 or 5 or 10 lines. Does that make sense?

Here is an example of the data Note the shipping cost and loss columns. There is no way to separate the values without a line number. Shipping charges are variable based on about 60 factors. The Total shipping charge was 6.95 and the shipping loss was $5.00 for the entire order not each line.

OrderId	ShippingMethod	OrderDate	ProductsOrdered	SKU	price	qty	Total	Discount	ShipToCountry	ShippingPrice	ShipLoss
21121945	United States Shipping	01/19/2011	aaa	12556	100	1	335.85	0	United States	6.95	5
21121945	United States Shipping	01/19/2011	bbb	13897	19.9	2	335.85	0	United States	6.95	5
21121945	United States Shipping	01/19/2011	ccc	82574	204	1	335.85	0	United States	6.95	5

Thanks so much for your help.

[orange]

You have 1 order (21121945) with 3 Products (aaa, bbb, ccc)
Each Product has a SKU Price and Quantity.
The Shipping Method, Total, ShipToCountry, ShippiingPrice and ShipLoss are attributes of the Order.

The issue you are facing is common. The data you are showing is NOT NORMALIZED. You must Normalize your tables to make sense of the data and to do meaningful queries and reports.

Please see the free data model to show Orders and OrderItems(Products)
http://www.databaseanswers.org/data_...ries/index.htm

These free video tutorials describe the processes of data modelling, normalization and entity relationship diagramming. There are other videos, but this group is by the same presenter and covers an example situation.

http://www.youtube.com/watch?v=IiVq8M5DBkk Logical data modeling

http://www.youtube.com/watch?v=BGMwuOtRfqU Candidate key

http://www.youtube.com/watch?v=ZiB-BKCzS_I Normalization

http://www.youtube.com/watch?v=pJ47btpjAhA Normalization example

http://www.youtube.com/watch?v=q3Wg2fZENK0 1st Normal form

http://www.youtube.com/watch?v=vji0pfliHZI 2nd Normal form

http://www.youtube.com/watch?v=HH-QR7t-kMo 3rd Normal form

http://www.youtube.com/watch?v=q1GaaGHHAqM E_R Diagramming

http://www.youtube.com/watch?v=lXAGQ8vmhCY ERD Part 2