ACCESS FEED: Re: [MS_AccessPros] Help in Union query for three tables

Kamis, 09 Januari 2014

Re: [MS_AccessPros] Help in Union query for three tables

Khalid-

Give the field a name:

SELECT TransactionDate, ClientCIN, ClientName, "" AS Details, ConsignmentNo, Cartons, Weight, Expenses,"" AS AmountReceived

FROM FreightCharges

UNION ALL

…

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

On Jan 9, 2014, at 10:12 AM, Khalid Tanweer <khalidtanweerburrah@yahoo.com> wrote:

John,
Putting PARAMETERS [Enter Client ID] Double; on the first line and replacing NULL with "" worked and did not produced any error message, but it was giving me all records from three tables.

I tried with the following Sql and that is OK:
The sql is now:

SELECT TransactionDate, ClientCIN, ClientName,"", ConsignmentNo, Cartons, Weight, Expenses,""
FROM FreightCharges
WHERE (((FreightCharges.ClientCIN) Like [Enter Client ID]))
UNION ALL
SELECT TransactionDate, ClientCIN, ClientName, Details,"","","", Expenses, AmountReceived
FROM MiscAccount
WHERE (((MiscAccount.ClientCIN) Like [Enter Client ID]))
UNION ALL SELECT TransactionDate, ClientCIN, ClientName, Details,"" ,"" , "" , "" , AmountReceived
FROM PaymentReceived
WHERE (((PaymentReceived.ClientCIN) Like [Enter Client ID]));

The only thing is that for "Details" i am getting column heading "Expr1003" and for "AmountReceived" Expr1008

It is not such a big problem as on the report we can modify the Label for them, but is there any other solution for this?

Thank you very much up to here because of your hints and clues i did managed the problem.

Khalid

On Thursday, January 9, 2014 12:49 PM, John Viescas <JohnV@msn.com> wrote:

Khalid-

You can normally get away with NOT explicitly declaring parameters in most queries, but a UNION query is an exception. Try adding

PARAMETERS [Enter Client ID] Double;

.. as the first line in your query.

It might also not like trying to UNION a Null value with something else. Try using an empty string - "" - instead of NULL.

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

On Jan 9, 2014, at 8:39 AM, Khalid Tanweer <khalidtanweerburrah@yahoo.com> wrote:

Hi All,
I am trying to make a union query from three tables.
My query is working for first two tables, the sql upto here is:

SELECT TransactionDate, ClientCIN, ClientName, NULL As Details, ConsignmentNo, Cartons, Weight, Expenses, NULL As AmountReceived
FROM FreightCharges
WHERE (((FreightCharges.ClientCIN) Like [Enter Client ID]))
UNION ALL
SELECT TransactionDate, ClientCIN, ClientName, Details, NULL As ConsignmentNo, NULL As Cartons, NULL As Weight, Expenses, AmountReceived
FROM MiscAccount
WHERE (((MiscAccount.ClientCIN) Like [Enter Client ID]));
----------- OK UPTO HERE ABOVE--------------------

But when i add sql for third tableand sql becomes as follows:

SELECT TransactionDate, ClientCIN, ClientName, NULL As Details, ConsignmentNo, Cartons, Weight, Expenses, NULL As AmountReceived
FROM FreightCharges
WHERE (((FreightCharges.ClientCIN) Like [Enter Client ID]))
UNION ALL
SELECT TransactionDate, ClientCIN, ClientName, Details, NULL As ConsignmentNo, NULL As Cartons, NULL As Weight, Expenses, AmountReceived
FROM MiscAccount
WHERE (((MiscAccount.ClientCIN) Like [Enter Client ID]))
UNION ALL
SELECT TransactionDate, ClientCIN, ClientName, Details, NULL , NULL , NULL , NULL , AmountReceived
FROM PaymentReceived
WHERE (((PaymentReceived.ClientCIN) Like [Enter Client ID]));

Here after entering Client ID, i get error message "Data Type mismatch" I have checked many times Data Type for third table and it is same as first two tables.

Second thing which i tried is for third table i removed "ALL" from the "UNION ALL" then it gives an error message:
"This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try
simplifying the expression by assigning parts of expression to variables."

Structure of Fields is:
TransactionDate = Date/Time, Short Date
ClientCIN = Number, Double
ClientName = Text, 50
ConsignmentNo = Text, 9, Input mask: 9999\-A\-99;0;-
Cartons = Number, Double, Standard, 0
Weight = Number, Double, Standard, 2
Expenses = Number, Double, Standard, 0
Details = Text, 100
AmoundReceived = Number, Double, Standard, 0

Need help where i am wrong or what to do.

Khalid