putting criteria in the table

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

Guest

Is there a way to put criterias statment in the table then have query look it
up in the table?
for example: in a table under "criteria" column, i put : not like camp*
and not like motor*
and in the query design under name criteria section i put
[tbltest]![criteria] but of course that didn't work.
thanks for you help.
 
Your expectations can't be met. If your value in the table was "camp*", you
could create a query with a criteria like
Not Like DLookup("Criteria","tblTest")
It's just not possible to parameterize "Not Like" or "Like" etc.

You may need to find another solution. Since we don't know your
requirements, we can't make viable suggestions.
 
You can do that if you write the SQL in VBA:

strSQL = "SELECT * FROM SomeTable WHERE " & DLookup("[Criteria]",
"tblQueryStuff", "QueryName = 'MyQuery'")

However, not a really good idea.
 
Thank you both for you comments. I'll think of better way if any.

What I am trying to do is cleaning up the database someone created. The
database is assigning collector name to their account by customer name(their
portfolio). However, the problem is that there are like 30 queries one for
each collector with their parameters. I wanted to put all in the table as a
list and assign the collector name based on the criteria set up in the table.
So that if anybody leaves or have new collector or swaps...it would be easy
to change instead of going in each query changing the criteria.

Thanks again!

for example:
Collector A = all New York City customer except customer name camp* or motor*
Collector B =



Klatuu said:
You can do that if you write the SQL in VBA:

strSQL = "SELECT * FROM SomeTable WHERE " & DLookup("[Criteria]",
"tblQueryStuff", "QueryName = 'MyQuery'")

However, not a really good idea.
--
Dave Hargis, Microsoft Access MVP


GEORGIA said:
Is there a way to put criterias statment in the table then have query look it
up in the table?
for example: in a table under "criteria" column, i put : not like camp*
and not like motor*
and in the query design under name criteria section i put
[tbltest]![criteria] but of course that didn't work.
thanks for you help.
Click to expand...
Click to expand...
 
Okay, this is a very common mistake made by people who don't have any
experience or training in relational database design and the way it is done
it this case leads me to believe it was designed by a person familiar with
Excel.

If you have the name of the collector in each customer record, that is not
correct. Here is what you need to do.
1. Create a Collector table that has the name of each collector. Use an
Autonumber field as the table's primary key.
2. Add a field to the Customer table with the data type of Long (the same
base data type as an autonumber, but does not auto increment). This will be
the new field to identify the collector associated with the customer.
3. Put all the names of the collectors in the collector table.
4. Create an Update Query that will update the Customer table with the
Autonumber field from the Collector table. You will just need to join the
tables on the collector name.
5. Delete the Collector Name field in the Customer table.
6. Update your table relations to join the Collector table to the Customer
table. You will want to set cascading updates on the join so that if you move
a customer from one collector's portfolio to another's, it will allow the
change.

Now if Fred quites and is replaced by Sally, all you have to do is open
Fred's record and change the name to Sally. The autonumber field that
relates to the customer table doesn't need to change. The change will
immediately be reflected just by the name change. The only time you will
need to change that is if you move a customer from one collector to another.
This is where you need the cascading update.

You will have to work out the detail, this is just a 30,000 foot overview,
but it is the correct design concept.
--
Dave Hargis, Microsoft Access MVP


GEORGIA said:
Thank you both for you comments. I'll think of better way if any.

What I am trying to do is cleaning up the database someone created. The
database is assigning collector name to their account by customer name(their
portfolio). However, the problem is that there are like 30 queries one for
each collector with their parameters. I wanted to put all in the table as a
list and assign the collector name based on the criteria set up in the table.
So that if anybody leaves or have new collector or swaps...it would be easy
to change instead of going in each query changing the criteria.

Thanks again!

for example:
Collector A = all New York City customer except customer name camp* or motor*
Collector B =



Klatuu said:
You can do that if you write the SQL in VBA:

strSQL = "SELECT * FROM SomeTable WHERE " & DLookup("[Criteria]",
"tblQueryStuff", "QueryName = 'MyQuery'")

However, not a really good idea.
--
Dave Hargis, Microsoft Access MVP


GEORGIA said:
Is there a way to put criterias statment in the table then have query look it
up in the table?
for example: in a table under "criteria" column, i put : not like camp*
and not like motor*
and in the query design under name criteria section i put
[tbltest]![criteria] but of course that didn't work.
thanks for you help.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top