Multiple Choice

  • Thread starter Thread starter KP
  • Start date Start date
K

KP

Hi, I hope you are all doing well.

I am building a report from a query and have several
fields that correlate to one another. For example:
Customer has which vehicle, choices being Ford, Chevy,
etc. When the vehicle of choice is checked on the data
entry form, I want to put only the checked entry from the
table on the report, rather than list all the different
types again.

So this example would look like this:
Customer has a Ford.

Is there a way to block out the other choices and include
only the checked selection and if so how do I do that?


TIA
KP
 
KP,

Your question implies you have several Yes/No fields in your table, one ofr
each vehicle type? If this is indeed the case then allow me to say the
problem is not how to do what you want on the report, the problem is in the
design! What you have done is very inflexible, in that whenever you need to
add a vehicle type, you are required to change your table design (add
fields), plus you end up having a number of fields per record of which you
will only be using one, which is inefficient in terms of db size. I would
suggest the following changes to your db design, and urge you to consider
it:

Add a "Vehicles" table, one Vehicle_ID field (PK) plus a Vehicle_Description
one;
Add a Vehicle_ID to your Customers table, populate it and then remove the
Yes/No ones;
Join the two tables and enforce Referential Integrity;
Change your form so you use a listbox or combobox for vehicle types, and
remove the checkboxes.

Your report will now be a piece of cake, just join the two tables in a query
and pick up whatever fields you need from your Customers table, plus the
Description field from the Vehicles table.
Additionally, you will have a much more robust and space-efficient design,
ease of maintenace and ease in further development.

HTH,
Nikos
 
Back
Top