Query help, gathering data

Question

Hello everyone,

I am using Access 2016 trying to gather some useful data from a garbage table..

I have searched around for days now and either I have not found the correct question, or the answer does not exist...

I have an interesting data set that was built by an outside party and I am trying to make sense of it and gather the data in some meaningful way to display it using a pivot table in Excel. or perhaps some access report where I can display each record and supply a couple editable fields from a new table to re-write the information.

The source data is as follows - arrrggg the forum will not let me attach images or links...

so if you goto drive . google . com and type in /open?id=1TBTsMKl4uSvMn7hleaZokRRGkH4YBavt you will get a better idea of the following

Item Code Title Description Opt1 Opt2
12345 21 Main Widget Silly Widget 1 5
12345 21 Main Widget Same Widget, diff txt 2 3
12345 21 Main Widget related to widget 5 2
12345 18 Main Widget Silly Widget 1 5
12345 18 Main Widget Same Widget, diff txt 2 3

So, the deal is that the description will change based on opt1 and opt2

Code signifies the same item in a different location which may or may not have unique data.

What I would like to gather would be something like this...

Again google drive and use /open?id=1KmiUXJn6X6UlEJbuZcuxiJ9wQRoLtDGm

Item Code Title Opt 15 23 52
12345 21 Main Widget Silly Widget Same Widget, diff txt related to widget
12345 18 Main Widget Silly Widget Same Widget, diff txt

The unused field should remain empty but the data still needs to be gathered for the remaining fields in the row

Opt1 uses 0-5 and opt2 uses 0-15 so I presume the final output would be something like...

Item, code, title, and then followed by a lot of cells with the data

google drive /open?id=1-pxvLdy8vCmbHDIC5HEfo1OIOTF98vvl

Code Item Code Title opt 00 opt 01 opt 02 opt 03 opt 04 opt 05 opt 06 opt 07 opt 08 opt 09 etc...

In actuality all option codes are not being used i.e. opt 2 only uses 2-3-4 with the exception when opt1 uses a 0

Or should I approach this some other way?

Sorry I am self taught so I am sure I have minimal idea of what I am doing.. :)

Any help , pointers or links are greatly appreciated.!

--- by experimenting I see i was on the path to really mess up the data...

I am not sure the best way to proceed.. I am currently separating out this jumbled mess into separate tables for each option... my current plan is to create a report in access using these various tables on the screen and to then provide related data for a full evaluation of the data.  any suggestions?

Answer 1

Here is the data from the links you provided:

It appears you have 'Items' that may have multiple 'Codes' and each 'Code' may have multiple 'Options'. Further, each 'Item' has a Number,  a 'Title' and a 'Description'. Is this correct? If not, please tell us what the data hierarchy is. Once that is established, we can move on.

Answer 2

thanks for the links

I'm having a hard time making sense out of it overall but this is where I am at.

Every ITEM, can be assigned multiple CODEs - kind of like a category, I consider ITEM to be top level but???

For every CODE there is a Title, Description, Option1, Option2 and some other misc fields that are not used..

The title may change between codes but within each code it is the same.

The description will change based on the options, i.e. option combo 1-1 description= Tank option combo 1-5 Silver

Sometimes an option is related to the CODE and sometimes it is related to the ITEM which is why I continue to be so confused...

I am trying to separate out the overall specific ITEM details from the generic CODE details.

I tried separating out the various code combinations and the descriptions, but if I try to build them back into a table I get duplicated records..

Answer 3

If you build your tables properly, then you won't have duplicate records but COULD have duplicate Item Numbers. That's not the same thing as duplicate records because each new table record has a seperate and unique ID. Try building the following tables and fields:

Table Name: tblItems

• Field Name: ItemID         Data Type: AutoNumber        New Values: Increment          Indexed: Yes (No Duplicates)
• Field Name: ItemNum     Data Type: Number               Indexed: Yes (Duplicates OK)

Table Name: tblCodes

• Field Name: CodeID         Data Type: AutoNumber        New Values: Increment          Indexed: Yes (No Duplicates)
• Field Name: CodeNum      Data Type: Number              Indexed: Yes (Duplicates OK)
• Field Name: ItemID         Data Type: Number              Indexed: Yes (Duplicates OK)

• Field Name: Title              Data Type: Text
• Field Name: Description    Data Type: Text
• Field Name: Option1         Data Type: Number              Indexed: Yes (Duplicates OK)
• Field Name: Option2         Data Type: Number              Indexed: Yes (Duplicates OK)

Table Name: tblOptions

• Field Name: OptionID                  Data Type: AutoNumber        New Values: Increment          Indexed: Yes (No Duplicates)
• CodeID                                       Data Type: Number     Indexed: Yes (Duplicates OK)
• Field Name: OptionDescription      Data Type: Text (such as Silver etc.)

Remember this is not a spreadsheet. Data is held in individual tables and relationships are built between those tables.

You will have a one-to-many relationship between tblItems and tblCodes based upon the ItemID field in each table

You will also have a one-to-many relationship between tblCodes and tblOptions based upon the CodeID field in each table.

Once the tables are properly built and the relationships established using the Database Tools--> Relationships window, then you can input test data into the tables to see how it looks and create forms and reports for data input and reporting.

I must sign off for the day today, but will look tomorrow for any further questions or comments.