Append Query with multiple criteria

  • Thread starter Thread starter LisaS
  • Start date Start date
L

LisaS

In the design view of the Append query, I am trying
to "send" the data to predefined fields based on what is
in the field called SUBGROUP. There are three possible
SUBGROUP fields in the originating table - SUBGROUP1,
SUBGROUP2 and SUBGROUP3. And the data that is contained
within the fields maybe one of 5 different subgroups -
100000, 100005, 240007, 100010, 100214. Depending on which
of these it is, decides where it goes in the destination
(append to) table.
For instance [SUBGROUP1]="100005", then it should go into
SUBGROUP2_MCC field in the destination table.
I can only get the data to go into the first field
[SUBGROUP1].
Your assistance is truly appreciated!
 
I am unclear on what exactly you are attempting, so this may be off-base.

First of all it sounds as if you are defining the types of subgroups by naming
the field. That is probably incorrect design. You would probably be better off
with a table that stores the subgroup type in one field and the value in a
second field and probably a pointer to the primary key of another table in a
third field.

Second can you post the SQL of the query that almost works.
 
When I get this spreadsheet (linking an Excel sheet), i
get multiple "subgroups" in the same column. I will start
with the subgroups 100000, 100005, 240007, 100010, 100214
all in the field called "SUBGROUP. What I would like is to
put (keep) all the subgroup 100000 into the field
called "SUBGROUP", then all 100005 into the field
called "SUBGROUP100005", all the subgroup 240007 into the
field called "SUBGROUP240007" and so on.
I am making any sense?
TY
-----Original Message-----
I am unclear on what exactly you are attempting, so this may be off-base.

First of all it sounds as if you are defining the types of subgroups by naming
the field. That is probably incorrect design. You would probably be better off
with a table that stores the subgroup type in one field and the value in a
second field and probably a pointer to the primary key of another table in a
third field.

Second can you post the SQL of the query that almost works.


In the design view of the Append query, I am trying
to "send" the data to predefined fields based on what is
in the field called SUBGROUP. There are three possible
SUBGROUP fields in the originating table - SUBGROUP1,
SUBGROUP2 and SUBGROUP3. And the data that is contained
within the fields maybe one of 5 different subgroups -
100000, 100005, 240007, 100010, 100214. Depending on which
of these it is, decides where it goes in the destination
(append to) table.
For instance [SUBGROUP1]="100005", then it should go into
SUBGROUP2_MCC field in the destination table.
I can only get the data to go into the first field
[SUBGROUP1].
Your assistance is truly appreciated!
Click to expand...
.
Click to expand...
 
HELP!
I am still trying to move records to different fields
based on what I thought was fairly simple criteria.

All the data is now in fields I will call A1, A2, and A3.

Based on what is in A1, I want to either leave it in A1,
A3 and A3 OR move the data A1->B1, A2->B2 and A3->B3.

Please note: i am fairly knowledgeable about expressions,
but not VBA.

TY, TY, TY!
-----Original Message-----
When I get this spreadsheet (linking an Excel sheet), i
get multiple "subgroups" in the same column. I will start
with the subgroups 100000, 100005, 240007, 100010, 100214
all in the field called "SUBGROUP. What I would like is to
put (keep) all the subgroup 100000 into the field
called "SUBGROUP", then all 100005 into the field
called "SUBGROUP100005", all the subgroup 240007 into the
field called "SUBGROUP240007" and so on.
I am making any sense?
TY
-----Original Message-----
I am unclear on what exactly you are attempting, so this may be off-base.

First of all it sounds as if you are defining the types of subgroups by naming
the field. That is probably incorrect design. You
Click to expand...
would
probably be better off
with a table that stores the subgroup type in one field and the value in a
second field and probably a pointer to the primary key
Click to expand...
of
another table in a
third field.

Second can you post the SQL of the query that almost works.


In the design view of the Append query, I am trying
to "send" the data to predefined fields based on what is
in the field called SUBGROUP. There are three possible
SUBGROUP fields in the originating table - SUBGROUP1,
SUBGROUP2 and SUBGROUP3. And the data that is contained
within the fields maybe one of 5 different subgroups -
100000, 100005, 240007, 100010, 100214. Depending on which
of these it is, decides where it goes in the destination
(append to) table.
For instance [SUBGROUP1]="100005", then it should go into
SUBGROUP2_MCC field in the destination table.
I can only get the data to go into the first field
[SUBGROUP1].
Your assistance is truly appreciated!
Click to expand...
.
Click to expand...
.
Click to expand...
 
This would be an update query. You are copying data from one field to
another field in the same record, right? First create an update query that
will update B1 to A1, B2 to A2, and B3 to A3 based on your criteria. You
only need to check for the criteria that will result in the move since
nothing happens if your criteria is not met. Then you will need another
update query to erase A1, A2, and A3. Use the criteria A1=B1 to determine
which data to remove.

Kelvin

LisaS said:
HELP!
I am still trying to move records to different fields
based on what I thought was fairly simple criteria.

All the data is now in fields I will call A1, A2, and A3.

Based on what is in A1, I want to either leave it in A1,
A3 and A3 OR move the data A1->B1, A2->B2 and A3->B3.

Please note: i am fairly knowledgeable about expressions,
but not VBA.

TY, TY, TY!
-----Original Message-----
When I get this spreadsheet (linking an Excel sheet), i
get multiple "subgroups" in the same column. I will start
with the subgroups 100000, 100005, 240007, 100010, 100214
all in the field called "SUBGROUP. What I would like is to
put (keep) all the subgroup 100000 into the field
called "SUBGROUP", then all 100005 into the field
called "SUBGROUP100005", all the subgroup 240007 into the
field called "SUBGROUP240007" and so on.
I am making any sense?
TY
-----Original Message-----
I am unclear on what exactly you are attempting, so this may be off-base.

First of all it sounds as if you are defining the types of subgroups by naming
the field. That is probably incorrect design. You
Click to expand...
would
probably be better off
with a table that stores the subgroup type in one field and the value in a
second field and probably a pointer to the primary key
Click to expand...
of
another table in a
third field.

Second can you post the SQL of the query that almost works.



LisaS wrote:

In the design view of the Append query, I am trying
to "send" the data to predefined fields based on what is
in the field called SUBGROUP. There are three possible
SUBGROUP fields in the originating table - SUBGROUP1,
SUBGROUP2 and SUBGROUP3. And the data that is contained
within the fields maybe one of 5 different subgroups -
100000, 100005, 240007, 100010, 100214. Depending on which
of these it is, decides where it goes in the destination
(append to) table.
For instance [SUBGROUP1]="100005", then it should go into
SUBGROUP2_MCC field in the destination table.
I can only get the data to go into the first field
[SUBGROUP1].
Your assistance is truly appreciated!
.
Click to expand...
.
Click to expand...
Click to expand...
 
Back
Top