Results 1 to 4 of 4
  1. #1
    cathstorey is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2012
    Posts
    2

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

    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!

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,849
    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

  3. #3
    cathstorey is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2012
    Posts
    2
    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.

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,849
    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

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Report Generates Duplicate Lines
    By Perceptus in forum Reports
    Replies: 12
    Last Post: 11-20-2012, 05:19 PM
  2. Replies: 2
    Last Post: 06-10-2012, 10:25 PM
  3. Replies: 5
    Last Post: 06-01-2012, 03:59 PM
  4. Replies: 5
    Last Post: 12-06-2010, 10:15 AM
  5. line chart not showing lines
    By ajetrumpet in forum Reports
    Replies: 0
    Last Post: 09-11-2010, 05:55 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums