Column Parameters

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

Guest

I know how to set a parameter for enetering data from a single column. How
do I do as follows:

Vendor TX AR MN NY
Pepsi Y Y Y
Coke Y Y
Dr. Pepper Y Y Y Y

I want to run a query where I can type in NY and it will display all vendors
where NY = Y but then be able to use that same query to be able to type TX
and it does the same thing without manually going into the query and changing
the field I want to see.

Any suggestions?
 
Dear Jacqua:

The way to write this query depends not only on what you want it to produce,
but also on from what you want it to be produced. You haven't said anything
at all about the source of the information. Unless you do so, and in some
detail, it's not likely anyone can guess how to help you.

If the results you posted were from a query, you might start by posting the
SQL code of that query.

Tom Ellison
 
Looking at your data it seems like you have your database in spreadsheet
format and not in a relational database method.
You need the table like --
DrinkVendorID - Autonumber - primary key
Drink - text
Vendor - text

The data would look like this.
DRINK VENDOR
Pepsi TX
Pepsi MN
Pepsi NY
Coke AR
Coke MN
Dr. Pepper TX
Dr. Pepper AR
Dr. Pepper MN
Dr. Pepper NY
 
Here is the SQL code.

SELECT [General Contractors Master List].[General Contractor], [General
Contractors Master List].AL, [General Contractors Master List].AZ, [General
Contractors Master List].AR, [General Contractors Master List].CA, [General
Contractors Master List].CO, [General Contractors Master List].CT, [General
Contractors Master List].DE, [General Contractors Master List].DC, [General
Contractors Master List].FL, [General Contractors Master List].GA, [General
Contractors Master List].HI, [General Contractors Master List].ID, [General
Contractors Master List].IL, [General Contractors Master List].[IN], [General
Contractors Master List].IO, [General Contractors Master List].KS, [General
Contractors Master List].KY, [General Contractors Master List].LA, [General
Contractors Master List].ME, [General Contractors Master List].MD, [General
Contractors Master List].MA, [General Contractors Master List].MI, [General
Contractors Master List].MN, [General Contractors Master List].MS, [General
Contractors Master List].MO, [General Contractors Master List].MT, [General
Contractors Master List].NB, [General Contractors Master List].NV, [General
Contractors Master List].NH, [General Contractors Master List].NJ, [General
Contractors Master List].NM, [General Contractors Master List].NY, [General
Contractors Master List].NC, [General Contractors Master List].ND, [General
Contractors Master List].OH, [General Contractors Master List].OK, [General
Contractors Master List].[OR], [General Contractors Master List].PA, [General
Contractors Master List].RI, [General Contractors Master List].SC, [General
Contractors Master List].SD, [General Contractors Master List].TN, [General
Contractors Master List].TX, [General Contractors Master List].UT, [General
Contractors Master List].VT, [General Contractors Master List].VA, [General
Contractors Master List].WA, [General Contractors Master List].WV, [General
Contractors Master List].WI, [General Contractors Master List].WY
FROM [General Contractors Master List];

If I have a list of 25 vendors and all 50 states listed as column headers,
and if a vendor works in that state, there is a "Y" in the field, I want to
be able to have a parameter set where I can type TX and it shows all vendors
who have "Y" in the TX column. Then be able to run the query again with a
different state and it pull the information without having to go into the
query and change scroll through the states and put "y" as my criteria.

Does that make sense?
-Brandi
 
Dear Jacqua:

In order to perform much of any kind of database function on this, it must
be normalized. That is, the design of this table is BAD.

A good table design would look like this:

[General Contractor]
State

There would be no Yes/No column. Only those states for which Y(es) is
intended would be rows. The N(o) states would not exist for the appropriate
contractor.

Creating most useful things from what you have will be difficult. You would
need to transform the appearance of the table (if not, as preferred, the
actual table itself) in order to perform meaningful work.

For the sample data you posted:

Vendor TX AR MN NY
Pepsi Y Y Y
Coke Y Y
Dr. Pepper Y Y Y Y

The table would be:

Vendor State
Pepsi TX
Pepsi MN
Pepsi NY
Coke AR
Coke MN
Pepper TX
Pepper AR
Pepper MN
Pepper NY

From this, plus a table listing all the states, you could readily create the
appearance you now have, plus be able to easily do the "filtering" you now
desire and answer questions like:

How many states are served by Pepsi? How many states are served by Pepsi
and Coke, but not Pepper? Which states have Pepper but not Coke?

There is not a single question you can ask that is not easier from
"normalized" data than from what you have now. Plan on making the change.
You're going to be much better off almost immediately.

Tom Ellison


Jacqua5 said:
Here is the SQL code.

SELECT [General Contractors Master List].[General Contractor], [General
Contractors Master List].AL, [General Contractors Master List].AZ,
[General
Contractors Master List].AR, [General Contractors Master List].CA,
[General
Contractors Master List].CO, [General Contractors Master List].CT,
[General
Contractors Master List].DE, [General Contractors Master List].DC,
[General
Contractors Master List].FL, [General Contractors Master List].GA,
[General
Contractors Master List].HI, [General Contractors Master List].ID,
[General
Contractors Master List].IL, [General Contractors Master List].[IN],
[General
Contractors Master List].IO, [General Contractors Master List].KS,
[General
Contractors Master List].KY, [General Contractors Master List].LA,
[General
Contractors Master List].ME, [General Contractors Master List].MD,
[General
Contractors Master List].MA, [General Contractors Master List].MI,
[General
Contractors Master List].MN, [General Contractors Master List].MS,
[General
Contractors Master List].MO, [General Contractors Master List].MT,
[General
Contractors Master List].NB, [General Contractors Master List].NV,
[General
Contractors Master List].NH, [General Contractors Master List].NJ,
[General
Contractors Master List].NM, [General Contractors Master List].NY,
[General
Contractors Master List].NC, [General Contractors Master List].ND,
[General
Contractors Master List].OH, [General Contractors Master List].OK,
[General
Contractors Master List].[OR], [General Contractors Master List].PA,
[General
Contractors Master List].RI, [General Contractors Master List].SC,
[General
Contractors Master List].SD, [General Contractors Master List].TN,
[General
Contractors Master List].TX, [General Contractors Master List].UT,
[General
Contractors Master List].VT, [General Contractors Master List].VA,
[General
Contractors Master List].WA, [General Contractors Master List].WV,
[General
Contractors Master List].WI, [General Contractors Master List].WY
FROM [General Contractors Master List];

If I have a list of 25 vendors and all 50 states listed as column headers,
and if a vendor works in that state, there is a "Y" in the field, I want
to
be able to have a parameter set where I can type TX and it shows all
vendors
who have "Y" in the TX column. Then be able to run the query again with a
different state and it pull the information without having to go into the
query and change scroll through the states and put "y" as my criteria.

Does that make sense?
-Brandi

Tom Ellison said:
Dear Jacqua:

The way to write this query depends not only on what you want it to
produce,
but also on from what you want it to be produced. You haven't said
anything
at all about the source of the information. Unless you do so, and in
some
detail, it's not likely anyone can guess how to help you.

If the results you posted were from a query, you might start by posting
the
SQL code of that query.

Tom Ellison
Click to expand...
Click to expand...
 
Back
Top