Unable to merge identical table fields

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

Guest

I have gained invaluable information using this forum for the past couple of
years and I thank all who participate. I have been wrestling with this
problem for some time now and have finally decided the time has come to post
my question.

I have a good understanding of the different types of queries (ie: select,
append, union, make table etc). My database is using linked tables ( which
may be part of my problem - I am not sure). I am trying to merge two similar
tables on a field named [State Code]. In the Design View of the tables, all
components are set exactly the same. I am using Access 2002.

Here is a sample of my table data.

Table A Table B

State Code Amount State Code Amount

CA $50.00 MO
$10.00
LA $90.00 UT
$33.00
DE $25.00 CA
$40.00

This would be my desired recordset returned by a select query on which I
could base a form and/or report.

State Code Amount

CA $90.00
DE $25.00
LA $90.00
MO $10.00
UT $33.00

What the query does however is return a recordset like the following:

CA $50.00
DE $25.00
LA $90.00
CA $40.00
MO $10.00
UT $33.00

A union query is used to bring the two tables together. Then a select query
is used to "combine by state code" and view the results. What makes this
problem more perplexing is that what I really want to do is enter a Parameter
on the [State Code] field to view all of the "merged" table data. However,
when I place a parameter on this field, it will return only "some" of the
results. For instance, using the Parameter value "CA", the query returns
only the following Table B value:

CA $40.00

The $50.00 Table A value is not returned. I simply do not know what the
problem is. Any suggestions are appreciated. Thank you.
 
KC said:
I have gained invaluable information using this forum for the past
couple of years and I thank all who participate. I have been
wrestling with this problem for some time now and have finally
decided the time has come to post my question.

I have a good understanding of the different types of queries (ie:
select, append, union, make table etc). My database is using linked
tables ( which may be part of my problem - I am not sure). I am
trying to merge two similar tables on a field named [State Code]. In
the Design View of the tables, all components are set exactly the
same. I am using Access 2002.

Here is a sample of my table data.

Table A Table B

State Code Amount State Code
Amount

CA $50.00 MO
$10.00
LA $90.00 UT
$33.00
DE $25.00 CA
$40.00

This would be my desired recordset returned by a select query on
which I could base a form and/or report.

State Code Amount

CA $90.00
DE $25.00
LA $90.00
MO $10.00
UT $33.00

What the query does however is return a recordset like the following:

CA $50.00
DE $25.00
LA $90.00
CA $40.00
MO $10.00
UT $33.00

A union query is used to bring the two tables together. Then a
select query is used to "combine by state code" and view the results.
What makes this problem more perplexing is that what I really want to
do is enter a Parameter on the [State Code] field to view all of the
"merged" table data. However, when I place a parameter on this
field, it will return only "some" of the results. For instance,
using the Parameter value "CA", the query returns only the following
Table B value:

CA $40.00

The $50.00 Table A value is not returned. I simply do not know what
the problem is. Any suggestions are appreciated. Thank you.
Click to expand...

It would appear you need to normalize the tables. It appears that there
is a possibility for any state to have more than one amount and you want to
be able to combine them That means each table should be split with the
state name in one table and the amounts in another. Data that is related to
the state (like capital city etc.) would remain with that and data related
to the amount (like who paid it) would be in the other table.
 
Presumably you're doing something like

SELECT [State Code], Amount
FROM [Table A]
UNION ALL
SELECT [State Code], Amount
FROM [Table B]
WHERE [State Code] = [What State?]

In that SQL, the parameter only applies to the last subselect.

To have it apply to the the UNIONed query, you either need to save the
query, and do a query against the saved query with a parameter, or try
something like

SELECT [State Code], Amount
FROM
(
SELECT [State Code], Amount
FROM [Table A]
UNION ALL
SELECT [State Code], Amount
FROM [Table B]
) AS MainQuery
WHERE [State Code] = [What State?]

(BTW, make sure you have UNION ALL, rather than simply UNION. If you only
have UNION, then if you had rows with the same state code and amount in the
two tables, you'd only get one of them, not both)


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


KC Classic said:
I have gained invaluable information using this forum for the past couple
of
years and I thank all who participate. I have been wrestling with this
problem for some time now and have finally decided the time has come to
post
my question.

I have a good understanding of the different types of queries (ie: select,
append, union, make table etc). My database is using linked tables (
which
may be part of my problem - I am not sure). I am trying to merge two
similar
tables on a field named [State Code]. In the Design View of the tables,
all
components are set exactly the same. I am using Access 2002.

Here is a sample of my table data.

Table A Table B

State Code Amount State Code Amount

CA $50.00 MO
$10.00
LA $90.00 UT
$33.00
DE $25.00 CA
$40.00

This would be my desired recordset returned by a select query on which I
could base a form and/or report.

State Code Amount

CA $90.00
DE $25.00
LA $90.00
MO $10.00
UT $33.00

What the query does however is return a recordset like the following:

CA $50.00
DE $25.00
LA $90.00
CA $40.00
MO $10.00
UT $33.00

A union query is used to bring the two tables together. Then a select
query
is used to "combine by state code" and view the results. What makes this
problem more perplexing is that what I really want to do is enter a
Parameter
on the [State Code] field to view all of the "merged" table data.
However,
when I place a parameter on this field, it will return only "some" of the
results. For instance, using the Parameter value "CA", the query returns
only the following Table B value:

CA $40.00

The $50.00 Table A value is not returned. I simply do not know what the
problem is. Any suggestions are appreciated. Thank you.
Click to expand...
 
Thanks Joseph,

Your quick response is appreciated.

I believe you are saying what I have heard many times in the past - smaller
and only related information is a better table construct than larger tables.
My tables all contain the same structure (ie: identical field names, sizes
etc.), just working with multiple system data. I can normalize the tables
accordingly as I believe you are suggesting. Access even has the Table
Analyzer that can do that for you.

One further question though. Assuming I normalize the data and I now have a
smaller table containing only data directly related to the individual state,
don't I then have to use joins to bring the table data together via a query.
Say I have a table named tblState and others named tblSystemA, tblSystemB,
etc., I would then join the tables in a query on the field [State Code] to be
able to introduce Parameters to the mix. Seems I would only be splitting the
information up only to bring it together again. I do want tblSystemA,
tblSystemB data to be seen together only make accesible by [State Code] to
user via Parameter value.

As you probably can tell, I am a bit unclear on data normalization. Thank
you.


Joseph Meehan said:
KC said:
I have gained invaluable information using this forum for the past
couple of years and I thank all who participate. I have been
wrestling with this problem for some time now and have finally
decided the time has come to post my question.

I have a good understanding of the different types of queries (ie:
select, append, union, make table etc). My database is using linked
tables ( which may be part of my problem - I am not sure). I am
trying to merge two similar tables on a field named [State Code]. In
the Design View of the tables, all components are set exactly the
same. I am using Access 2002.

Here is a sample of my table data.

Table A Table B

State Code Amount State Code
Amount

CA $50.00 MO
$10.00
LA $90.00 UT
$33.00
DE $25.00 CA
$40.00

This would be my desired recordset returned by a select query on
which I could base a form and/or report.

State Code Amount

CA $90.00
DE $25.00
LA $90.00
MO $10.00
UT $33.00

What the query does however is return a recordset like the following:

CA $50.00
DE $25.00
LA $90.00
CA $40.00
MO $10.00
UT $33.00

A union query is used to bring the two tables together. Then a
select query is used to "combine by state code" and view the results.
What makes this problem more perplexing is that what I really want to
do is enter a Parameter on the [State Code] field to view all of the
"merged" table data. However, when I place a parameter on this
field, it will return only "some" of the results. For instance,
using the Parameter value "CA", the query returns only the following
Table B value:

CA $40.00

The $50.00 Table A value is not returned. I simply do not know what
the problem is. Any suggestions are appreciated. Thank you.
Click to expand...

It would appear you need to normalize the tables. It appears that there
is a possibility for any state to have more than one amount and you want to
be able to combine them That means each table should be split with the
state name in one table and the amounts in another. Data that is related to
the state (like capital city etc.) would remain with that and data related
to the amount (like who paid it) would be in the other table.
Click to expand...
 
Douglas,

Thank you. I want to be sure this works and my SQL skills are limited. The
subselect statement is exactly what I have needed for a long time. Here is
the code I am using that now works:

SELECT [State Code], Total
FROM [SELECT [State Code], Total
FROM [uqryStatePayment-MERGE]
UNION ALL
SELECT [State Code], Total
FROM [uqryStatePayment-MERGE DTO DST TRAC]
]. AS Query6
WHERE [State Code] = [What State?];

Could you provide an example of what the code would read if I wanted to add
the field [MgmtCo]? This field is included in both of the tables referenced
above. I keep getting the following error message when I add the field.

Invalid bracketing of name SELECT '[State Code'

I don't understand what I am doing wrong.

Thanks in advance.
Douglas J. Steele said:
Presumably you're doing something like

SELECT [State Code], Amount
FROM [Table A]
UNION ALL
SELECT [State Code], Amount
FROM [Table B]
WHERE [State Code] = [What State?]

In that SQL, the parameter only applies to the last subselect.

To have it apply to the the UNIONed query, you either need to save the
query, and do a query against the saved query with a parameter, or try
something like

SELECT [State Code], Amount
FROM
(
SELECT [State Code], Amount
FROM [Table A]
UNION ALL
SELECT [State Code], Amount
FROM [Table B]
) AS MainQuery
WHERE [State Code] = [What State?]

(BTW, make sure you have UNION ALL, rather than simply UNION. If you only
have UNION, then if you had rows with the same state code and amount in the
two tables, you'd only get one of them, not both)


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


KC Classic said:
I have gained invaluable information using this forum for the past couple
of
years and I thank all who participate. I have been wrestling with this
problem for some time now and have finally decided the time has come to
post
my question.

I have a good understanding of the different types of queries (ie: select,
append, union, make table etc). My database is using linked tables (
which
may be part of my problem - I am not sure). I am trying to merge two
similar
tables on a field named [State Code]. In the Design View of the tables,
all
components are set exactly the same. I am using Access 2002.

Here is a sample of my table data.

Table A Table B

State Code Amount State Code Amount

CA $50.00 MO
$10.00
LA $90.00 UT
$33.00
DE $25.00 CA
$40.00

This would be my desired recordset returned by a select query on which I
could base a form and/or report.

State Code Amount

CA $90.00
DE $25.00
LA $90.00
MO $10.00
UT $33.00

What the query does however is return a recordset like the following:

CA $50.00
DE $25.00
LA $90.00
CA $40.00
MO $10.00
UT $33.00

A union query is used to bring the two tables together. Then a select
query
is used to "combine by state code" and view the results. What makes this
problem more perplexing is that what I really want to do is enter a
Parameter
on the [State Code] field to view all of the "merged" table data.
However,
when I place a parameter on this field, it will return only "some" of the
results. For instance, using the Parameter value "CA", the query returns
only the following Table B value:

CA $40.00

The $50.00 Table A value is not returned. I simply do not know what the
problem is. Any suggestions are appreciated. Thank you.
Click to expand...
Click to expand...
 
Seems I would only be splitting the information up only to bring it
together again.
Click to expand...

Look at your screen You will see the group name as one heading.
Somewhere underneath that you will see subject headings.
Some where under those you will see this reply.

The world makes more sense and is more manageable when split into levels
like this, and that is all that normalisation is doing.


KC Classic said:
Thanks Joseph,

Your quick response is appreciated.

I believe you are saying what I have heard many times in the past -
smaller
and only related information is a better table construct than larger
tables.
My tables all contain the same structure (ie: identical field names, sizes
etc.), just working with multiple system data. I can normalize the tables
accordingly as I believe you are suggesting. Access even has the Table
Analyzer that can do that for you.

One further question though. Assuming I normalize the data and I now have
a
smaller table containing only data directly related to the individual
state,
don't I then have to use joins to bring the table data together via a
query.
Say I have a table named tblState and others named tblSystemA, tblSystemB,
etc., I would then join the tables in a query on the field [State Code] to
be
able to introduce Parameters to the mix. Seems I would only be splitting
the
information up only to bring it together again. I do want tblSystemA,
tblSystemB data to be seen together only make accesible by [State Code] to
user via Parameter value.

As you probably can tell, I am a bit unclear on data normalization. Thank
you.


Joseph Meehan said:
KC said:
I have gained invaluable information using this forum for the past
couple of years and I thank all who participate. I have been
wrestling with this problem for some time now and have finally
decided the time has come to post my question.

I have a good understanding of the different types of queries (ie:
select, append, union, make table etc). My database is using linked
tables ( which may be part of my problem - I am not sure). I am
trying to merge two similar tables on a field named [State Code]. In
the Design View of the tables, all components are set exactly the
same. I am using Access 2002.

Here is a sample of my table data.

Table A Table B

State Code Amount State Code
Amount

CA $50.00 MO
$10.00
LA $90.00 UT
$33.00
DE $25.00 CA
$40.00

This would be my desired recordset returned by a select query on
which I could base a form and/or report.

State Code Amount

CA $90.00
DE $25.00
LA $90.00
MO $10.00
UT $33.00

What the query does however is return a recordset like the following:

CA $50.00
DE $25.00
LA $90.00
CA $40.00
MO $10.00
UT $33.00

A union query is used to bring the two tables together. Then a
select query is used to "combine by state code" and view the results.
What makes this problem more perplexing is that what I really want to
do is enter a Parameter on the [State Code] field to view all of the
"merged" table data. However, when I place a parameter on this
field, it will return only "some" of the results. For instance,
using the Parameter value "CA", the query returns only the following
Table B value:

CA $40.00

The $50.00 Table A value is not returned. I simply do not know what
the problem is. Any suggestions are appreciated. Thank you.
Click to expand...

It would appear you need to normalize the tables. It appears that
there
is a possibility for any state to have more than one amount and you want
to
be able to combine them That means each table should be split with the
state name in one table and the amounts in another. Data that is related
to
the state (like capital city etc.) would remain with that and data
related
to the amount (like who paid it) would be in the other table.
Click to expand...
Click to expand...
 
KC said:
Thanks Joseph,

Your quick response is appreciated.

I believe you are saying what I have heard many times in the past -
smaller and only related information is a better table construct than
larger tables. My tables all contain the same structure (ie:
identical field names, sizes etc.), just working with multiple system
data. I can normalize the tables accordingly as I believe you are
suggesting. Access even has the Table Analyzer that can do that for
you.

One further question though. Assuming I normalize the data and I now
have a smaller table containing only data directly related to the
individual state, don't I then have to use joins to bring the table
data together via a query. Say I have a table named tblState and
others named tblSystemA, tblSystemB, etc., I would then join the
tables in a query on the field [State Code] to be able to introduce
Parameters to the mix. Seems I would only be splitting the
information up only to bring it together again.
Click to expand...

Access likes to do it that way. Think of it like this. You are a
retailer. You keep your records on index cards. Each card for a customer.
You write down each sale on that card. What do you do when you run out of
card???? A new card, but then how do you put the information on those two
cards together?

Lets use one card for each customer and a slip of paper for each sale.
Each slip of paper may have one or a hundred items purchased. Each item is
on it's own slip of paper stapled to the "sale" slip.

That is the way normalization works.

Access has been designed to work that way so it works faster and better
that way than trying to bend it to any other way. Keep all like things in
the same table. A table for customers, one for sales and one for items
sold. Each item sold is related to a sale, each sale is related to a
customer.

It works.

I do want
tblSystemA, tblSystemB data to be seen together only make accesible
by [State Code] to user via Parameter value.

As you probably can tell, I am a bit unclear on data normalization.
Thank you.


:
Click to expand...
 
Add [MgmtCo] to the outer SELECT (your first query line) and to each of
the SELECTs in the union subquery.

The error you are getting is likely caused by the fact that you used
square brackets where parens are required. Look again at Mr.Steele's
example: Use parentheses to encapsulate a subquery.



KC said:
Douglas,

Thank you. I want to be sure this works and my SQL skills are limited. The
subselect statement is exactly what I have needed for a long time. Here is
the code I am using that now works:

SELECT [State Code], Total
FROM [SELECT [State Code], Total
FROM [uqryStatePayment-MERGE]
UNION ALL
SELECT [State Code], Total
FROM [uqryStatePayment-MERGE DTO DST TRAC]
]. AS Query6
WHERE [State Code] = [What State?];

Could you provide an example of what the code would read if I wanted to add
the field [MgmtCo]? This field is included in both of the tables referenced
above. I keep getting the following error message when I add the field.

Invalid bracketing of name SELECT '[State Code'

I don't understand what I am doing wrong.

Thanks in advance.
Douglas J. Steele said:
Presumably you're doing something like

SELECT [State Code], Amount
FROM [Table A]
UNION ALL
SELECT [State Code], Amount
FROM [Table B]
WHERE [State Code] = [What State?]

In that SQL, the parameter only applies to the last subselect.

To have it apply to the the UNIONed query, you either need to save the
query, and do a query against the saved query with a parameter, or try
something like

SELECT [State Code], Amount
FROM
(
SELECT [State Code], Amount
FROM [Table A]
UNION ALL
SELECT [State Code], Amount
FROM [Table B]
) AS MainQuery
WHERE [State Code] = [What State?]

(BTW, make sure you have UNION ALL, rather than simply UNION. If you only
have UNION, then if you had rows with the same state code and amount in the
two tables, you'd only get one of them, not both)


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


KC Classic said:
I have gained invaluable information using this forum for the past couple
of
years and I thank all who participate. I have been wrestling with this
problem for some time now and have finally decided the time has come to
post
my question.

I have a good understanding of the different types of queries (ie: select,
append, union, make table etc). My database is using linked tables (
which
may be part of my problem - I am not sure). I am trying to merge two
similar
tables on a field named [State Code]. In the Design View of the tables,
all
components are set exactly the same. I am using Access 2002.

Here is a sample of my table data.

Table A Table B

State Code Amount State Code Amount

CA $50.00 MO
$10.00
LA $90.00 UT
$33.00
DE $25.00 CA
$40.00

This would be my desired recordset returned by a select query on which I
could base a form and/or report.

State Code Amount

CA $90.00
DE $25.00
LA $90.00
MO $10.00
UT $33.00

What the query does however is return a recordset like the following:

CA $50.00
DE $25.00
LA $90.00
CA $40.00
MO $10.00
UT $33.00

A union query is used to bring the two tables together. Then a select
query
is used to "combine by state code" and view the results. What makes this
problem more perplexing is that what I really want to do is enter a
Parameter
on the [State Code] field to view all of the "merged" table data.
However,
when I place a parameter on this field, it will return only "some" of the
results. For instance, using the Parameter value "CA", the query returns
only the following Table B value:

CA $40.00

The $50.00 Table A value is not returned. I simply do not know what the
problem is. Any suggestions are appreciated. Thank you.
Click to expand...
Click to expand...
Click to expand...
 
SELECT [State Code], Total, [MgmtCo]
FROM [SELECT [State Code], Total, [MgmtCo]
FROM [uqryStatePayment-MERGE]
UNION ALL
SELECT [State Code], Total, [MgmtCo]
FROM [uqryStatePayment-MERGE DTO DST TRAC]
]. AS Query6
WHERE [State Code] = [What State?];



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


KC Classic said:
Douglas,

Thank you. I want to be sure this works and my SQL skills are limited.
The
subselect statement is exactly what I have needed for a long time. Here
is
the code I am using that now works:

SELECT [State Code], Total
FROM [SELECT [State Code], Total
FROM [uqryStatePayment-MERGE]
UNION ALL
SELECT [State Code], Total
FROM [uqryStatePayment-MERGE DTO DST TRAC]
]. AS Query6
WHERE [State Code] = [What State?];

Could you provide an example of what the code would read if I wanted to
add
the field [MgmtCo]? This field is included in both of the tables
referenced
above. I keep getting the following error message when I add the field.

Invalid bracketing of name SELECT '[State Code'

I don't understand what I am doing wrong.

Thanks in advance.
Douglas J. Steele said:
Presumably you're doing something like

SELECT [State Code], Amount
FROM [Table A]
UNION ALL
SELECT [State Code], Amount
FROM [Table B]
WHERE [State Code] = [What State?]

In that SQL, the parameter only applies to the last subselect.

To have it apply to the the UNIONed query, you either need to save the
query, and do a query against the saved query with a parameter, or try
something like

SELECT [State Code], Amount
FROM
(
SELECT [State Code], Amount
FROM [Table A]
UNION ALL
SELECT [State Code], Amount
FROM [Table B]
) AS MainQuery
WHERE [State Code] = [What State?]

(BTW, make sure you have UNION ALL, rather than simply UNION. If you only
have UNION, then if you had rows with the same state code and amount in
the
two tables, you'd only get one of them, not both)


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


KC Classic said:
I have gained invaluable information using this forum for the past
couple
of
years and I thank all who participate. I have been wrestling with this
problem for some time now and have finally decided the time has come to
post
my question.

I have a good understanding of the different types of queries (ie:
select,
append, union, make table etc). My database is using linked tables (
which
may be part of my problem - I am not sure). I am trying to merge two
similar
tables on a field named [State Code]. In the Design View of the
tables,
all
components are set exactly the same. I am using Access 2002.

Here is a sample of my table data.

Table A Table B

State Code Amount State Code
Amount

CA $50.00 MO
$10.00
LA $90.00 UT
$33.00
DE $25.00 CA
$40.00

This would be my desired recordset returned by a select query on which
I
could base a form and/or report.

State Code Amount

CA $90.00
DE $25.00
LA $90.00
MO $10.00
UT $33.00

What the query does however is return a recordset like the following:

CA $50.00
DE $25.00
LA $90.00
CA $40.00
MO $10.00
UT $33.00

A union query is used to bring the two tables together. Then a select
query
is used to "combine by state code" and view the results. What makes
this
problem more perplexing is that what I really want to do is enter a
Parameter
on the [State Code] field to view all of the "merged" table data.
However,
when I place a parameter on this field, it will return only "some" of
the
results. For instance, using the Parameter value "CA", the query
returns
only the following Table B value:

CA $40.00

The $50.00 Table A value is not returned. I simply do not know what
the
problem is. Any suggestions are appreciated. Thank you.
Click to expand...
Click to expand...
Click to expand...
 
Joseph said:
KC said:
Thanks Joseph,

Your quick response is appreciated.

I believe you are saying what I have heard many times in the past -
smaller and only related information is a better table construct than
larger tables. My tables all contain the same structure (ie:
identical field names, sizes etc.), just working with multiple system
data. I can normalize the tables accordingly as I believe you are
suggesting. Access even has the Table Analyzer that can do that for
you.

One further question though. Assuming I normalize the data and I now
have a smaller table containing only data directly related to the
individual state, don't I then have to use joins to bring the table
data together via a query. Say I have a table named tblState and
others named tblSystemA, tblSystemB, etc., I would then join the
tables in a query on the field [State Code] to be able to introduce
Parameters to the mix. Seems I would only be splitting the
information up only to bring it together again.
Click to expand...

Access likes to do it that way. Think of it like this. You are a
retailer. You keep your records on index cards. Each card for a customer.
You write down each sale on that card. What do you do when you run out of
card???? A new card, but then how do you put the information on those two
cards together?

Lets use one card for each customer and a slip of paper for each sale.
Each slip of paper may have one or a hundred items purchased. Each item is
on it's own slip of paper stapled to the "sale" slip.

That is the way normalization works.

Access has been designed to work that way so it works faster and better
that way than trying to bend it to any other way. Keep all like things in
the same table. A table for customers, one for sales and one for items
sold. Each item sold is related to a sale, each sale is related to a
customer.

It works.

I do want
tblSystemA, tblSystemB data to be seen together only make accesible
by [State Code] to user via Parameter value.

As you probably can tell, I am a bit unclear on data normalization.
Thank you.


:
Click to expand...
Click to expand...

Dia is Muire dhuit

Nice analogy Joseph!
 
Douglas,

I have been able to get the code you provided to run. I may have spoken too
soon as the recordset returned still does not include the data from both of
the queries referenced in the code. This is the problem I have had for a
very long time.

The code which does run is shown below along with my recordset results.

SELECT [State Code], Total, [MgmtCo]
FROM
(
SELECT [State Code], Total, [MgmtCo]
FROM [uqryStatePayment-MERGE]
UNION ALL
SELECT [State Code], Total, [MgmtCo]
FROM [uqryStatePayment-MERGE DTO DST TRAC]
) AS Query6
WHERE [State Code] = [What State?]

A sampling of the resulting recordset is posted below:

State Code Total MgmtCo
CO $75.09 L2
CO $51.43 L2
CO $77.94 L2
CO $165.06 L2
CO $146.06 L2
CO $30.20 L2
CO $15.73 L2

The requested "CO" State code records are returned but there is not data
showing for any of the records contained in query "uqryStatePayment-MERGE"
The records shown above are from the query "uqryStatePayment-MERGE DTO DST
TRAC' only. There should be records under "MgmtCo" named LA, CM, VE etc.,
etc. My thanks for your assistance so far. Do you have any ideas?

Douglas J. Steele said:
SELECT [State Code], Total, [MgmtCo]
FROM [SELECT [State Code], Total, [MgmtCo]
FROM [uqryStatePayment-MERGE]
UNION ALL
SELECT [State Code], Total, [MgmtCo]
FROM [uqryStatePayment-MERGE DTO DST TRAC]
]. AS Query6
WHERE [State Code] = [What State?];



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


KC Classic said:
Douglas,

Thank you. I want to be sure this works and my SQL skills are limited.
The
subselect statement is exactly what I have needed for a long time. Here
is
the code I am using that now works:

SELECT [State Code], Total
FROM [SELECT [State Code], Total
FROM [uqryStatePayment-MERGE]
UNION ALL
SELECT [State Code], Total
FROM [uqryStatePayment-MERGE DTO DST TRAC]
]. AS Query6
WHERE [State Code] = [What State?];

Could you provide an example of what the code would read if I wanted to
add
the field [MgmtCo]? This field is included in both of the tables
referenced
above. I keep getting the following error message when I add the field.

Invalid bracketing of name SELECT '[State Code'

I don't understand what I am doing wrong.

Thanks in advance.
Douglas J. Steele said:
Presumably you're doing something like

SELECT [State Code], Amount
FROM [Table A]
UNION ALL
SELECT [State Code], Amount
FROM [Table B]
WHERE [State Code] = [What State?]

In that SQL, the parameter only applies to the last subselect.

To have it apply to the the UNIONed query, you either need to save the
query, and do a query against the saved query with a parameter, or try
something like

SELECT [State Code], Amount
FROM
(
SELECT [State Code], Amount
FROM [Table A]
UNION ALL
SELECT [State Code], Amount
FROM [Table B]
) AS MainQuery
WHERE [State Code] = [What State?]

(BTW, make sure you have UNION ALL, rather than simply UNION. If you only
have UNION, then if you had rows with the same state code and amount in
the
two tables, you'd only get one of them, not both)


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I have gained invaluable information using this forum for the past
couple
of
years and I thank all who participate. I have been wrestling with this
problem for some time now and have finally decided the time has come to
post
my question.

I have a good understanding of the different types of queries (ie:
select,
append, union, make table etc). My database is using linked tables (
which
may be part of my problem - I am not sure). I am trying to merge two
similar
tables on a field named [State Code]. In the Design View of the
tables,
all
components are set exactly the same. I am using Access 2002.

Here is a sample of my table data.

Table A Table B

State Code Amount State Code
Amount

CA $50.00 MO
$10.00
LA $90.00 UT
$33.00
DE $25.00 CA
$40.00

This would be my desired recordset returned by a select query on which
I
could base a form and/or report.

State Code Amount

CA $90.00
DE $25.00
LA $90.00
MO $10.00
UT $33.00

What the query does however is return a recordset like the following:

CA $50.00
DE $25.00
LA $90.00
CA $40.00
MO $10.00
UT $33.00

A union query is used to bring the two tables together. Then a select
query
is used to "combine by state code" and view the results. What makes
this
problem more perplexing is that what I really want to do is enter a
Parameter
on the [State Code] field to view all of the "merged" table data.
However,
when I place a parameter on this field, it will return only "some" of
the
results. For instance, using the Parameter value "CA", the query
returns
only the following Table B value:

CA $40.00

The $50.00 Table A value is not returned. I simply do not know what
the
problem is. Any suggestions are appreciated. Thank you.
Click to expand...
Click to expand...
Click to expand...
 
Try small pieces.

If you strictly use

SELECT [State Code], Total, [MgmtCo]
FROM [uqryStatePayment-MERGE]

what do you get?

What about with

SELECT [State Code], Total, [MgmtCo]
FROM [uqryStatePayment-MERGE]
WHERE [State Code] = [What State?]


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


KC Classic said:
Douglas,

I have been able to get the code you provided to run. I may have spoken
too
soon as the recordset returned still does not include the data from both
of
the queries referenced in the code. This is the problem I have had for a
very long time.

The code which does run is shown below along with my recordset results.

SELECT [State Code], Total, [MgmtCo]
FROM
(
SELECT [State Code], Total, [MgmtCo]
FROM [uqryStatePayment-MERGE]
UNION ALL
SELECT [State Code], Total, [MgmtCo]
FROM [uqryStatePayment-MERGE DTO DST TRAC]
) AS Query6
WHERE [State Code] = [What State?]

A sampling of the resulting recordset is posted below:

State Code Total MgmtCo
CO $75.09 L2
CO $51.43 L2
CO $77.94 L2
CO $165.06 L2
CO $146.06 L2
CO $30.20 L2
CO $15.73 L2

The requested "CO" State code records are returned but there is not data
showing for any of the records contained in query "uqryStatePayment-MERGE"
The records shown above are from the query "uqryStatePayment-MERGE DTO DST
TRAC' only. There should be records under "MgmtCo" named LA, CM, VE etc.,
etc. My thanks for your assistance so far. Do you have any ideas?

Douglas J. Steele said:
SELECT [State Code], Total, [MgmtCo]
FROM [SELECT [State Code], Total, [MgmtCo]
FROM [uqryStatePayment-MERGE]
UNION ALL
SELECT [State Code], Total, [MgmtCo]
FROM [uqryStatePayment-MERGE DTO DST TRAC]
]. AS Query6
WHERE [State Code] = [What State?];



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


KC Classic said:
Douglas,

Thank you. I want to be sure this works and my SQL skills are limited.
The
subselect statement is exactly what I have needed for a long time.
Here
is
the code I am using that now works:

SELECT [State Code], Total
FROM [SELECT [State Code], Total
FROM [uqryStatePayment-MERGE]
UNION ALL
SELECT [State Code], Total
FROM [uqryStatePayment-MERGE DTO DST TRAC]
]. AS Query6
WHERE [State Code] = [What State?];

Could you provide an example of what the code would read if I wanted to
add
the field [MgmtCo]? This field is included in both of the tables
referenced
above. I keep getting the following error message when I add the
field.

Invalid bracketing of name SELECT '[State Code'

I don't understand what I am doing wrong.

Thanks in advance.
:

Presumably you're doing something like

SELECT [State Code], Amount
FROM [Table A]
UNION ALL
SELECT [State Code], Amount
FROM [Table B]
WHERE [State Code] = [What State?]

In that SQL, the parameter only applies to the last subselect.

To have it apply to the the UNIONed query, you either need to save the
query, and do a query against the saved query with a parameter, or try
something like

SELECT [State Code], Amount
FROM
(
SELECT [State Code], Amount
FROM [Table A]
UNION ALL
SELECT [State Code], Amount
FROM [Table B]
) AS MainQuery
WHERE [State Code] = [What State?]

(BTW, make sure you have UNION ALL, rather than simply UNION. If you
only
have UNION, then if you had rows with the same state code and amount
in
the
two tables, you'd only get one of them, not both)


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I have gained invaluable information using this forum for the past
couple
of
years and I thank all who participate. I have been wrestling with
this
problem for some time now and have finally decided the time has come
to
post
my question.

I have a good understanding of the different types of queries (ie:
select,
append, union, make table etc). My database is using linked tables
(
which
may be part of my problem - I am not sure). I am trying to merge
two
similar
tables on a field named [State Code]. In the Design View of the
tables,
all
components are set exactly the same. I am using Access 2002.

Here is a sample of my table data.

Table A Table B

State Code Amount State Code
Amount

CA $50.00 MO
$10.00
LA $90.00 UT
$33.00
DE $25.00 CA
$40.00

This would be my desired recordset returned by a select query on
which
I
could base a form and/or report.

State Code Amount

CA $90.00
DE $25.00
LA $90.00
MO $10.00
UT $33.00

What the query does however is return a recordset like the
following:

CA $50.00
DE $25.00
LA $90.00
CA $40.00
MO $10.00
UT $33.00

A union query is used to bring the two tables together. Then a
select
query
is used to "combine by state code" and view the results. What makes
this
problem more perplexing is that what I really want to do is enter a
Parameter
on the [State Code] field to view all of the "merged" table data.
However,
when I place a parameter on this field, it will return only "some"
of
the
results. For instance, using the Parameter value "CA", the query
returns
only the following Table B value:

CA $40.00

The $50.00 Table A value is not returned. I simply do not know what
the
problem is. Any suggestions are appreciated. Thank you.
Click to expand...
Click to expand...
Click to expand...
 
Douglas,

Thank you for your quick response again. The problem must be the inclusion
of the parameter. I ran the query both ways as you suggested. The first
recordset returns all [State Code], [MgmtCo] and [Total] field data as
requested. No problem.

When running the code with the parameter selected, no records at all are
returned. This is true no matter which [State Code] value is selected. So,
basically, the parameter works on the union query "uqryStatePayment-MERGE DTO
DST TRAC" but not on the union query "uqryStatePayment-MERGE".

There are queries behind the union queries but no parameters are included.
All I can think of is that the computer is not understanding the parameter
requested correctly. Does upper case/lower case matter? Does formatting
matter? I have tried the parameter both ways with no luck. What should I
check now?

Douglas J. Steele said:
Try small pieces.

If you strictly use

SELECT [State Code], Total, [MgmtCo]
FROM [uqryStatePayment-MERGE]

what do you get?

What about with

SELECT [State Code], Total, [MgmtCo]
FROM [uqryStatePayment-MERGE]
WHERE [State Code] = [What State?]


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


KC Classic said:
Douglas,

I have been able to get the code you provided to run. I may have spoken
too
soon as the recordset returned still does not include the data from both
of
the queries referenced in the code. This is the problem I have had for a
very long time.

The code which does run is shown below along with my recordset results.

SELECT [State Code], Total, [MgmtCo]
FROM
(
SELECT [State Code], Total, [MgmtCo]
FROM [uqryStatePayment-MERGE]
UNION ALL
SELECT [State Code], Total, [MgmtCo]
FROM [uqryStatePayment-MERGE DTO DST TRAC]
) AS Query6
WHERE [State Code] = [What State?]

A sampling of the resulting recordset is posted below:

State Code Total MgmtCo
CO $75.09 L2
CO $51.43 L2
CO $77.94 L2
CO $165.06 L2
CO $146.06 L2
CO $30.20 L2
CO $15.73 L2

The requested "CO" State code records are returned but there is not data
showing for any of the records contained in query "uqryStatePayment-MERGE"
The records shown above are from the query "uqryStatePayment-MERGE DTO DST
TRAC' only. There should be records under "MgmtCo" named LA, CM, VE etc.,
etc. My thanks for your assistance so far. Do you have any ideas?

Douglas J. Steele said:
SELECT [State Code], Total, [MgmtCo]
FROM [SELECT [State Code], Total, [MgmtCo]
FROM [uqryStatePayment-MERGE]
UNION ALL
SELECT [State Code], Total, [MgmtCo]
FROM [uqryStatePayment-MERGE DTO DST TRAC]
]. AS Query6
WHERE [State Code] = [What State?];



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Douglas,

Thank you. I want to be sure this works and my SQL skills are limited.
The
subselect statement is exactly what I have needed for a long time.
Here
is
the code I am using that now works:

SELECT [State Code], Total
FROM [SELECT [State Code], Total
FROM [uqryStatePayment-MERGE]
UNION ALL
SELECT [State Code], Total
FROM [uqryStatePayment-MERGE DTO DST TRAC]
]. AS Query6
WHERE [State Code] = [What State?];

Could you provide an example of what the code would read if I wanted to
add
the field [MgmtCo]? This field is included in both of the tables
referenced
above. I keep getting the following error message when I add the
field.

Invalid bracketing of name SELECT '[State Code'

I don't understand what I am doing wrong.

Thanks in advance.
:

Presumably you're doing something like

SELECT [State Code], Amount
FROM [Table A]
UNION ALL
SELECT [State Code], Amount
FROM [Table B]
WHERE [State Code] = [What State?]

In that SQL, the parameter only applies to the last subselect.

To have it apply to the the UNIONed query, you either need to save the
query, and do a query against the saved query with a parameter, or try
something like

SELECT [State Code], Amount
FROM
(
SELECT [State Code], Amount
FROM [Table A]
UNION ALL
SELECT [State Code], Amount
FROM [Table B]
) AS MainQuery
WHERE [State Code] = [What State?]

(BTW, make sure you have UNION ALL, rather than simply UNION. If you
only
have UNION, then if you had rows with the same state code and amount
in
the
two tables, you'd only get one of them, not both)


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I have gained invaluable information using this forum for the past
couple
of
years and I thank all who participate. I have been wrestling with
this
problem for some time now and have finally decided the time has come
to
post
my question.

I have a good understanding of the different types of queries (ie:
select,
append, union, make table etc). My database is using linked tables
(
which
may be part of my problem - I am not sure). I am trying to merge
two
similar
tables on a field named [State Code]. In the Design View of the
tables,
all
components are set exactly the same. I am using Access 2002.

Here is a sample of my table data.

Table A Table B

State Code Amount State Code
Amount

CA $50.00 MO
$10.00
LA $90.00 UT
$33.00
DE $25.00 CA
$40.00

This would be my desired recordset returned by a select query on
which
I
could base a form and/or report.

State Code Amount

CA $90.00
DE $25.00
LA $90.00
MO $10.00
UT $33.00

What the query does however is return a recordset like the
following:

CA $50.00
DE $25.00
LA $90.00
CA $40.00
MO $10.00
UT $33.00

A union query is used to bring the two tables together. Then a
select
query
is used to "combine by state code" and view the results. What makes
this
problem more perplexing is that what I really want to do is enter a
Parameter
on the [State Code] field to view all of the "merged" table data.
However,
when I place a parameter on this field, it will return only "some"
of
the
results. For instance, using the Parameter value "CA", the query
returns
only the following Table B value:

CA $40.00

The $50.00 Table A value is not returned. I simply do not know what
the
problem is. Any suggestions are appreciated. Thank you.
Click to expand...
Click to expand...
Click to expand...
 
You didn't happen to use a Lookup field in the table on which the query is
built, did you?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


KC Classic said:
Douglas,

Thank you for your quick response again. The problem must be the inclusion
of the parameter. I ran the query both ways as you suggested. The first
recordset returns all [State Code], [MgmtCo] and [Total] field data as
requested. No problem.

When running the code with the parameter selected, no records at all are
returned. This is true no matter which [State Code] value is selected.
So,
basically, the parameter works on the union query "uqryStatePayment-MERGE
DTO
DST TRAC" but not on the union query "uqryStatePayment-MERGE".

There are queries behind the union queries but no parameters are included.
All I can think of is that the computer is not understanding the parameter
requested correctly. Does upper case/lower case matter? Does formatting
matter? I have tried the parameter both ways with no luck. What should I
check now?

Douglas J. Steele said:
Try small pieces.

If you strictly use

SELECT [State Code], Total, [MgmtCo]
FROM [uqryStatePayment-MERGE]

what do you get?

What about with

SELECT [State Code], Total, [MgmtCo]
FROM [uqryStatePayment-MERGE]
WHERE [State Code] = [What State?]


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


KC Classic said:
Douglas,

I have been able to get the code you provided to run. I may have
spoken
too
soon as the recordset returned still does not include the data from
both
of
the queries referenced in the code. This is the problem I have had for
a
very long time.

The code which does run is shown below along with my recordset results.

SELECT [State Code], Total, [MgmtCo]
FROM
(
SELECT [State Code], Total, [MgmtCo]
FROM [uqryStatePayment-MERGE]
UNION ALL
SELECT [State Code], Total, [MgmtCo]
FROM [uqryStatePayment-MERGE DTO DST TRAC]
) AS Query6
WHERE [State Code] = [What State?]

A sampling of the resulting recordset is posted below:

State Code Total MgmtCo
CO $75.09 L2
CO $51.43 L2
CO $77.94 L2
CO $165.06 L2
CO $146.06 L2
CO $30.20 L2
CO $15.73 L2

The requested "CO" State code records are returned but there is not
data
showing for any of the records contained in query
"uqryStatePayment-MERGE"
The records shown above are from the query "uqryStatePayment-MERGE DTO
DST
TRAC' only. There should be records under "MgmtCo" named LA, CM, VE
etc.,
etc. My thanks for your assistance so far. Do you have any ideas?

:

SELECT [State Code], Total, [MgmtCo]
FROM [SELECT [State Code], Total, [MgmtCo]
FROM [uqryStatePayment-MERGE]
UNION ALL
SELECT [State Code], Total, [MgmtCo]
FROM [uqryStatePayment-MERGE DTO DST TRAC]
]. AS Query6
WHERE [State Code] = [What State?];



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Douglas,

Thank you. I want to be sure this works and my SQL skills are
limited.
The
subselect statement is exactly what I have needed for a long time.
Here
is
the code I am using that now works:

SELECT [State Code], Total
FROM [SELECT [State Code], Total
FROM [uqryStatePayment-MERGE]
UNION ALL
SELECT [State Code], Total
FROM [uqryStatePayment-MERGE DTO DST TRAC]
]. AS Query6
WHERE [State Code] = [What State?];

Could you provide an example of what the code would read if I wanted
to
add
the field [MgmtCo]? This field is included in both of the tables
referenced
above. I keep getting the following error message when I add the
field.

Invalid bracketing of name SELECT '[State Code'

I don't understand what I am doing wrong.

Thanks in advance.
:

Presumably you're doing something like

SELECT [State Code], Amount
FROM [Table A]
UNION ALL
SELECT [State Code], Amount
FROM [Table B]
WHERE [State Code] = [What State?]

In that SQL, the parameter only applies to the last subselect.

To have it apply to the the UNIONed query, you either need to save
the
query, and do a query against the saved query with a parameter, or
try
something like

SELECT [State Code], Amount
FROM
(
SELECT [State Code], Amount
FROM [Table A]
UNION ALL
SELECT [State Code], Amount
FROM [Table B]
) AS MainQuery
WHERE [State Code] = [What State?]

(BTW, make sure you have UNION ALL, rather than simply UNION. If
you
only
have UNION, then if you had rows with the same state code and
amount
in
the
two tables, you'd only get one of them, not both)


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


message
I have gained invaluable information using this forum for the past
couple
of
years and I thank all who participate. I have been wrestling
with
this
problem for some time now and have finally decided the time has
come
to
post
my question.

I have a good understanding of the different types of queries
(ie:
select,
append, union, make table etc). My database is using linked
tables
(
which
may be part of my problem - I am not sure). I am trying to merge
two
similar
tables on a field named [State Code]. In the Design View of the
tables,
all
components are set exactly the same. I am using Access 2002.

Here is a sample of my table data.

Table A Table B

State Code Amount State Code
Amount

CA $50.00 MO
$10.00
LA $90.00 UT
$33.00
DE $25.00 CA
$40.00

This would be my desired recordset returned by a select query on
which
I
could base a form and/or report.

State Code Amount

CA $90.00
DE $25.00
LA $90.00
MO $10.00
UT $33.00

What the query does however is return a recordset like the
following:

CA $50.00
DE $25.00
LA $90.00
CA $40.00
MO $10.00
UT $33.00

A union query is used to bring the two tables together. Then a
select
query
is used to "combine by state code" and view the results. What
makes
this
problem more perplexing is that what I really want to do is enter
a
Parameter
on the [State Code] field to view all of the "merged" table data.
However,
when I place a parameter on this field, it will return only
"some"
of
the
results. For instance, using the Parameter value "CA", the query
returns
only the following Table B value:

CA $40.00

The $50.00 Table A value is not returned. I simply do not know
what
the
problem is. Any suggestions are appreciated. Thank you.
Click to expand...
Click to expand...
Click to expand...
 
Hello Again Douglas,

No Lookup field on any of the tables involved in these queries.

Maybe the general database flow will help. Identical system data is
appended to 4 identical master tables. Those master tables are queried and
summarized daily. The following SQL in a query was necessary to get my
summary query to show any records at all. I would imagine this has something
to do with the problem. The SQL below is representative of only one of the 4
tables that is ultimately combined via the union query we have previously
been discussing (ie. DLW Daily Withholding Table, DTO Daily Withholding
Table, etc). At present, the same join is made with each of the four tables.

SELECT [DFH Daily Withholding Table].[Supersheet Date], [DFH Daily
Withholding Table].[Trade Date], [DFH Daily Withholding Table].[State Code],
[DFH Daily Withholding Table].Fund, [DFH Daily Withholding Table].Account,
Sum([DFH Daily Withholding Table].Amount) AS [DFH WH Amount]
FROM tblState RIGHT JOIN [DFH Daily Withholding Table] ON tblState.[State
Code] = [DFH Daily Withholding Table].[State Code]
GROUP BY [DFH Daily Withholding Table].[Supersheet Date], [DFH Daily
Withholding Table].[Trade Date], [DFH Daily Withholding Table].[State Code],
[DFH Daily Withholding Table].Fund, [DFH Daily Withholding Table].Account;

If I take away the RIGHT JOIN with [tblState], no records are returned.
Basically, by joining with [tblState], I was able to continue in the
development of this database. I would imagine this is part of the problem.
Your thoughts are greatly appreciated!

Douglas J. Steele said:
You didn't happen to use a Lookup field in the table on which the query is
built, did you?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


KC Classic said:
Douglas,

Thank you for your quick response again. The problem must be the inclusion
of the parameter. I ran the query both ways as you suggested. The first
recordset returns all [State Code], [MgmtCo] and [Total] field data as
requested. No problem.

When running the code with the parameter selected, no records at all are
returned. This is true no matter which [State Code] value is selected.
So,
basically, the parameter works on the union query "uqryStatePayment-MERGE
DTO
DST TRAC" but not on the union query "uqryStatePayment-MERGE".

There are queries behind the union queries but no parameters are included.
All I can think of is that the computer is not understanding the parameter
requested correctly. Does upper case/lower case matter? Does formatting
matter? I have tried the parameter both ways with no luck. What should I
check now?

Douglas J. Steele said:
Try small pieces.

If you strictly use

SELECT [State Code], Total, [MgmtCo]
FROM [uqryStatePayment-MERGE]

what do you get?

What about with

SELECT [State Code], Total, [MgmtCo]
FROM [uqryStatePayment-MERGE]
WHERE [State Code] = [What State?]


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Douglas,

I have been able to get the code you provided to run. I may have
spoken
too
soon as the recordset returned still does not include the data from
both
of
the queries referenced in the code. This is the problem I have had for
a
very long time.

The code which does run is shown below along with my recordset results.

SELECT [State Code], Total, [MgmtCo]
FROM
(
SELECT [State Code], Total, [MgmtCo]
FROM [uqryStatePayment-MERGE]
UNION ALL
SELECT [State Code], Total, [MgmtCo]
FROM [uqryStatePayment-MERGE DTO DST TRAC]
) AS Query6
WHERE [State Code] = [What State?]

A sampling of the resulting recordset is posted below:

State Code Total MgmtCo
CO $75.09 L2
CO $51.43 L2
CO $77.94 L2
CO $165.06 L2
CO $146.06 L2
CO $30.20 L2
CO $15.73 L2

The requested "CO" State code records are returned but there is not
data
showing for any of the records contained in query
"uqryStatePayment-MERGE"
The records shown above are from the query "uqryStatePayment-MERGE DTO
DST
TRAC' only. There should be records under "MgmtCo" named LA, CM, VE
etc.,
etc. My thanks for your assistance so far. Do you have any ideas?

:

SELECT [State Code], Total, [MgmtCo]
FROM [SELECT [State Code], Total, [MgmtCo]
FROM [uqryStatePayment-MERGE]
UNION ALL
SELECT [State Code], Total, [MgmtCo]
FROM [uqryStatePayment-MERGE DTO DST TRAC]
]. AS Query6
WHERE [State Code] = [What State?];



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Douglas,

Thank you. I want to be sure this works and my SQL skills are
limited.
The
subselect statement is exactly what I have needed for a long time.
Here
is
the code I am using that now works:

SELECT [State Code], Total
FROM [SELECT [State Code], Total
FROM [uqryStatePayment-MERGE]
UNION ALL
SELECT [State Code], Total
FROM [uqryStatePayment-MERGE DTO DST TRAC]
]. AS Query6
WHERE [State Code] = [What State?];

Could you provide an example of what the code would read if I wanted
to
add
the field [MgmtCo]? This field is included in both of the tables
referenced
above. I keep getting the following error message when I add the
field.

Invalid bracketing of name SELECT '[State Code'

I don't understand what I am doing wrong.

Thanks in advance.
:

Presumably you're doing something like

SELECT [State Code], Amount
FROM [Table A]
UNION ALL
SELECT [State Code], Amount
FROM [Table B]
WHERE [State Code] = [What State?]

In that SQL, the parameter only applies to the last subselect.

To have it apply to the the UNIONed query, you either need to save
the
query, and do a query against the saved query with a parameter, or
try
something like

SELECT [State Code], Amount
FROM
(
SELECT [State Code], Amount
FROM [Table A]
UNION ALL
SELECT [State Code], Amount
FROM [Table B]
) AS MainQuery
WHERE [State Code] = [What State?]

(BTW, make sure you have UNION ALL, rather than simply UNION. If
you
only
have UNION, then if you had rows with the same state code and
amount
in
the
two tables, you'd only get one of them, not both)


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


message
I have gained invaluable information using this forum for the past
couple
of
years and I thank all who participate. I have been wrestling
with
this
problem for some time now and have finally decided the time has
come
to
post
my question.

I have a good understanding of the different types of queries
(ie:
select,
append, union, make table etc). My database is using linked
tables
(
which
may be part of my problem - I am not sure). I am trying to merge
two
similar
tables on a field named [State Code]. In the Design View of the
tables,
all
components are set exactly the same. I am using Access 2002.

Here is a sample of my table data.

Table A Table B

State Code Amount State Code
Amount

CA $50.00 MO
$10.00
LA $90.00 UT
$33.00
DE $25.00 CA
$40.00

This would be my desired recordset returned by a select query on
which
I
could base a form and/or report.

State Code Amount

CA $90.00
DE $25.00
LA $90.00
MO $10.00
UT $33.00

What the query does however is return a recordset like the
following:

CA $50.00
DE $25.00
LA $90.00
CA $40.00
MO $10.00
UT $33.00

A union query is used to bring the two tables together. Then a
select
query
is used to "combine by state code" and view the results. What
makes
this
problem more perplexing is that what I really want to do is enter
a
Parameter
on the [State Code] field to view all of the "merged" table data.
However,
when I place a parameter on this field, it will return only
"some"
of
the
results. For instance, using the Parameter value "CA", the query
returns
only the following Table B value:

CA $40.00

The $50.00 Table A value is not returned. I simply do not know
what
the
problem is. Any suggestions are appreciated. Thank you.
Click to expand...
Click to expand...
Click to expand...
 
This makes no sense to me at all!

Your query, which you say doesn't return any records without the RIGHT JOIN,
doesn't mention tblState anywhere else in the query. From the Help file,
""Right outer joins include all of the records from the second (right) of
two tables, even if there are no matching values for records in the first
(left) table." In other words, the RIGHT JOIN shouldn't be doing anything!

Hopefully some lurker will chime in with a suggestion.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


KC Classic said:
Hello Again Douglas,

No Lookup field on any of the tables involved in these queries.

Maybe the general database flow will help. Identical system data is
appended to 4 identical master tables. Those master tables are queried and
summarized daily. The following SQL in a query was necessary to get my
summary query to show any records at all. I would imagine this has
something
to do with the problem. The SQL below is representative of only one of
the 4
tables that is ultimately combined via the union query we have previously
been discussing (ie. DLW Daily Withholding Table, DTO Daily Withholding
Table, etc). At present, the same join is made with each of the four
tables.

SELECT [DFH Daily Withholding Table].[Supersheet Date], [DFH Daily
Withholding Table].[Trade Date], [DFH Daily Withholding Table].[State
Code],
[DFH Daily Withholding Table].Fund, [DFH Daily Withholding Table].Account,
Sum([DFH Daily Withholding Table].Amount) AS [DFH WH Amount]
FROM tblState RIGHT JOIN [DFH Daily Withholding Table] ON tblState.[State
Code] = [DFH Daily Withholding Table].[State Code]
GROUP BY [DFH Daily Withholding Table].[Supersheet Date], [DFH Daily
Withholding Table].[Trade Date], [DFH Daily Withholding Table].[State
Code],
[DFH Daily Withholding Table].Fund, [DFH Daily Withholding Table].Account;

If I take away the RIGHT JOIN with [tblState], no records are returned.
Basically, by joining with [tblState], I was able to continue in the
development of this database. I would imagine this is part of the
problem.
Your thoughts are greatly appreciated!

Douglas J. Steele said:
You didn't happen to use a Lookup field in the table on which the query
is
built, did you?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


KC Classic said:
Douglas,

Thank you for your quick response again. The problem must be the
inclusion
of the parameter. I ran the query both ways as you suggested. The
first
recordset returns all [State Code], [MgmtCo] and [Total] field data as
requested. No problem.

When running the code with the parameter selected, no records at all
are
returned. This is true no matter which [State Code] value is selected.
So,
basically, the parameter works on the union query
"uqryStatePayment-MERGE
DTO
DST TRAC" but not on the union query "uqryStatePayment-MERGE".

There are queries behind the union queries but no parameters are
included.
All I can think of is that the computer is not understanding the
parameter
requested correctly. Does upper case/lower case matter? Does
formatting
matter? I have tried the parameter both ways with no luck. What
should I
check now?

:

Try small pieces.

If you strictly use

SELECT [State Code], Total, [MgmtCo]
FROM [uqryStatePayment-MERGE]

what do you get?

What about with

SELECT [State Code], Total, [MgmtCo]
FROM [uqryStatePayment-MERGE]
WHERE [State Code] = [What State?]


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Douglas,

I have been able to get the code you provided to run. I may have
spoken
too
soon as the recordset returned still does not include the data from
both
of
the queries referenced in the code. This is the problem I have had
for
a
very long time.

The code which does run is shown below along with my recordset
results.

SELECT [State Code], Total, [MgmtCo]
FROM
(
SELECT [State Code], Total, [MgmtCo]
FROM [uqryStatePayment-MERGE]
UNION ALL
SELECT [State Code], Total, [MgmtCo]
FROM [uqryStatePayment-MERGE DTO DST TRAC]
) AS Query6
WHERE [State Code] = [What State?]

A sampling of the resulting recordset is posted below:

State Code Total MgmtCo
CO $75.09 L2
CO $51.43 L2
CO $77.94 L2
CO $165.06 L2
CO $146.06 L2
CO $30.20 L2
CO $15.73 L2

The requested "CO" State code records are returned but there is not
data
showing for any of the records contained in query
"uqryStatePayment-MERGE"
The records shown above are from the query "uqryStatePayment-MERGE
DTO
DST
TRAC' only. There should be records under "MgmtCo" named LA, CM, VE
etc.,
etc. My thanks for your assistance so far. Do you have any ideas?

:

SELECT [State Code], Total, [MgmtCo]
FROM [SELECT [State Code], Total, [MgmtCo]
FROM [uqryStatePayment-MERGE]
UNION ALL
SELECT [State Code], Total, [MgmtCo]
FROM [uqryStatePayment-MERGE DTO DST TRAC]
]. AS Query6
WHERE [State Code] = [What State?];



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Douglas,

Thank you. I want to be sure this works and my SQL skills are
limited.
The
subselect statement is exactly what I have needed for a long
time.
Here
is
the code I am using that now works:

SELECT [State Code], Total
FROM [SELECT [State Code], Total
FROM [uqryStatePayment-MERGE]
UNION ALL
SELECT [State Code], Total
FROM [uqryStatePayment-MERGE DTO DST TRAC]
]. AS Query6
WHERE [State Code] = [What State?];

Could you provide an example of what the code would read if I
wanted
to
add
the field [MgmtCo]? This field is included in both of the tables
referenced
above. I keep getting the following error message when I add the
field.

Invalid bracketing of name SELECT '[State Code'

I don't understand what I am doing wrong.

Thanks in advance.
:

Presumably you're doing something like

SELECT [State Code], Amount
FROM [Table A]
UNION ALL
SELECT [State Code], Amount
FROM [Table B]
WHERE [State Code] = [What State?]

In that SQL, the parameter only applies to the last subselect.

To have it apply to the the UNIONed query, you either need to
save
the
query, and do a query against the saved query with a parameter,
or
try
something like

SELECT [State Code], Amount
FROM
(
SELECT [State Code], Amount
FROM [Table A]
UNION ALL
SELECT [State Code], Amount
FROM [Table B]
) AS MainQuery
WHERE [State Code] = [What State?]

(BTW, make sure you have UNION ALL, rather than simply UNION. If
you
only
have UNION, then if you had rows with the same state code and
amount
in
the
two tables, you'd only get one of them, not both)


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


message
I have gained invaluable information using this forum for the
past
couple
of
years and I thank all who participate. I have been wrestling
with
this
problem for some time now and have finally decided the time
has
come
to
post
my question.

I have a good understanding of the different types of queries
(ie:
select,
append, union, make table etc). My database is using linked
tables
(
which
may be part of my problem - I am not sure). I am trying to
merge
two
similar
tables on a field named [State Code]. In the Design View of
the
tables,
all
components are set exactly the same. I am using Access 2002.

Here is a sample of my table data.

Table A Table B

State Code Amount State Code
Amount

CA $50.00 MO
$10.00
LA $90.00 UT
$33.00
DE $25.00 CA
$40.00

This would be my desired recordset returned by a select query
on
which
I
could base a form and/or report.

State Code Amount

CA $90.00
DE $25.00
LA $90.00
MO $10.00
UT $33.00

What the query does however is return a recordset like the
following:

CA $50.00
DE $25.00
LA $90.00
CA $40.00
MO $10.00
UT $33.00

A union query is used to bring the two tables together. Then
a
select
query
is used to "combine by state code" and view the results. What
makes
this
problem more perplexing is that what I really want to do is
enter
a
Parameter
on the [State Code] field to view all of the "merged" table
data.
However,
when I place a parameter on this field, it will return only
"some"
of
the
results. For instance, using the Parameter value "CA", the
query
returns
only the following Table B value:

CA $40.00

The $50.00 Table A value is not returned. I simply do not
know
what
the
problem is. Any suggestions are appreciated. Thank you.
Click to expand...
Click to expand...
Click to expand...
 
Douglas,

You are absolutely correct about the [tblState] join. The RIGHT JOIN was
not doing a thing! My apologies for the mistake. Without the [tblState]
table involved, the query does produce a recordset that equals the total
records in the underlying table.

What I should have said was that when I add the parameter [What State?] for
the field [State Code], no records are returned. At the time the database
was developed, I was thinking the join had to be set up to be able to query
using a parameter. I have since learned differently.

I love to use Microsoft Access and hope to get better at using the program.
I promise this is my last post regarding this issue. I do appreciate all of
your time and effort today and this past week. Yourself and all of the MVP's
responses are clear, accurate and to the point on virtually everything I have
ever read on the subject here. I thank you all for what you do.

Douglas J. Steele said:
This makes no sense to me at all!

Your query, which you say doesn't return any records without the RIGHT JOIN,
doesn't mention tblState anywhere else in the query. From the Help file,
""Right outer joins include all of the records from the second (right) of
two tables, even if there are no matching values for records in the first
(left) table." In other words, the RIGHT JOIN shouldn't be doing anything!

Hopefully some lurker will chime in with a suggestion.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


KC Classic said:
Hello Again Douglas,

No Lookup field on any of the tables involved in these queries.

Maybe the general database flow will help. Identical system data is
appended to 4 identical master tables. Those master tables are queried and
summarized daily. The following SQL in a query was necessary to get my
summary query to show any records at all. I would imagine this has
something
to do with the problem. The SQL below is representative of only one of
the 4
tables that is ultimately combined via the union query we have previously
been discussing (ie. DLW Daily Withholding Table, DTO Daily Withholding
Table, etc). At present, the same join is made with each of the four
tables.

SELECT [DFH Daily Withholding Table].[Supersheet Date], [DFH Daily
Withholding Table].[Trade Date], [DFH Daily Withholding Table].[State
Code],
[DFH Daily Withholding Table].Fund, [DFH Daily Withholding Table].Account,
Sum([DFH Daily Withholding Table].Amount) AS [DFH WH Amount]
FROM tblState RIGHT JOIN [DFH Daily Withholding Table] ON tblState.[State
Code] = [DFH Daily Withholding Table].[State Code]
GROUP BY [DFH Daily Withholding Table].[Supersheet Date], [DFH Daily
Withholding Table].[Trade Date], [DFH Daily Withholding Table].[State
Code],
[DFH Daily Withholding Table].Fund, [DFH Daily Withholding Table].Account;

If I take away the RIGHT JOIN with [tblState], no records are returned.
Basically, by joining with [tblState], I was able to continue in the
development of this database. I would imagine this is part of the
problem.
Your thoughts are greatly appreciated!

Douglas J. Steele said:
You didn't happen to use a Lookup field in the table on which the query
is
built, did you?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Douglas,

Thank you for your quick response again. The problem must be the
inclusion
of the parameter. I ran the query both ways as you suggested. The
first
recordset returns all [State Code], [MgmtCo] and [Total] field data as
requested. No problem.

When running the code with the parameter selected, no records at all
are
returned. This is true no matter which [State Code] value is selected.
So,
basically, the parameter works on the union query
"uqryStatePayment-MERGE
DTO
DST TRAC" but not on the union query "uqryStatePayment-MERGE".

There are queries behind the union queries but no parameters are
included.
All I can think of is that the computer is not understanding the
parameter
requested correctly. Does upper case/lower case matter? Does
formatting
matter? I have tried the parameter both ways with no luck. What
should I
check now?

:

Try small pieces.

If you strictly use

SELECT [State Code], Total, [MgmtCo]
FROM [uqryStatePayment-MERGE]

what do you get?

What about with

SELECT [State Code], Total, [MgmtCo]
FROM [uqryStatePayment-MERGE]
WHERE [State Code] = [What State?]


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Douglas,

I have been able to get the code you provided to run. I may have
spoken
too
soon as the recordset returned still does not include the data from
both
of
the queries referenced in the code. This is the problem I have had
for
a
very long time.

The code which does run is shown below along with my recordset
results.

SELECT [State Code], Total, [MgmtCo]
FROM
(
SELECT [State Code], Total, [MgmtCo]
FROM [uqryStatePayment-MERGE]
UNION ALL
SELECT [State Code], Total, [MgmtCo]
FROM [uqryStatePayment-MERGE DTO DST TRAC]
) AS Query6
WHERE [State Code] = [What State?]

A sampling of the resulting recordset is posted below:

State Code Total MgmtCo
CO $75.09 L2
CO $51.43 L2
CO $77.94 L2
CO $165.06 L2
CO $146.06 L2
CO $30.20 L2
CO $15.73 L2

The requested "CO" State code records are returned but there is not
data
showing for any of the records contained in query
"uqryStatePayment-MERGE"
The records shown above are from the query "uqryStatePayment-MERGE
DTO
DST
TRAC' only. There should be records under "MgmtCo" named LA, CM, VE
etc.,
etc. My thanks for your assistance so far. Do you have any ideas?

:

SELECT [State Code], Total, [MgmtCo]
FROM [SELECT [State Code], Total, [MgmtCo]
FROM [uqryStatePayment-MERGE]
UNION ALL
SELECT [State Code], Total, [MgmtCo]
FROM [uqryStatePayment-MERGE DTO DST TRAC]
]. AS Query6
WHERE [State Code] = [What State?];



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Douglas,

Thank you. I want to be sure this works and my SQL skills are
limited.
The
subselect statement is exactly what I have needed for a long
time.
Here
is
the code I am using that now works:

SELECT [State Code], Total
FROM [SELECT [State Code], Total
FROM [uqryStatePayment-MERGE]
UNION ALL
SELECT [State Code], Total
FROM [uqryStatePayment-MERGE DTO DST TRAC]
]. AS Query6
WHERE [State Code] = [What State?];

Could you provide an example of what the code would read if I
wanted
to
add
the field [MgmtCo]? This field is included in both of the tables
referenced
above. I keep getting the following error message when I add the
field.

Invalid bracketing of name SELECT '[State Code'

I don't understand what I am doing wrong.

Thanks in advance.
:

Presumably you're doing something like

SELECT [State Code], Amount
FROM [Table A]
UNION ALL
SELECT [State Code], Amount
FROM [Table B]
WHERE [State Code] = [What State?]

In that SQL, the parameter only applies to the last subselect.

To have it apply to the the UNIONed query, you either need to
save
the
query, and do a query against the saved query with a parameter,
or
try
something like

SELECT [State Code], Amount
FROM
(
SELECT [State Code], Amount
FROM [Table A]
UNION ALL
SELECT [State Code], Amount
FROM [Table B]
) AS MainQuery
WHERE [State Code] = [What State?]

(BTW, make sure you have UNION ALL, rather than simply UNION. If
you
only
have UNION, then if you had rows with the same state code and
amount
in
the
two tables, you'd only get one of them, not both)


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


message
I have gained invaluable information using this forum for the
past
couple
of
years and I thank all who participate. I have been wrestling
with
this
problem for some time now and have finally decided the time
has
come
to
post
my question.

I have a good understanding of the different types of queries
(ie:
select,
append, union, make table etc). My database is using linked
tables
(
which
may be part of my problem - I am not sure). I am trying to
merge
two
Click to expand...
Click to expand...
Click to expand...
 
Back
Top