Results 1 to 12 of 12
  1. #1
    PC404 is offline Novice
    Windows 10 Access 2007
    Join Date
    May 2018
    Posts
    3

    Unhappy Not sure what to title this..Please help

    Hello, new to Access, I am stuck on how to do this:

    I have a MasterData Table, I created two different queries from that MD (MasterData), one being the ItemNum (ex.B102) and the second being UPC (ex.024052412307).

    I need to create a final Table that will give me both the UPC and the ItemNum. Here is the tricky part explained below:

    This is the ItemNum query:

    ID itemNumber itemNumberQualifier
    2 PA300-003 BuyerAssigned
    10 PA300-012 BuyerAssigned
    18 PA300-013 BuyerAssigned
    26 PA300-023 BuyerAssigned
    34 PA320-001 BuyerAssigned
    42 PA320-002 BuyerAssigned



    This is the UPC query:

    ID itemNumberQualifier itemNumber
    3 UPC 024052402131
    11 UPC 024052402148
    19 UPC 024052402155
    27 UPC 024052402162
    35 UPC 024052402179
    43 UPC 024052402186




    Notice the ID, for the ItemNum its positive numbers while the UPC is negative numbers. The easier way to have done this is if the MD didnt have both the UPC and ItemNum under one column:

    Example:


    itemNumber itemNumberQualifier
    PA300-003 SellerAssigned
    PA300-003 BuyerAssigned
    024052402131 UPC












    PA300-012 SellerAssigned
    PA300-012 BuyerAssigned
    024052402148 UPC










    PA300-013 SellerAssigned
    PA300-013 BuyerAssigned
    024052402155 UPC


    All that empty space is more data thats just scattered on different rows, so for example, ItemNum "PA300-003" you will see the 3 parts to it:

    1. SellerAssigned (Not needed, its a repeat, 85% of the time, to "BuyerAssigned".
    2. BuyerAssigned (I am the Buyer, so I use this as opposed to the SellerAssigned.
    3. UPC (Very important)

    Between all the empty spaces is the information relevant to that ItemNum, example below:


    itemNumber itemNumberQualifier descriptionValue itemDescriptionQualifier itemDescriptionClassification itemFriendlyDescription itemFeatures unitOfMeasure value itemUnitDescription unitOfMeasure6 value7 unitOfMeasure8 value9 unitOfMeasure10 value11
    PA300-003 SellerAssigned













    PA300-003 BuyerAssigned













    024052402131 UPC















    Back Cushion/Loughran SellerAssigned Product Back Cushion
















    Each 1 Back Cushion/Loughran Inches 15.75 Inches 9.84 Inches 27.56







    Each 1 Back Cushion/Loughran Centimeters 40.005 Centimeters 24.994 Centimeters 70.002
































    PA300-012 SellerAssigned













    PA300-012 BuyerAssigned













    024052402148 UPC















    Ottoman Seat Cushion/Loughran SellerAssigned Product Ottoman Seat Cushion
















    Each 1 Ottoman Seat Cushion/Loughran Inches 26.18 Inches 5.91 Inches 26.18







    Each 1 Ottoman Seat Cushion/Loughran Centimeters 66.497 Centimeters 15.011 Centimeters 66.497
































    PA300-013 SellerAssigned













    PA300-013 BuyerAssigned













    024052402155 UPC















    Back Cushion/Loughran SellerAssigned Product Back Cushion
















    Each 1 Back Cushion/Loughran Inches 15.75 Inches 9.84 Inches 25.2







    Each 1 Back Cushion/Loughran Centimeters 40.005 Centimeters 24.994 Centimeters 64.008


































    I have no idea why they did it this way, I used to work on this in excel, but i heard its easier to work with Data like this in Access.

    The data has missing information as well, some UPC's dont have ItemNum and some ItemNum don't have UPC. Sometimes its missing a description, its missing dimensions, prices, etc.

    How do I get this organized, any help would be so appreciated that I am willing to make a donation.

    Here is a final example of what the first Item should look like:


    ItemNum UPC DescriptionValue Price ItemUnitDesc Value7 Value9 Value11
    PA300-003 024052402131 Back Cushion/Loughran 450.95 Back Cushion/Loughran 17.75 9.84 27.56


    I hope this made sense, I am not sure what this kind of condition or task is named within the Access/Data community, I have searched far and wide on google, I am not sure I am asking the right question I can provide the actual data if needed, thank you in advance.






































    fdf

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    It's called GIGO (Garbage In Garbage Out). There is nothing to indicate that ItemNum PA300-003 and UPC 024052402131 belong together. There is no relationship. There is no magic fix for this other than a lot of data editing. Build a table that establishes relationship between these two data entities.

    ItemNum UPC
    PA300-003 024052402131
    ... ...
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Gicu's Avatar
    Gicu is offline VIP
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi there,

    Your data is a mess, looks like some transactional data that was crammed into the table even if it made no sense. You will need to decide exactly what you need to extract from the existing data and how to modify, create your new db to go forward. If given enough info I am sure we can help you extract most of the data in a satisfactory way to populate your new tables. But you know your needs the best, so you have to give us more info (i.e. inches vs centimeters - extract both or only one and calculate the other, etc.)

    Cheers,
    Vlad

  4. #4
    PC404 is offline Novice
    Windows 10 Access 2007
    Join Date
    May 2018
    Posts
    3
    Quote Originally Posted by Gicu View Post
    Hi there,

    Your data is a mess, looks like some transactional data that was crammed into the table even if it made no sense. You will need to decide exactly what you need to extract from the existing data and how to modify, create your new db to go forward. If given enough info I am sure we can help you extract most of the data in a satisfactory way to populate your new tables. But you know your needs the best, so you have to give us more info (i.e. inches vs centimeters - extract both or only one and calculate the other, etc.)

    Cheers,
    Vlad
    Thank you for the feedback guys, I am aware of the data being a mess, its terrible, it took me 3 weeks to sort this data out in excel with scripts and macros, i do this twice a year, there are over 12k items. I am trying to upload this onto Amazon, so here are some of the required fields:

    ItemNumber (important)
    UPC (important)
    Price (100% markup, current prices in the data is dealer cost)
    Inventory (separate file that uses ItemNum, its easier to work on this when the data is fixed)
    Dimensions (inches)
    Weight
    DescriptionValue
    ItemUnitDescription
    Color
    Active (Active means that the product is not discontinued, they don't delete discontinued items)


    Other fields that i will include later are tax (simple formula), shipping to calculate shipping based on Weight, simply stuff) and two other fields that are not relevant, just to give you an idea of where i am going with this.

    Hope this helps, thank you again.

  5. #5
    Gicu's Avatar
    Gicu is offline VIP
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Can you upload an Access db with a small sample of your data?
    Cheers,
    Vlad

  6. #6
    PC404 is offline Novice
    Windows 10 Access 2007
    Join Date
    May 2018
    Posts
    3
    Here is a link to the file, I created different queries to try and get UPC and ItemNum separated, but still trying to find some sort of variable to link them together. I also included the xml file, this is usually how its downloaded.

    https://www.dropbox.com/s/k5rkuiqrgk...ter.accdb?dl=0 (Access File)
    https://www.dropbox.com/s/k5rkuiqrgk...ter.accdb?dl=0 (XML File)

    Thank you

  7. #7
    Gicu's Avatar
    Gicu is offline VIP
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    I think both links point to the Access file.
    Cheers,
    Vlad

  8. #8
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    PC404,
    You say you work with this data/application and perform some task(s) twice a year.
    You should spend a few minutes and write a description of what the"business" is in simple, plain English. By business I mean what this data and processes are in support of.
    Use simple terms --like you were talking to a 10 yr old who knows nothing of you, database or your environment--- avoid jargon.

    If you don't know the business well enough to describe it, you can't expect readers to guess what the data means and how it fits together.

    Here is a link to Database planning and design info.
    I recommend you spend some time and work through the tutorials from RogersAccessLibrary identified in the link.

    Good luck.

  9. #9
    Gicu's Avatar
    Gicu is offline VIP
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi,

    Would you please have a look a the attached file and see if that is what you were looking for. I had to delete a whole bunch of records as the file was not uploading on this forum, but you can bring your entire table (AshleyMaster) back.

    I have tried to add comments in the code to make it easy to follow, please ask any questions if you need clarifications. You will need to review the assumptions I made in the query and the module. To make it work open the form and click the button. Once you get the confirmation message open the temporary table (tmpAshley) and have a look at the data.

    Cheers,
    Vlad
    Attached Files Attached Files

  10. #10
    Gicu's Avatar
    Gicu is offline VIP
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Have you had a chance to review the file and the code I uploaded?

    Cheers,
    Vlad

  11. #11
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    vlad,

    I have a feeling the OP has gone to another source of info. Or has another priority. My instinct says you have done more analysis on this issue than the OP or his colleagues.

  12. #12
    Gicu's Avatar
    Gicu is offline VIP
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    orange, looks that way, but I had fun doing so its OK.

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

Similar Threads

  1. Hiding top title Bar
    By stu_C in forum Access
    Replies: 2
    Last Post: 09-20-2016, 08:01 AM
  2. Replies: 5
    Last Post: 08-03-2016, 04:36 AM
  3. Replies: 2
    Last Post: 03-20-2015, 06:30 AM
  4. Auto Title
    By Lois in forum Forms
    Replies: 1
    Last Post: 10-21-2011, 09:21 AM
  5. No useful title
    By cap.zadi in forum Access
    Replies: 1
    Last Post: 09-05-2011, 09:50 AM

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