query column heads

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table of locations with Items for fields, quantities of item
stored in the record. So any location could have a number for some, all or
none of the items.
I also have a table of items including the item number (a field in the
location table) description and unit of measure.
I would like to populate a list box through vba by setting the recordsource
to some SQL that would give me an Item, the description, unit of measure and
quantity for a selected location.
I can't see how to do this since the item in the location table is a field
and not a value.
I started making a table including the location item and quantity, but it is
all duplicated, seems a waste.
Any help is greatly appreciated.
Ken
 
If you're not stuck in your design then I would suggest this:

Locations:
location_id
location_name
location_description

Items:
item_id
description
unit_of_measure

Item_Locations:
item_id - related to item_id in Items
location_id - related to location_id in Locations
quantity

Then you can create a form that has Locations as it's record source,
then you create a subform with Item_Locations as it's record source.
This will show you all items in a given location.

Any more questions, don't hesitate to post back.

Cheers,
Jason Lepack
 
Back
Top