Initial Design and Querying

[TheBrigg]

Hello All,

I will try to keep this simple. What I would like to do, is store a list of model numbers in MS Access with some relating data, and then output it in a particular format through Excel.

What I need help with: I have model numbers similar to 070841. Some are completely unique, and others are essentially the same as a different model. I am trying to find a way to keep models listed individually in the same Access table, but link the related models by ID number. I am having trouble finding a good way to do this and still be able to get relevant data by passing a query to Access. It is important that I use Access to do as much of the manipulation as possible.

Example:
01000 Unique (10)
01001 Same as 01002 and 01003 (3)
01002 Same as 01001 and 01003 (2)
01003 Same as 01001 and 01002 (7)
01004 Unique (4)

Query values from table, return as:
01000 - 10
01001/01002/01003 - 12
01004 - 4

I have a lot of experience with Excel and VBA, but my knowledge of Access and SQL are relatively limited. Most work I've done was manipulating data on the client side, but this particular project does not permit that.

Thank you for any assistance.

[NTC]

the database solution is called grouping, and in your case most likely using an aggregate query or using the report object feature.

you must add another field to you table which would be the 'Group' field (or whatever name makes sense to you)

01000 01000
01001 0100X
01002 0100X
01003 0100X
01004 01004

it's difficult to be too precise not knowing your app or user interface but hopefully you get the jist..... the second field exists to allow you to group.

hope it helps.

[TheBrigg]

Thank you for the reply.

The information you gave me helps, I hadn't even thought about grouping and using sum functions etc. and that will work great. I had some other questions, but I thought of an idea while typing this out.

Thank you again.