Field Criteria

[alex]

Using Access 03’…

Hello all,

I have a query that uses a field in a table as its criteria. E.g.,
Criteria: like [tblA].[STORE]

This works very well…however, I would like to modify the query (and
table) to accept multiple names within a single text field.

For example: Table A currently has a column named Store which holds a
single store.

I would like the text field to hold multiple stores (e.g., Gap, JCrew,
Target) and be able to compare multiple criteria based on a single
field. Keep in mind that what I mentioned above can easily be
accomplished by writing it out in Design View…e.g., Like Gap or Like
JCrew or Like Target. I just can’t seem to do it by table/field
reference.

Also, I cannot place the stores on multiple lines because I’ll have to
duplicate the primary key.

Any thoughts?

alex

 

[Cheese_whiz]

Hi alex,

Access 2007 has a multi-value data type I believe. I don't know how
developers feel about it. I would assume it's one of those things that is
designed to make life easier for less experienced developers, but at the cost
of the quality of the data you collect. Regardless, in older versions
there's no such data type and it's a violation of data normalization to put
multiple entries in a single field like you are asking. The answer, in 2003
(and prior versions) is to create a separate table to store those values,
then create a relationship (many to many) between the original table and the
new table, by creating a third table (known as a 'junction' table).

Then if you wanted to use a criteria in a query like you were suggesting, I
imagine you'd use a field (column) from the new table you created instead of
the original (new as in the new table, not the junction table).

That's my best take on it. I could be wrong here.

CW

Edit: Here's a comment or two from Allen Browne's site about multi-value
fields. Might be worth checking out.

 

[Jeff Boyce]

One fact, one field.

Storing "multiple names within a single ... field" would violate this basic
principle.

Do you actually have to STORE the store names, or could you work from a
textbox control on a form (unbound)?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Using Access 03’…

Hello all,

I have a query that uses a field in a table as its criteria. E.g.,
Criteria: like [tblA].[STORE]

This works very well…however, I would like to modify the query (and
table) to accept multiple names within a single text field.

For example: Table A currently has a column named Store which holds a
single store.

I would like the text field to hold multiple stores (e.g., Gap, JCrew,
Target) and be able to compare multiple criteria based on a single
field. Keep in mind that what I mentioned above can easily be
accomplished by writing it out in Design View…e.g., Like Gap or Like
JCrew or Like Target. I just can’t seem to do it by table/field
reference.

Also, I cannot place the stores on multiple lines because I’ll have to
duplicate the primary key.

Any thoughts?

alex

 

[Cheese_whiz]

oops! Here's the link:

http://www.allenbrowne.com/Access2007.html#Mixed

CW

Hi alex,

Access 2007 has a multi-value data type I believe. I don't know how
developers feel about it. I would assume it's one of those things that is
designed to make life easier for less experienced developers, but at the cost
of the quality of the data you collect. Regardless, in older versions
there's no such data type and it's a violation of data normalization to put
multiple entries in a single field like you are asking. The answer, in 2003
(and prior versions) is to create a separate table to store those values,
then create a relationship (many to many) between the original table and the
new table, by creating a third table (known as a 'junction' table).

Then if you wanted to use a criteria in a query like you were suggesting, I
imagine you'd use a field (column) from the new table you created instead of
the original (new as in the new table, not the junction table).

That's my best take on it. I could be wrong here.

CW

Edit: Here's a comment or two from Allen Browne's site about multi-value
fields. Might be worth checking out.

Using Access 03’…

Hello all,

I have a query that uses a field in a table as its criteria. E.g.,
Criteria: like [tblA].[STORE]

This works very well…however, I would like to modify the query (and
table) to accept multiple names within a single text field.

For example: Table A currently has a column named Store which holds a
single store.

I would like the text field to hold multiple stores (e.g., Gap, JCrew,
Target) and be able to compare multiple criteria based on a single
field. Keep in mind that what I mentioned above can easily be
accomplished by writing it out in Design View…e.g., Like Gap or Like
JCrew or Like Target. I just can’t seem to do it by table/field
reference.

Also, I cannot place the stores on multiple lines because I’ll have to
duplicate the primary key.

Any thoughts?

alex

 

[alex]

One fact, one field.

Storing "multiple names within a single ... field" would violate this basic
principle.

Do you actually have to STORE the store names, or could you work from a
textbox control on a form (unbound)?

Regards

Jeff Boyce
Microsoft Office/Access MVP


Using Access 03’…

Hello all,

I have a query that uses a field in a table as its criteria.  E.g.,
Criteria: like [tblA].[STORE]

This works very well…however, I would like to modify the query (and
table) to accept multiple names within a single text field.

For example:  Table A currently has a column named Store which holds a
single store.

I would like the text field to hold multiple stores (e.g., Gap, JCrew,
Target) and be able to compare multiple criteria based on a single
field.  Keep in mind that what I mentioned above can easily be
accomplished by writing it out in Design View…e.g., Like Gap or Like
JCrew or Like Target.  I just can’t seem to do it by table/field
reference.

Also, I cannot place the stores on multiple lines because I’ll have to
duplicate the primary key.

Any thoughts?

alex

Thanks for posting back...

I do have to store the Store names. This is what I'm doing (in a
nutshell).

Everyday I bring into Access about 20 to 40K records from ORACLE
(we'll call it tbl1). These records change daily!

I then have a second table (tbl2) that mirrors tbl1 (structure only)
in which multiple users create a case name and then list parameters in
the individual columns (this data is mostly static and only updated
periodically). When a qry is run, the user gets a report based on the
criterion he/she entered. In the past, should the user want multiple
criteria, he/she would have to duplicate the case name (on the next
line) and enter the second criterion. I want to keep the case name on
only one line and allow the qry to pick up multiple criteria in the
same textbox. What I'm basically doing is providing the WHERE of the
SQL clause (which exists in a table seperate from the query).

I hope I didn't confuse anyone!

 

[alex]

One fact, one field.

Storing "multiple names within a single ... field" would violate this basic
principle.

Do you actually have to STORE the store names, or could you work from a
textbox control on a form (unbound)?

Jeff Boyce
Microsoft Office/Access MVP

"alex" <[email protected]> wrote in message

Using Access 03’…

Hello all,

I have a query that uses a field in a table as its criteria.  E.g.,
Criteria: like [tblA].[STORE]

This works very well…however, I would like to modify the query (and
table) to accept multiple names within a single text field.

For example:  Table A currently has a column named Store which holds a
single store.

I would like the text field to hold multiple stores (e.g., Gap, JCrew,
Target) and be able to compare multiple criteria based on a single
field.  Keep in mind that what I mentioned above can easily be
accomplished by writing it out in Design View…e.g., Like Gap or Like
JCrew or Like Target.  I just can’t seem to do it by table/field
reference.

Also, I cannot place the stores on multiple lines because I’ll have to
duplicate the primary key.

Any thoughts?

alex

Thanks for posting back...

I do have to store the Store names.  This is what I'm doing (in a
nutshell).

Everyday I bring into Access about 20 to 40K records from ORACLE
(we'll call it tbl1).  These records change daily!

I then have a second table (tbl2) that mirrors tbl1 (structure only)
in which multiple users create a case name and then list parameters in
the individual columns (this data is mostly static and only updated
periodically).  When a qry is run, the user gets a report based on the
criterion he/she entered.  In the past, should the user want multiple
criteria, he/she would have to duplicate the case name (on the next
line) and enter the second criterion.  I want to keep the case name on
only one line and allow the qry to pick up multiple criteria in the
same textbox.  What I'm basically doing is providing the WHERE of the
SQL clause (which exists in a table seperate from the query).

I hope I didn't confuse anyone!- Hide quoted text -

- Show quoted text -

I guess another way to post my question is:

How can I write a query based on dozens of criteria stored somewhere
else. I cannot store my criteria in an indivdiual field; my table
would be too fat!

alex

 

[Jeff Boyce]

Alex

I'm probably not seeing the full picture yet, but...

If you have multiple values, why not use a table ... and use one record per
value?

It seems possible that you could join your data to that table to get all of
the records from the data set where value matched the values in that second
table...

Or maybe I just don't see it yet.

Regards

Jeff Boyce
Microsoft Office/Access MVP

One fact, one field.

Storing "multiple names within a single ... field" would violate this
basic
principle.

Do you actually have to STORE the store names, or could you work from a
textbox control on a form (unbound)?

Jeff Boyce
Microsoft Office/Access MVP

"alex" <[email protected]> wrote in message

Using Access 03’…

Hello all,

I have a query that uses a field in a table as its criteria. E.g.,
Criteria: like [tblA].[STORE]

This works very well…however, I would like to modify the query (and
table) to accept multiple names within a single text field.

For example: Table A currently has a column named Store which holds a
single store.

I would like the text field to hold multiple stores (e.g., Gap, JCrew,
Target) and be able to compare multiple criteria based on a single
field. Keep in mind that what I mentioned above can easily be
accomplished by writing it out in Design View…e.g., Like Gap or Like
JCrew or Like Target. I just can’t seem to do it by table/field
reference.

Also, I cannot place the stores on multiple lines because I’ll have to
duplicate the primary key.

Any thoughts?

alex

Thanks for posting back...

I do have to store the Store names. This is what I'm doing (in a
nutshell).

Everyday I bring into Access about 20 to 40K records from ORACLE
(we'll call it tbl1). These records change daily!

I then have a second table (tbl2) that mirrors tbl1 (structure only)
in which multiple users create a case name and then list parameters in
the individual columns (this data is mostly static and only updated
periodically). When a qry is run, the user gets a report based on the
criterion he/she entered. In the past, should the user want multiple
criteria, he/she would have to duplicate the case name (on the next
line) and enter the second criterion. I want to keep the case name on
only one line and allow the qry to pick up multiple criteria in the
same textbox. What I'm basically doing is providing the WHERE of the
SQL clause (which exists in a table seperate from the query).

I hope I didn't confuse anyone!- Hide quoted text -

- Show quoted text -

I guess another way to post my question is:

How can I write a query based on dozens of criteria stored somewhere
else. I cannot store my criteria in an indivdiual field; my table
would be too fat!

alex

 

[alex]

Alex

I'm probably not seeing the full picture yet, but...

If you have multiple values, why not use a table ... and use one record per
value?

It seems possible that you could join your data to that table to get all of
the records from the data set where value matched the values in that second
table...

Or maybe I just don't see it yet.

Regards

Jeff Boyce
Microsoft Office/Access MVP

One fact, one field.
Storing "multiple names within a single ... field" would violate this
basic
principle.
Do you actually have to STORE the store names, or could you work froma
textbox control on a form (unbound)?
Regards
Jeff Boyce
Microsoft Office/Access MVP
Using Access 03’…
Hello all,
I have a query that uses a field in a table as its criteria. E.g.,
Criteria: like [tblA].[STORE]
This works very well…however, I would like to modify the query (and
table) to accept multiple names within a single text field.
For example: Table A currently has a column named Store which holds a
single store.
I would like the text field to hold multiple stores (e.g., Gap, JCrew,
Target) and be able to compare multiple criteria based on a single
field. Keep in mind that what I mentioned above can easily be
accomplished by writing it out in Design View…e.g., Like Gap or Like
JCrew or Like Target. I just can’t seem to do it by table/field
reference.
Also, I cannot place the stores on multiple lines because I’ll haveto
duplicate the primary key.
Any thoughts?
alex

Thanks for posting back...

I do have to store the Store names. This is what I'm doing (in a
nutshell).

Everyday I bring into Access about 20 to 40K records from ORACLE
(we'll call it tbl1). These records change daily!

I then have a second table (tbl2) that mirrors tbl1 (structure only)
in which multiple users create a case name and then list parameters in
the individual columns (this data is mostly static and only updated
periodically). When a qry is run, the user gets a report based on the
criterion he/she entered. In the past, should the user want multiple
criteria, he/she would have to duplicate the case name (on the next
line) and enter the second criterion. I want to keep the case name on
only one line and allow the qry to pick up multiple criteria in the
same textbox. What I'm basically doing is providing the WHERE of the
SQL clause (which exists in a table seperate from the query).

I hope I didn't confuse anyone!- Hide quoted text -

- Show quoted text -

I guess another way to post my question is:

How can I write a query based on dozens of criteria stored somewhere
else.  I cannot store my criteria in an indivdiual field; my table
would be too fat!

alex- Hide quoted text -

- Show quoted text -

Thanks again Jeff for posting...

I have been using a table, and from that table one value/one record.
The problem arises when the user needs to create multiple records
based on a single case. Maybe I can spell it out below...

Below is an example of my tbl2 (the table that's referenced in the
criteria section of the query).

CaseName Store Mgr AuditDate
OprTact JMart S.Smith 20080930

The data you see above was entered by an investigator...that data will
be bounced off the 40k records that are taken from ORACLE every day.
If JMart is listed with S.Smith as the manager with an audit date of
20080903, then my investigator will get a report that his data above
is among (a record) in the 40K that cycles through daily.

What if, however, the investigator wants to know everything you see
above AND a Store name of GasMart with the same Mgr as above. The
CaseName stays the same, so the investigator (historically) would
enter the CaseName, again on another line, and GasMart under Store and
S.Smith under Mgr. What I'm trying to do is keep the CaseName to one
line, while allowing multiple criteria in the other records. I'm also
trying to avoid putting them in seperate tables.

 

[Jeff Boyce]

Alex

I guess I don't understand what you are trying to do with the 40K of ORACLE
records. It sounds like you are trying to search them by one or more
selection criteria (e.g., [Store], [Manager], [AuditDate], ...

So if I wanted to see all records for [Store] = "JMart" or "GasMart" and
[Manager] = "S.Smith", I'd use those as selection criteria (the implication
being that "S.Smith" manages more than one Store).

Who is it that needs to do this searching (?or filtering?)? What kind of
experience do they have using Access queries?

Do you need to consider creating a search form and dynamically setting the
"WHERE" clause based on the choices and entries on the search form?

(Still) more info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP



Thanks again Jeff for posting...

I have been using a table, and from that table one value/one record.
The problem arises when the user needs to create multiple records
based on a single case. Maybe I can spell it out below...

Below is an example of my tbl2 (the table that's referenced in the
criteria section of the query).

CaseName Store Mgr AuditDate
OprTact JMart S.Smith 20080930

The data you see above was entered by an investigator...that data will
be bounced off the 40k records that are taken from ORACLE every day.
If JMart is listed with S.Smith as the manager with an audit date of
20080903, then my investigator will get a report that his data above
is among (a record) in the 40K that cycles through daily.

What if, however, the investigator wants to know everything you see
above AND a Store name of GasMart with the same Mgr as above. The
CaseName stays the same, so the investigator (historically) would
enter the CaseName, again on another line, and GasMart under Store and
S.Smith under Mgr. What I'm trying to do is keep the CaseName to one
line, while allowing multiple criteria in the other records. I'm also
trying to avoid putting them in seperate tables.

 

[alex]

Alex

I guess I don't understand what you are trying to do with the 40K of ORACLE
records.  It sounds like you are trying to search them by one or more
selection criteria (e.g., [Store], [Manager], [AuditDate], ...

So if I wanted to see all records for [Store] = "JMart" or "GasMart" and
[Manager] = "S.Smith", I'd use those as selection criteria (the implication
being that "S.Smith" manages more than one Store).

Who is it that needs to do this searching (?or filtering?)?  What kind of
experience do they have using Access queries?

Do you need to consider creating a search form and dynamically setting the
"WHERE" clause based on the choices and entries on the search form?

(Still) more info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thanks again Jeff for posting...

I have been using a table, and from that table one value/one record.
The problem arises when the user needs to create multiple records
based on a single case.  Maybe I can spell it out below...

Below is an example of my tbl2 (the table that's referenced in the
criteria section of the query).

CaseName          Store         Mgr          AuditDate
OprTact               JMart        S.Smith     20080930

The data you see above was entered by an investigator...that data will
be bounced off the 40k records that are taken from ORACLE every day.
If JMart is listed with S.Smith as the manager with an audit date of
20080903, then my investigator will get a report that his data above
is among (a record) in the 40K that cycles through daily.

What if, however, the investigator wants to know everything you see
above AND a Store name of GasMart with the same Mgr as above.  The
CaseName stays the same, so the investigator (historically) would
enter the CaseName, again on another line, and GasMart under Store and
S.Smith under Mgr.  What I'm trying to do is keep the CaseName to one
line, while allowing multiple criteria in the other records.  I'm also
trying to avoid putting them in seperate tables.

Jeff,

I'm taking the ORACLE records (which are now in Access) and comparing
them to a table populated by multiple users. You're right, I am
searching by >1 criteria.

My users are new to Access and simply populate a Form (if they were
more comfortable, they could write their own SQL). I have assistants
that run the queries that compare the two tables.

Since we last spoke, I broke the user table into two and created a one-
to-many relationship...this seems to work OK.

I'm not exactly sure what you mean by: creating a search form and
dynamically setting the "WHERE" clause based on the choices and
entries on the search form...I think I get the idea, however.

I think we're on the same page...you mentioned above to: use those
[records] as selection criteria. I am doing that, I just wanted to it
on one line.

Old way:

CaseName Store Mgr AuditDate
OprTact JMart S.Smith 20080930
OprTact GasMart S.Smith 20081001

What I had hoped for:

CaseName Store Mgr AuditDate
OprTact JMart, GasMart S.Smith 20080930,
20081001

I now have two tables: one that holds the CaseName (once). And one
that holds the CaseName multiple times with the corresponding records
used in my queries.

I hope this makes sense and thanks for helping.

alex