Query Multiple Fields

[Dr_Worm]

Hi

I have a table with the fields “Level, Room, Type, Component1, Qty1, Component2, Qty2, Component3, Qty3….”

I need to create a query (or whatever is needed) to create a recordset with:
Level, Room, Type, Component1, Qty1
Level, Room, Type, Component2, Qty2
Level, Room, Type, Component3, Qty3….
And if Qty is Null no record is created.

 

[bat17]

[Sermon on] If you really have created a table with a list of components with matching Qty fields you probably need to reconsider your table design if you have not committed to far.
It would be best to have one table with a Key field, Level, Room, Type and another with the Key field from the first table and Component and qty. when you link these together you are able to have as many components numbers as required. This means when someone decides that they need one more than you have allowed for (inevitable!) you don't have to re-write have the database to accommodate it!
Web Monkey has an article about table relationships that is worth a look [/Sermon Off]

To achieve what you want with the existing layout I think that you will need to create a query for each component number and then join them all up again with a Union Query.


HTH

Peter