Creating a Query based on another table`

[Ian]

I posted this in query's but noone seemed able to help.
I'd like to make a select query for one table based on
the values in another.

Specifically Table 1 contains the contact information for
people with postal codes (N2T 2R8, M2J 2X9, etc..).
Table 2 contains the forward sortation area (field: FSA)
of postal codes that I'd like to select out (N2T, N1R,
etc...)

Previously I'd create a select query for table one with
criteria for postal codes Like "N2T*" OR "N1R*" OR etc...
but I don't want to have to keep creating these long
select criteria.

I tried creating the a table 1 query with the criteria
Like [table2]![FSA]* but I got an error. When I took out
the * I got nothing.

Any ideas?

 

[DDM]

Ian, I'm not quite sure I understand what you're trying to accomplish. Are
you:

1-trying to retrieve the FSA for the PCs that you include in your criteria;
or
2-trying to retrieve a list of PCs by FSA?

It it's (1), there's no getting around creating long select criteria. At
least the wildcards will ease the burden somewhat. If it's (2), it should be
easier.

Assuming (2), you need a table that lists, as unique values, the postal
codes (in one field) with the forward sortation areas (in another field)
they fall into. Your table2 sounds like it fits this description. If this is
so, you can set up a 1-to-many relationship between tables 1 and 2 with
table2 on the "1" side. Then you can create a query that includes both
tables, with the PC field from table1 and the FSA field from table2. Enter a
FSA as criteria and you will get a list of PCs that fall into it, together
with whatever other fields you include in your query. Hope this helps.

DDM
"DDM's Microsoft Office Tips and Tricks"
www.ddmcomputing.com

 

[Guest]

Thank you for the response but it didn't really get me
much further.

I have a list of 1000 clients with postal codes (PC's).
There are about 200 different FSA which would apply to
these PC's but both lists will grow in the future.

What I'm trying to prevent is having to reconstruct the
query everytime I want to sort my clients by FSA.

The way I thought about doing this was to have every
possible FSA in another table (Table 2) with another
field that is a yes/no box. When I want to include a
certain FSA I turn on the yes/no box and create a select
query to show only thos FSA's

From here I wanted to take the selected FSA's and apply
them to the PC list in my client table. Kind of like
using a parameter query where the parameter is specified
by the list of FSA's in another query.

Here's what I tried - but it didn't work.

Table 1 has Client ID & Postal Code (format N2T 2R8)
Table 2 has FSA's (format N2T, etc...) and a yes no box
Query 1 has only the FSA's from Table 2 with the box on
Query 2 has the client ID (and address info) where the
postal code criteria is [[Query 2]![FSA]*] but it really
didn't like that expression. Is there another way to do
it that will work?

-----Original Message-----
Ian, I'm not quite sure I understand what you're trying to accomplish. Are
you:

1-trying to retrieve the FSA for the PCs that you include in your criteria;
or
2-trying to retrieve a list of PCs by FSA?

It it's (1), there's no getting around creating long select criteria. At
least the wildcards will ease the burden somewhat. If it's (2), it should be
easier.

Assuming (2), you need a table that lists, as unique values, the postal
codes (in one field) with the forward sortation areas (in another field)
they fall into. Your table2 sounds like it fits this description. If this is
so, you can set up a 1-to-many relationship between tables 1 and 2 with
table2 on the "1" side. Then you can create a query that includes both
tables, with the PC field from table1 and the FSA field from table2. Enter a
FSA as criteria and you will get a list of PCs that fall into it, together
with whatever other fields you include in your query. Hope this helps.

DDM
"DDM's Microsoft Office Tips and Tricks"
www.ddmcomputing.com

I posted this in query's but noone seemed able to help.
I'd like to make a select query for one table based on
the values in another.

Specifically Table 1 contains the contact information for
people with postal codes (N2T 2R8, M2J 2X9, etc..).
Table 2 contains the forward sortation area (field: FSA)
of postal codes that I'd like to select out (N2T, N1R,
etc...)

Previously I'd create a select query for table one with
criteria for postal codes Like "N2T*" OR "N1R*" OR etc...
but I don't want to have to keep creating these long
select criteria.

I tried creating the a table 1 query with the criteria
Like [table2]![FSA]* but I got an error. When I took out
the * I got nothing.

Any ideas?

.

 

[DDM]

Ian, if what you want to do is pull up a list of clients where those clients
are sorted by FSA and filtered to show only specific FSAs, then you are
almost there.

You do need a table that contains every FSA and every PC that goes with it.
PC must be a unique value in this table, and that field must be indexed (no
duplicates). You need another table, one that lists the 200 or so FSAs,
where each FSA is unique, and that field must be indexed (no duplicates).
Put the Yes/No field in this table. Create a tabular form based on this
table. Use the form to select Yes or No for each FSA.

Set up the relationships as follows: FSA table to FSA/PC table, 1-to-many,
on the FSA field, with the FSA table on the "1" side. FSA/PC table to
"Clients" table, 1-to-many, on the PC field, with FSA/PC on the "1" side.

Create a query that includes all three tables. Include all fields from the
Clients table and the FSA and Yes/No fields from the FSA table. (No fields
from the FSA/PC table.) Sort ascending on FSA. Set criteria under the Yes/No
field as Yes, and hide the field. Run the query.

The resulting dataset should show only those FSAs you elected to include,
sorted by FSA. You won't have to rebuild the criteria. Just open the form
you created above and go up and down the list, checking and unchecking boxes
as desired.

DDM
"DDM's Microsoft Office Tips and Tricks"
www.ddmcomputing.com

Thank you for the response but it didn't really get me
much further.

I have a list of 1000 clients with postal codes (PC's).
There are about 200 different FSA which would apply to
these PC's but both lists will grow in the future.

What I'm trying to prevent is having to reconstruct the
query everytime I want to sort my clients by FSA.

The way I thought about doing this was to have every
possible FSA in another table (Table 2) with another
field that is a yes/no box. When I want to include a
certain FSA I turn on the yes/no box and create a select
query to show only thos FSA's

From here I wanted to take the selected FSA's and apply
them to the PC list in my client table. Kind of like
using a parameter query where the parameter is specified
by the list of FSA's in another query.

Here's what I tried - but it didn't work.

Table 1 has Client ID & Postal Code (format N2T 2R8)
Table 2 has FSA's (format N2T, etc...) and a yes no box
Query 1 has only the FSA's from Table 2 with the box on
Query 2 has the client ID (and address info) where the
postal code criteria is [[Query 2]![FSA]*] but it really
didn't like that expression. Is there another way to do
it that will work?

-----Original Message-----
Ian, I'm not quite sure I understand what you're trying to accomplish. Are
you:

1-trying to retrieve the FSA for the PCs that you include in your criteria;
or
2-trying to retrieve a list of PCs by FSA?

It it's (1), there's no getting around creating long select criteria. At
least the wildcards will ease the burden somewhat. If it's (2), it should be
easier.

Assuming (2), you need a table that lists, as unique values, the postal
codes (in one field) with the forward sortation areas (in another field)
they fall into. Your table2 sounds like it fits this description. If this is
so, you can set up a 1-to-many relationship between tables 1 and 2 with
table2 on the "1" side. Then you can create a query that includes both
tables, with the PC field from table1 and the FSA field from table2. Enter a
FSA as criteria and you will get a list of PCs that fall into it, together
with whatever other fields you include in your query. Hope this helps.

DDM
"DDM's Microsoft Office Tips and Tricks"
www.ddmcomputing.com

I posted this in query's but noone seemed able to help.
I'd like to make a select query for one table based on
the values in another.

Specifically Table 1 contains the contact information for
people with postal codes (N2T 2R8, M2J 2X9, etc..).
Table 2 contains the forward sortation area (field: FSA)
of postal codes that I'd like to select out (N2T, N1R,
etc...)

Previously I'd create a select query for table one with
criteria for postal codes Like "N2T*" OR "N1R*" OR etc...
but I don't want to have to keep creating these long
select criteria.

I tried creating the a table 1 query with the criteria
Like [table2]![FSA]* but I got an error. When I took out
the * I got nothing.

Any ideas?

.

 

[Guest]

Thank you very much - is there an easy way to use an
append query to take the 1st three digits of the postal
code and pump them into another coloum? (this way I can
just use my existing client list to populate my FSA table
then sort out the unique values).

-----Original Message-----
Ian, if what you want to do is pull up a list of clients where those clients
are sorted by FSA and filtered to show only specific FSAs, then you are
almost there.

You do need a table that contains every FSA and every PC that goes with it.
PC must be a unique value in this table, and that field must be indexed (no
duplicates). You need another table, one that lists the 200 or so FSAs,
where each FSA is unique, and that field must be indexed (no duplicates).
Put the Yes/No field in this table. Create a tabular form based on this
table. Use the form to select Yes or No for each FSA.

Set up the relationships as follows: FSA table to FSA/PC table, 1-to-many,
on the FSA field, with the FSA table on the "1" side. FSA/PC table to
"Clients" table, 1-to-many, on the PC field, with FSA/PC on the "1" side.

Create a query that includes all three tables. Include all fields from the
Clients table and the FSA and Yes/No fields from the FSA table. (No fields
from the FSA/PC table.) Sort ascending on FSA. Set criteria under the Yes/No
field as Yes, and hide the field. Run the query.

The resulting dataset should show only those FSAs you elected to include,
sorted by FSA. You won't have to rebuild the criteria. Just open the form
you created above and go up and down the list, checking and unchecking boxes
as desired.

DDM
"DDM's Microsoft Office Tips and Tricks"
www.ddmcomputing.com

Thank you for the response but it didn't really get me
much further.

I have a list of 1000 clients with postal codes (PC's).
There are about 200 different FSA which would apply to
these PC's but both lists will grow in the future.

What I'm trying to prevent is having to reconstruct the
query everytime I want to sort my clients by FSA.

The way I thought about doing this was to have every
possible FSA in another table (Table 2) with another
field that is a yes/no box. When I want to include a
certain FSA I turn on the yes/no box and create a select
query to show only thos FSA's

From here I wanted to take the selected FSA's and apply
them to the PC list in my client table. Kind of like
using a parameter query where the parameter is specified
by the list of FSA's in another query.

Here's what I tried - but it didn't work.

Table 1 has Client ID & Postal Code (format N2T 2R8)
Table 2 has FSA's (format N2T, etc...) and a yes no box
Query 1 has only the FSA's from Table 2 with the box on
Query 2 has the client ID (and address info) where the
postal code criteria is [[Query 2]![FSA]*] but it really
didn't like that expression. Is there another way to do
it that will work?

-----Original Message-----
Ian, I'm not quite sure I understand what you're

trying
to accomplish. Are

you:

1-trying to retrieve the FSA for the PCs that you include in your criteria;
or
2-trying to retrieve a list of PCs by FSA?

It it's (1), there's no getting around creating long select criteria. At
least the wildcards will ease the burden somewhat. If it's (2), it should be
easier.

Assuming (2), you need a table that lists, as unique values, the postal
codes (in one field) with the forward sortation areas (in another field)
they fall into. Your table2 sounds like it fits this description. If this is
so, you can set up a 1-to-many relationship between tables 1 and 2 with
table2 on the "1" side. Then you can create a query

that
includes both

tables, with the PC field from table1 and the FSA

field
from table2. Enter a

FSA as criteria and you will get a list of PCs that

fall
into it, together

with whatever other fields you include in your query. Hope this helps.

DDM
"DDM's Microsoft Office Tips and Tricks"
www.ddmcomputing.com

I posted this in query's but noone seemed able to help.
I'd like to make a select query for one table based on
the values in another.

Specifically Table 1 contains the contact

information
for

people with postal codes (N2T 2R8, M2J 2X9, etc..).
Table 2 contains the forward sortation area (field: FSA)
of postal codes that I'd like to select out (N2T, N1R,
etc...)

Previously I'd create a select query for table one with
criteria for postal codes Like "N2T*" OR "N1R*" OR etc...
but I don't want to have to keep creating these long
select criteria.

I tried creating the a table 1 query with the criteria
Like [table2]![FSA]* but I got an error. When I

took
out

the * I got nothing.

Any ideas?



.

.

 

[DDM]

You're very welcome!

"to take the 1st three digits of the postal code and pump them into another
column" -- a simple select query based on the table that contains the codes
will do this. Just create a calculated field something like this:
FSA:LEFT([field with FSA plus additional characters],3).

"then sort out the unique values" Set the query properties to output unique
values only.

Then when you have confirmed hat it works, you can change it to a
"make-table" query.

DDM
"DDM's Microsoft Office Tips and Tricks"
www.ddmcomputing.com

Thank you very much - is there an easy way to use an
append query to take the 1st three digits of the postal
code and pump them into another coloum? (this way I can
just use my existing client list to populate my FSA table
then sort out the unique values).

-----Original Message-----
Ian, if what you want to do is pull up a list of clients where those clients
are sorted by FSA and filtered to show only specific FSAs, then you are
almost there.

You do need a table that contains every FSA and every PC that goes with it.
PC must be a unique value in this table, and that field must be indexed (no
duplicates). You need another table, one that lists the 200 or so FSAs,
where each FSA is unique, and that field must be indexed (no duplicates).
Put the Yes/No field in this table. Create a tabular form based on this
table. Use the form to select Yes or No for each FSA.

Set up the relationships as follows: FSA table to FSA/PC table, 1-to-many,
on the FSA field, with the FSA table on the "1" side. FSA/PC table to
"Clients" table, 1-to-many, on the PC field, with FSA/PC on the "1" side.

Create a query that includes all three tables. Include all fields from the
Clients table and the FSA and Yes/No fields from the FSA table. (No fields
from the FSA/PC table.) Sort ascending on FSA. Set criteria under the Yes/No
field as Yes, and hide the field. Run the query.

The resulting dataset should show only those FSAs you elected to include,
sorted by FSA. You won't have to rebuild the criteria. Just open the form
you created above and go up and down the list, checking and unchecking boxes
as desired.

DDM
"DDM's Microsoft Office Tips and Tricks"
www.ddmcomputing.com

Thank you for the response but it didn't really get me
much further.

I have a list of 1000 clients with postal codes (PC's).
There are about 200 different FSA which would apply to
these PC's but both lists will grow in the future.

What I'm trying to prevent is having to reconstruct the
query everytime I want to sort my clients by FSA.

The way I thought about doing this was to have every
possible FSA in another table (Table 2) with another
field that is a yes/no box. When I want to include a
certain FSA I turn on the yes/no box and create a select
query to show only thos FSA's

From here I wanted to take the selected FSA's and apply
them to the PC list in my client table. Kind of like
using a parameter query where the parameter is specified
by the list of FSA's in another query.

Here's what I tried - but it didn't work.

Table 1 has Client ID & Postal Code (format N2T 2R8)
Table 2 has FSA's (format N2T, etc...) and a yes no box
Query 1 has only the FSA's from Table 2 with the box on
Query 2 has the client ID (and address info) where the
postal code criteria is [[Query 2]![FSA]*] but it really
didn't like that expression. Is there another way to do
it that will work?



-----Original Message-----
Ian, I'm not quite sure I understand what you're trying
to accomplish. Are
you:

1-trying to retrieve the FSA for the PCs that you
include in your criteria;
or
2-trying to retrieve a list of PCs by FSA?

It it's (1), there's no getting around creating long
select criteria. At
least the wildcards will ease the burden somewhat. If
it's (2), it should be
easier.

Assuming (2), you need a table that lists, as unique
values, the postal
codes (in one field) with the forward sortation areas
(in another field)
they fall into. Your table2 sounds like it fits this
description. If this is
so, you can set up a 1-to-many relationship between
tables 1 and 2 with
table2 on the "1" side. Then you can create a query that
includes both
tables, with the PC field from table1 and the FSA field
from table2. Enter a
FSA as criteria and you will get a list of PCs that fall
into it, together
with whatever other fields you include in your query.
Hope this helps.

DDM
"DDM's Microsoft Office Tips and Tricks"
www.ddmcomputing.com

message
I posted this in query's but noone seemed able to help.
I'd like to make a select query for one table based on
the values in another.

Specifically Table 1 contains the contact information
for
people with postal codes (N2T 2R8, M2J 2X9, etc..).
Table 2 contains the forward sortation area (field:
FSA)
of postal codes that I'd like to select out (N2T, N1R,
etc...)

Previously I'd create a select query for table one with
criteria for postal codes Like "N2T*" OR "N1R*" OR
etc...
but I don't want to have to keep creating these long
select criteria.

I tried creating the a table 1 query with the criteria
Like [table2]![FSA]* but I got an error. When I took
out
the * I got nothing.

Any ideas?



.

.