Help on Parameter Query possible query within a query (1 Viewer)

BJF

Registered User.
Local time
Yesterday, 19:06
Joined
Feb 19, 2010
Messages
126
Hi All,

I could really use some direction on this please!
I'm not sure how to go about it.
I'm thinking it will have to involve multiple queries, not sure at all what i need to do.

I have a table with 5 fields.

ResinCode
R1
R2
R3
R4

There are some ResinCodes that are only comprised of R1 in which case R1 is the equivalent of that ResinCode.
Other ResinCodes however are blends of different ResinCodes (Some use R1 and R2, a few use R1, R2, and R3, etc.)

I want to create a parameter query that will prompt me to enter a ResinCode and after I type a ResinCode into the parameter question,
the query results will return any ResinCodes records that contain that particular ResinCode in any of the other 4 fields (R1, R2, R3, R4).

an example of the query im looking for would be like this:

[ENTER RESIN CODE]
if I enter R001,
it might return for example:
R001
R008
R022
In this case being that R001, R008, and R022 all contain R001 in either field R1, R2, R3, or R4

Thanks for any direction, I appreciate it.
BJF
 

Gasman

Enthusiastic Amateur
Local time
Today, 00:06
Joined
Sep 21, 2011
Messages
10,531
Structure is all wrong.
Each resin should be a record, then dead easy.
Else have to use a bunch of ORs
 

plog

Banishment Pending
Local time
Yesterday, 18:06
Joined
May 11, 2011
Messages
10,916
You have not normalized your data properly. That's always an problem and a leading cause of the issue you are posting about today. You need to store your data properly:


When you feel the need to add a numeric suffix to field names, its time for a new table--or a redisgn of your existing one. You should not have 4 'R' fields. You should have 4 rows for that data instead:

tblResins
ResinID, autonumber, primary key
ResinCode, text, this will store whatever is currently in ResinCode
ResinNumber, number, this will store the number currently suffixed to each R field
ResinValue, text, this will store whatever is currently in those 4 R fields

When you have your data like that, searching it for just the data you want becomes trivial.
 

BJF

Registered User.
Local time
Yesterday, 19:06
Joined
Feb 19, 2010
Messages
126
Structure is all wrong.
Each resin should be a record, then dead easy.
Else have to use a bunch of ORs
Hi,
sorry if I didnt explain this clearly,
Each Resin is a separate record
ResinCode is the field name and some records are single resins, while others are blends made up of single resins within the table.
 

BJF

Registered User.
Local time
Yesterday, 19:06
Joined
Feb 19, 2010
Messages
126
You have not normalized your data properly. That's always an problem and a leading cause of the issue you are posting about today. You need to store your data properly:


When you feel the need to add a numeric suffix to field names, its time for a new table--or a redisgn of your existing one. You should not have 4 'R' fields. You should have 4 rows for that data instead:

tblResins
ResinID, autonumber, primary key
ResinCode, text, this will store whatever is currently in ResinCode
ResinNumber, number, this will store the number currently suffixed to each R field
ResinValue, text, this will store whatever is currently in those 4 R fields

When you have your data like that, searching it for just the data you want becomes trivial.
Thanks for this response Plog, I will have to think about this and make sense of it before i do anything else.
 

plog

Banishment Pending
Local time
Yesterday, 18:06
Joined
May 11, 2011
Messages
10,916
You explained it really clearly:

I have a table with 5 fields.

ResinCode
R1
R2
R3
R4

Your follow up is now making it really unclear.

Can you take a screenshot of your table with sample data in it? Perhaps complete the Relationship Tool and post a screenshot fo that.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 00:06
Joined
Feb 19, 2013
Messages
14,358
Sorry to say you have the wrong table design. Your 4 fields should be in rows in a child table not columns

oops too late
 

Users who are viewing this thread

Top Bottom