Data organising

[gumby]

Hi,

Currently I have a Form linked to a table for data input.

Example of data in the table below...


What I would like to do, is access to generate
A table for each 'Sub-Category' type, with the categories as Field names and the data sorted into these....
eg.
a table for 'Consumables' that would have a Field name of Fire_Assay
with the Equipment items of 45e Std and 503b Std


I originally was doing this via excel with a array formula, but the amount of information has grown to the point where it is now impractical to use Excel for this purpose alone.

Is anyone able to suggest a way to go about this, or point me in the right direction? (I am in the learning stages for access databases)
I did try to do each list as a separate query, but given there is about 7 sub categories and 32 categories.........

[ranman256]

create a MAKE table query
bring the fields: SubCata, Equipment
put a label on Equipment to give it a new name (to look like): Fire_Assay:Equipment
turn off duplicates by setting the query property: UNIQUE VALUES = TRUE
run the query and tell it to use the table name : Consumables

[CJ_London]

a table for 'Consumables' that would have a Field name of Fire_Assay
with the Equipment items of 45e Std and 503b Std

Bit confused - in the context of Access, you already have normalised data, doing what you ask will move you to an un-normalised state. Which may be OK for Excel but makes life difficult in Access

You will be building into the design of tables a classification based on table and column names e.g.

tblEquipment
Metallurgy...Filtering_SG..Safety

If I've totally misunderstood, I apologise, if not, perhaps explain why it is impractical

[gumby]

Thank you for your reply, I tried what you suggested and I ended up with a table, with just 2 columns.


I created a simple test example (so I have something small and simple to test with any suggestions)
Original Data


Output I am chasing

(listing for the 'Car' Subcategory)

Bit confused - in the context of Access, you already have normalised data, doing what you ask will move you to an un-normalised state. Which may be OK for Excel but makes life difficult in Access

You will be building into the design of tables a classification based on table and column names e.g.

tblEquipment
Metallurgy...Filtering_SG..Safety

If I've totally misunderstood, I apologise, if not, perhaps explain why it is impractical

Long story short, eventually some tables in the database will be linked to excel, and trying to get it in a state easier to work with in excel, as doing this in excel seems to cause a high number of processes and a rather large spreadsheet (file size)

[CJ_London]

Quite simply, don't think you can do this in Access as a single view, Only way I can think of is to have multiple select queries, one for each category/subcategory, which you then copy and paste individually into excel - or each column in Excel links to the relevant query

[Bulzie]

Just from that last example, maybe filter the query to start to get rid of Bikes and then look at doing a crosstab query. In Design, select Query Wizard button and Crosstab.

[gumby]

Thank you everyone for your comments & suggestions. I ended up using a combination of query types (normal and cross tab) to get tables which I could then use Excel to format with minimal number of processes.