Is there a way to use a list box to select which table to query?

[Me]

I have multiple tables of products divided by manufacturer. I want to have a
list box that allows me to choose which manufacturer table I'm looking at.

I can make a list of the tables into a table but then how do I select which
table to use?

TIA

 

[Steve]

What you want can be done but the root of the problem is that the deign of
the structure of your tables is not correct. If you put a bandaid on this
problem, it won't be long until another problem arises. Suggested tables
are:

TblManufacturer
ManufacturerID
Manufacturer
<Other manufacturer fields you need in your database. like address fields>

TblProduct
ProductID
Product
<Other product fields you need>

TblProductManufacturer
ProductManufacturerID
ProductID
ManufacturerID

The third table is necessary if you have products made by more than one
manufacturer. With the above tables, you can use a query that includes all
three tables to find:
1 All products made by a manufacturer
2 All manufacturers of any product

Steve

 

[KARL DEWEY]

Add a manufacturer field to one of your tables and append to have only one
table.

Makes for lower number of queries as you can add a criteria prompt for
manufacturer.

 

[Me]

I think I need to be more detailed about what my data is and why I broke it
up this way.

The data is about cars. So I have a table for each year and make with model
info in a few different fields. Some models are the same but the engine
and/or transmission may be different.

I only have about 10 years of data so. I thought about having it as one
table with year, make, model etc. I just remember years ago when I was in
school they always told me if you have to keep putting in the same info (Ford
several thousand times) then you need a separate table.

Ultimately this whole thing is to search for the specific vehicle, extract a
bit of info, and perform some calculations based upon selections from 2 other
tables.

I still have the original all in one table format so if you still think that
would be the best way to deal with it that's pretty simple.

I really appreciate the help. I haven't written database stuff in a good 15
years and am really rusty.

 

[Steve]

TblManufacturer
ManufacturerID
Manufacturer
<Other manufacturer fields you need in your database. like address fields>

TblModel
ModelID
ManufacturerID
Model
<Other Model fields you need>

TblMake
MakeID
ModelID
Make

With the above tables, you can use a query that includes all
three tables to find:
1 All Models and Makes made by a manufacturer
2 All manufacturers of any Make and Model

Steve

 

[KARL DEWEY]

What would you extract from all of this data on the cars? Percent of USA
manufactured that have engine sizes in a specified range?

Post the field names and datatype of what you have. Also some sample data
and examples of what you might want to dig out of it.

See Steve's post.

 

[Ken Sheridan]

I think the logical model should be:

Manufacturers
----ManufactureID (primary key)
----Manufacturer

Makes
----MakeID (primary key)
----ManufacturerID
----Make

Models
----ModelD (primary key)
----MakeID
----Model

I'd probably also add a Specifications table referencing Models. The
specifications table itself might be referenced by other tables modelling
sub-types of the specifications entity type.

I suspect, however, that the OP might not be differentiating between
Manufacturers and Makes, e.g. they'd treat Saab or Vauxhall as the
'manufacturer', not GM, which owns both brands, in which case the
Manufacturers table is not required. But I could be wrong.

If Manufacturers is included as a separate entity type, then it does raise
another issue. What happens when a brand is sold? Ford have just sold the
Jaguar brand to Tata for instance, so should the manufacturer of my car be
recorded as Ford (which it was when I bought it a year ago) or Tata, i.e.
does the database record the manufacturer of the car when it was made, or the
current manufacturer of the of the brand?

The latter makes sense from the point of view of a car maintenance business
for instance, as the current owner of the brand is what's important, not the
historic owner, so the logical model would stand as above.

If the former, which would make sense for a motor museum's collection say
where the 'historic' manufacture is important, then it would be necessary to
introduce an additional table to model the many-to-many relationship between
Manufacturers and Makes, at the same time removing the foreign key
ManufacturerID column from Makes, and to have a composite foreign key of
ManufacurerID and MakeID in Models referencing the new table's composite
primary key of ManufacurerID and MakeID.

Ken Sheridan
Stafford, England

 

[Me]

Here are the fields

I have year and make as one but could be split since my original data is
that way. Make is the mfg that would be commonly be thought of. Pretty much
no cars are included in this database so a lot of those little details aren't
important. Pretty much dealing with Ford, Chevy, Dodge, Toyota.

Model, engine, transmission, powertrain are all used to select the vehicle.

Towing Capacity = # lbs vehicle can tow

The whole purpose of the database is to figure out how much a certain
vehicle can tow. So it's all about narrowing down the vehicles until you find
the right one and then see what the tow capacity is. After that there are
other tables that give weights of things we supply and it's just a matter of
taking the tow capacity less trailer weight divided by commodity weight to
find out how much of a commodity we can put in a trailer.

Users will never enter data. They will select year, make, model, trailer,
and commodity from list box. Then their answer will show up.

Sigh, I guess the last 15 years I should have expended some energy paying
attention to this stuff instead of sloughing it off onto the back burner.

 

[Ken Sheridan]

Taking the tables I suggested as the starting point you can eliminate the
manufacturers table. So the makes table is:

Makes
----Make (primary key)

You can add other columns as necessary to represent attributes of each make.

Tables represent entity types. Engines, transmissions, powertrains are all
entity types so you'd have a table for each of these:

Engines
----Engine (primary key)

Transmissions
----Transmission (primary key)

Powertrains
----Powertrain (primary key)

Again you can add other columns as necessary to represent attributes of each
entity type.

Models is also an entity type, but is slightly different as it will
reference makes:

Models
----Model (primary key)
----Make

You'll see that in each of these I've used 'natural keys' such as Make,
Model, Engine etc rather than numeric 'surrogate keys' such as MakeID,
ModelID, EngineID. This assumes than the values of each are distinct e.g.
two makes cannot have models of the same name. If they can then either a
numeric surrogate key would be necessary, or the primary key would be a
composite one of Make and Model. Natural keys will make things easier,
though, as you'll see below.

I've ignored Year for the moment as you say you have that integral with Make.

All of the above tables stand independent of each other apart from Makes and
Models (which references Makes), but the others are of course related in a
4-way many-to-many relationship. This relationship is modelled by another
table, which I'll call Vehicles, like so:

Vehicles
----Model
----Engine
----Powertrain
----Transmission
----TowingCapacity

The primary key of this table is a composite one of Model, Engine,
Powertrain and Transmission.

Although this table models a relationship type it also models an entity type
because relationship types are really just a special kind of entity type. So
again you can add other columns as necessary to represent attributes of this
entity type (Towingcapacity is such a 'non-key' attribute. Relationships
should be created between this table and Models, Engines, Powertrains, and
Transmissions. In the relationships referential integrity and cascade
updates should be enforced.

As for a form to search for vehicles I'd suggest you use a form, frmVehicles
say, in continuous form view bound to a query which references a series of
combo boxes from which you can select model, engine etc.

Put the unbound 'search' combo boxes in the form header. Each will list the
values from one table, so you'd have combo boxes cboModel, cboEngine,
cboTransmission and cboPowertrain. As an example the RowSource property of
cboEngine would be:

SELECT Engine FROM Engines ORDER BY Model;

The others would have similar RowSource properties drawing on the relevant
table in each case. The cboModel control would differ, however, by having a
RowSource of:

SELECT Model, Make FROM Models ORDER BY Make, Model;

and its other properties would be set up like so:

BoundColum: 1
ColumnCount: 2
ColumnWidths: 3cm;3cm
ListWidth: 6cm

If your units of measurement are imperial rather than metric Access will
automatically convert the above. Experiment with the ColumnWidths to get the
best fit. The ListWidth should be the sum of the ColumnWidths. To show the
make fro the selected model add a text box, txtMake, to the form header with
a ControlSource property of:

=[cboModel].[Column](1)

The Column property is zero-based, so Column(1) is the second column.

The query on which the form is based would be:

SELECT *
FROM Vehicles
WHERE (Model = Forms!frmVehicles!cboModel
OR Forms!frmVehicles!cboModel IS NULL)
AND (Engine = Forms!frmVehicles!cboEngine
OR Forms!frmVehicles!cboEngine IS NULL)
AND (Powertrain = Forms!frmVehicles!Powertrain
OR Forms!frmVehicles!Powertrain IS NULL)
AND (Transmission = Forms!frmVehicles!cboTransmission
OR Forms!frmVehicles!cboTransmission IS NULL)
ORDER BY Model;

In The AfterUpdate event procedures of the cboModel, cboEngine,
cboTransmission and cboPowertrain controls you'd requery the form with:

Me.Requery

As an item is selected from one of the combo boxes the form will be
requeried to show only those vehicles which match the selection, so as items
are progressively selected from the combo boxes the vehicles listed will be
narrowed down progressively on the basis of the combination of items selected
in the combo boxes.

To compute the amount of a commodity a trailer can carry add another combo
box, cboTrailer to the header with a RowSource along these lines:

SELECT Trailer, TrailerWeight FROM Trailers ORDER BY Trailer;

Set its other properties up exactly like cboModel.

To select a commodity add another combo box, cboCommodity with a RowSourece
along these lines:

SELECT Commodity, CommodityWeight FROM Commodities ORDER BY Commodity;

Again set its other properties up exactly like cboModel.

To show the amount of the selected commodity which can be carried in the
selected trailer by each vehicle add an unbound control to the form's detail
section with a ControlSource property of:

=([TowingCapacity]-[cboTrailer].[Column](1))/[cboCommodity].[Column](2)

I haven't been able to test the above of course, but I think I've got the
essentials right.

Ken Sheridan
Stafford, England