Table Query Parameters problem

[Lisa Cowan]

Dear anyone,
I have created a query on a table which based on people and their various
trades, you can be an electrician who tests, installs or inspects. So the
table has 7 trades columns and the query has all those columns plus contact
details and tel numbers. I have put parameters on the Trades columns so theat
you are probpted for the specific trade you are looking for in all seven
columns (Electrician might be found in Col1 for one person but it could be in
col 5 for someone else) plus a parameter for County so that we can norrow
down location.
I have tested it using criteria that should bring up a result (I know that
there is a Electrician in Norfolk) however I am only presented with one Trade
Parameter prompt and then the County prompt and then the result is a blank
database sheet with no entries on it. I have tried different trades, checked
spelling, parameters, delete the query and created another one but the same -
nothing.
Please help because I am starting to get a really bad headache.
thanks you in anticipation.

 

[Stefan Hoffmann]

hi Lisa,

I have created a query on a table which based on people and their various
trades, you can be an electrician who tests, installs or inspects. So the
table has 7 trades columns and the query has all those columns plus contact
details and tel numbers.

As Access is not Excel, you should normalize yout table:

http://en.wikipedia.org/wiki/Normalization

In your case, you need one additional table to define the trades, and
one additional table to store the information between your people and
trades, e.g.:

People:
ID, AutoIncrement, Primary Key
...

Trade:
ID, AutoIncrement, Primary Key
[Name], Primary Key, Text(50), not Null

People_Trade:
People_ID, Number(Long)
Trade_ID, Number(Long)
Description, Memo
...

with a combined primary key on (People_ID, Trade_ID).

I have put parameters on the Trades columns so theat
you are probpted for the specific trade you are looking for in all seven
columns (Electrician might be found in Col1 for one person but it could be in
col 5 for someone else) plus a parameter for County so that we can norrow
down location.

Here I'm not sure, if I understand you correctly. What are you storing
in each of your trades column? What purpose do these trade columns serve?

I have tested it using criteria that should bring up a result (I know that
there is a Electrician in Norfolk) however I am only presented with one Trade
Parameter prompt and then the County prompt and then the result is a blank
database sheet with no entries on it.

So each criteria looks like:

Like "*" & [ParameterToEnter] & "*"

?


mfG
--> stefan <--

 

[John Spencer (MVP)]

Your first problem is bad design.

You should have a separate table to contain the trades for each person. One
record for each combination of trade and person.

I suspect that your problem with your query is that you have put the criteria
all on one line. So if you input Electrician that value would have to be in
every one of the 7 trade columns.

If you are stuck with the current spreadsheet design then try putting the
county prompt on seven criteria lines
Field: County
Criteria (1): [What County]
Criteria (2): [What County]
....
Criteria (7): [What County]

Now put the prompt for the trade on seven lines but one per trade so you see a
stair step effect
Field: Trade1
Criteria (1): [What Trade]
Criteria (2-7): BLANK

Field: Trade2
Criteria (1): Blank
Criteria (2): [What Trade]
Criteria (3-7): BLANK

Continue.

By the way when you have a query that does not work it is a lot of help if you
copy and paste the SQL statement to your posting. It makes what you are
attempting to do a lot clearer. I made assumptions based on experience with
past postings - those assumptions may or may not be accurate.

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Lisa Cowan]

Your interpreted it correctly, thank you for your advice however not sure why
I need to have seven parameters for county is that to match off seven trades?
I do have a separate Trades table which I have linked to the main one so
that the 7 trade columns can all be selected through drop down menus. In the
main table each person has a record with all their trades listed and contact
details etc which are entered in through a form.
Your comment re 'bad design' please don't hold back. However I am not sure
how I could make it better as I am trying to improve on a appalling design of
an ecel spreadsheet where all the trades are listed in one cell. Sorry not a
good day and I have been working on this thing for to long for it not to be
personal. I really do appreciate your help. Thanks

Your first problem is bad design.

You should have a separate table to contain the trades for each person. One
record for each combination of trade and person.

I suspect that your problem with your query is that you have put the criteria
all on one line. So if you input Electrician that value would have to be in
every one of the 7 trade columns.

If you are stuck with the current spreadsheet design then try putting the
county prompt on seven criteria lines
Field: County
Criteria (1): [What County]
Criteria (2): [What County]
....
Criteria (7): [What County]

Now put the prompt for the trade on seven lines but one per trade so you see a
stair step effect
Field: Trade1
Criteria (1): [What Trade]
Criteria (2-7): BLANK

Field: Trade2
Criteria (1): Blank
Criteria (2): [What Trade]
Criteria (3-7): BLANK

Continue.

By the way when you have a query that does not work it is a lot of help if you
copy and paste the SQL statement to your posting. It makes what you are
attempting to do a lot clearer. I made assumptions based on experience with
past postings - those assumptions may or may not be accurate.

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Dear anyone,
I have created a query on a table which based on people and their various
trades, you can be an electrician who tests, installs or inspects. So the
table has 7 trades columns and the query has all those columns plus contact
details and tel numbers. I have put parameters on the Trades columns so theat
you are probpted for the specific trade you are looking for in all seven
columns (Electrician might be found in Col1 for one person but it could be in
col 5 for someone else) plus a parameter for County so that we can norrow
down location.
I have tested it using criteria that should bring up a result (I know that
there is a Electrician in Norfolk) however I am only presented with one Trade
Parameter prompt and then the County prompt and then the result is a blank
database sheet with no entries on it. I have tried different trades, checked
spelling, parameters, delete the query and created another one but the same -
nothing.
Please help because I am starting to get a really bad headache.
thanks you in anticipation.

 

[John Spencer (MVP)]

If you don't have county in each of the OR lines of the criteria then that set
of criteria will not restrict to the county.

This is an artifact of using the query design view. If you were writing the
query in SQL you could have a where clause that looked like

WHERE County = [Which County] AND
(Trade1 = [What Trade] or Trade2 = [What Trade] OR ...
OR Trade7 = [What Trade])

Notice how the parentheses include all the criteria for the trade fields.

Your structure should be like
Table: People
PersonID
PersonFirstName
PersonLastName
PersonPhone
Person Address
etc.

Table: Trades
TradeName
(any thing else that you need to store about a trade)

Table: PersonTrades (one record for each trade that a person/company has)
PersonID
TradeName
(and any other information you need to store about the person's trade - such
as Skill level, Price range (low, medium, high), etc)

Once you have this structure, you set up a form for entering information on
the person with a subform for entering the person's trades. I hope this is
enough to get you started.

You can always post back to the groups here for help.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[John Spencer (MVP)]

I just thought of another way to write the query that may be simpler to create
with your current structure. It might not be as efficient when the query is
run, but if your tables are fairly small the speed difference may be
unnoticeable to a human.

PUT the Prompt in your query as a field and then see if that matches one of
the fields using an in clause

Field: CheckAll: [What Trade]
Criteria(1):In([Trade1],[Trade2],[Trade3],[Trade4],[Trade5],[Trade6],[Trade7])

Field: County
Criteria(1): [What County]

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County