Display yes/no fields on a report as field name

[pbsandrad]

I am creating an association member roster of car dealerships. On the roster I want to list the lines (manufacturer)of cars the dealership sells. In my table I have each manufacturer listed as a yes/no field and if a dealer sells that line the box is checked for yes. Any given dealer might only sell 1 or 2 different lines (i.e. chevrolet, ford).

So on my report I want it to look like:

Dealership name Chevrolet
Address Ford
City,St zip
phone

Is there a way for me to tell the text box to display the line name if the yes/no field is "yes" in the table and not have the check box show up on the report, and of course not to display anything if it doesn't sell that line of cars? Note, there are about 40 different manufacturer lines in the table. I am fairly new to Access and obviously don't know how to code very well, but I hope to learn. Any suggestions would be appreciated.

 

[jrbarnett]

This is fairly easy to do. You don't say exactly which version of Access you are using, but the following are general steps:

1. Create a query based on your table, showing all fields.
2. Go into query design, when finished, and put "True" (without quote marks) in the Criteria under that field name.
Save the query.

If you run that query, it will show you the all data with the box ticked.

3. Create a report based upon the query.

This should solve your problem.

John

 

[pbsandrad]

John:
The only way your solution worked is if I only put "true" in the criteria for one manufacturer line. So if I have true under Ford and a dealer sells Fords Lincolns and Mercurys, thats fine. But then there are the dealers who sell only Chevrolets - they dont show up on the report. I need the report to show every dealer and every line that each dealer sells. Also the check box still shows up on the report, I only want the manufacturer name to show.

I currently have my report running from the Dealership table which houses all the dealers and all the manufacturers. The manufacturers are yes/no fields so that when I add a dealer I can specify which line of automobiles they sell.

This is exactly how I want my report to look, no check boxes, only manufacturer names:

Dealership name Chevrolet
Address Ford
City,St zip
phone

Dealership name Toyota
Address Dodge
City,St zip Nissan
phone

etc......

 

[PHV]

You have a normalization issue, take a look here:

http://r937.com/relational.html

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[lespaul]

you mean your table is:

DealerID
DealershipName
Address
blah
Ford (yes/no)
Chevrolet (yes/no)
etc.

PHV's right, you need to normalize first. You can do this with a query:

SELECT DealershipID, "FORD" As Models
FROM TableName
WHERE Ford
UNION
SELECT DealershipID, "CHEVROLET"
FROM TableName
WHERE Chevrolet
UNION.....

then use this query as the basis for your real query and you will be able to get the information you need easily.

A more long term solution will be to create a table Models where you would store an ID and the ModelName and any other information and then create a table that has both DealershipID and ModelID as FK to the main tables. Then you wouldn't have to have the normalization query and you don't have to change your table structure each time a new model is added to the list that your dealers sell.

HTH

Leslie

 

[willir]

pbsandrad

PHV and Leslie are both absolutely correct.

You have a many-to-many relationship...

A dealer may sell more than one model.
Models can be sold by many dealers.

tblDealer
DealerID - primary key
DealerName
Address

tblManufacturer
ManufacturerCode - primary key
ManufacturerName

tblDealerMaufacturer
DealerID - foreign key to tblDealer
ManufacturerCode - primary key to tblManufacturer

You are fortunate that there are only a few car manufacturers and using Yes/No fields for the Manufacture will work within limits.

Firstly, your queries will be very awkward. You will find your self using a lot of "OR"'s in your SQL statements.

But when a new manufacturer arises (with the Pacific rim, we will be seeing more companies from Asia, I am sure), you will have modify your forms and reports and queries to accommodate the new entry.

By treating the Manufacturer as a record in a table instead of a field in a table, you can accommodate changes much easier.

PHV gave you a link to an exceptional article on normalization. Here are some other links...

Where to find information about designing a database in Microsoft Access
283878 - Description of the database normalization basics
304467 - ACC2000 Defining Relationships Between Tables in a Microsoft Access Database

Richard