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