Combo Box Problem

[Guest]

Hi guys,

I have a few very basic tables that i would like to combine into a query
that a combo box can lookup.

example:

Table : OilFilter
Fields: OilId (autonumber)
Oil Filter
Supplier

Table : AirFilter
Fields: AirId (autonumber)
Air Filter
Supplier

Table : FuelFilter
Fields: FuelId (autonumber)
Fuel Filter
Supplier

I'd like to have a combo box look these up collectively, by that i mean i
would like the Oil Filter, Air Filter & Fuel Filter to appear in the same
column in the combo box.

The combo box is located on a form called "PartNoForm".

Regards,

GregInOz

 

[Guest]

I'm not sure if that what you mean, but try a union query

Select OilId , [Oil Filter], OilFilter.Supplier From OilFilter
UNION
Select AirId , [Air Filter] , AirFilter.Supplier From AirFilter
UNION
Select FuelId , [Fuel Filter] , FuelFilter.Supplier From FuelFilter

The Union query will list them one after the other in one query

 

[John Spencer]

I think that you really need to reconsider this design. You should have one
table for all of these filters and probably you should have just a parts
table. Right now, you will have a problem when you attempt to select an
item from the combobox if there is any overlap in the autonumbers.

If the autonumber of 2 can be an oil filter or an air filter or a fuel
filter then when you select fuel filter (#2), the combobox may show the air
filter(#2) when you move to the next control. If you are storing the number
in another table (as in this filter type was installed (sold) in this
vehicle) then you won't be able to tell which filter type it was.

If at all possible build one table
Table: Filters
Fields:
PartID (autonumber)
PartType (Oil Filter, Air Filter, Fuel Filter)
PartName
Supplier

I'm not sure if that what you mean, but try a union query

Select OilId , [Oil Filter], OilFilter.Supplier From OilFilter
UNION
Select AirId , [Air Filter] , AirFilter.Supplier From AirFilter
UNION
Select FuelId , [Fuel Filter] , FuelFilter.Supplier From FuelFilter

The Union query will list them one after the other in one query
--
Good Luck
BS"D

Hi guys,

I have a few very basic tables that i would like to combine into a query
that a combo box can lookup.

example:

Table : OilFilter
Fields: OilId (autonumber)
Oil Filter
Supplier

Table : AirFilter
Fields: AirId (autonumber)
Air Filter
Supplier

Table : FuelFilter
Fields: FuelId (autonumber)
Fuel Filter
Supplier

I'd like to have a combo box look these up collectively, by that i mean i
would like the Oil Filter, Air Filter & Fuel Filter to appear in the same
column in the combo box.

The combo box is located on a form called "PartNoForm".

Regards,

GregInOz