QUERY

[Guest]

I have a table with 1500 records (one record for each part number) and it is
25 fields wide. Each field is for a product but every field is not populated
for each record. I need help with QUERY to use for reporting how many
products (fields) use (are populated with) a certain part number (record ID).
An example is below:

ID Baby Child Teen Adult Senior
1 123 123 123
2 264 264 264 264
3 365 365 365
4 442 442

I want to use a QUERY to create a REPORTs on how many products use part 123
and what products they are. For example, part 123 is used in three products
(baby, teen, and senior).

Can this be done easily of do I have to manually make different tables.

 

[Phil Smith]

I would rearrange your table so it looks more like

ID category part#
1 baby 123
1 teen 123
1 Senior 123
2 baby 264
2 child 264
2 adult 264
2 Senior 264

What you are trying to with what you have is a mojor pain the butt.
What are trying to do with your database structured this way is extemely
easy.

Tust me on this one: The effort it will take to rearrange your database
will be well worth the effort. What you have right now is not very
functional.

Do the work now, save your bloodpressure later.

Come back once you hav done that, and the query to do what you wish will
be a piece of cake. (ooh cake...)


Phil

 

[Guest]

Hello Phil Smith,

Thank you for your advice. Before I get to work on reformatting the data I
would like to mention that I recently split the table into 25 separate tables
like the new example below. Can I use these new tables in an easy way or
should I forget about them altogether. I do not have any problem rebuiding my
table like you suggested. I just want to know if the work that I recently did
splitting out the records can be used in a similar manner. The new example
follows:

ID BabyTable
1 123
2 264
3 365

ID ChildTable
2 264
3 365
4 442

ID TeenTable
1 123
3 365
4 442

ID AdultTable
2 264

ID SeniorTable
1 123
2 264

etc...

Is this any more useful than the original table structure. Since I already
have this new arrangement of tables I would like to use it if this is easier
than rearranging the data again. If this is still difficult than I will redo
it and take it as another lesson learned.

Thanks for your patience and understanding!