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-

Again, you are having problems because of poor table design. You have payments and charges scattered all over the place, so it's difficult to get a single picture of the amounts owed or paid by one client. To try to fix it, you'll have to fill in missing "0" values. For example, because FreightCharges has only an amount owed, your query should look like:

SELECT TransactionID,TransactionDate, ClientCIN, ClientName,"" AS Details, ConsignmentNo, Cartons, Weight, Expenses, 0 AS AmountReceived, 0 AS TotCash, DSum("Expenses","FreightCharges","TransactionID <=" & [TransactionID]) AS TotExpense, -DSum("Expenses","FreightCharges","TransactionID <=" & [TransactionID]) AS Balance
FROM FreightCharges

WHERE (((FreightCharges.ClientCIN) Like [Enter Client ID]));

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 10, 2014, at 6:49 AM, Khalid Tanweer <khalidtanweerburrah@yahoo.com> wrote:

John,

I am checking these 3 queries for ClientCIN 51

Table FreightCharges has 4 records, having TransactionID 1,2,3,4, query datasheet view shows:

Expenses AmountReceived TotCash TotExpense Balance
4139   Null Null   4139 Null
2125 Null Null   6264 Null
2152 Null Null   8416 Null
2575 Null Null   10991 Null

This table does not have AmountReceived field (it has only Expenses) therefore expression TotCash is showing null for each record and also
Balance is showing null for each record.

I am thinking for something, which i may be wrong, should we not remove AmountReceived, TotCash and Balance
from this query here, and at some later stage after running these 3 queries we try to use them?
-------------------------
Now i show you the Datasheet view of 2nd query:

Table MiscAccount has 10 records, TransactionID 1 - 10, (TransactionID 1-7 & 10,11,12) are other ClientCIN than ClientCIN 51

TransactionID Expenses AmountReceived TotCash TotExpense Balance
8   4607 Null   #Error #Error #Error
9   Null 4300   #Error #Error #Error
-------------------------
For 3rd query:

Table PaymentReceived does not have Expenses field, here only comes AmountReceived
It has 10 records, TransactionID 1 - 8 for other ClientCIN and TransactionID 9,10 for ClientCIN 51

Datasheet view of this query is:

TransactionID Expenses AmountReceived TotCash TotExpense Balance
9   Null   3000 10973   #Error #Error
10   Null   2000 12973   #Error #Error
-------------------------
TotCash 10973 is coming because in table TransactionID 1-8, AmountReceived is
18+2000+456+445+2000+729+325+2000=7973 adding TransactionID 9 figure of AmountReceived 3000 becomes 10973 and then adding TransactionID 10 figure of AmountReceived 2000 becomes 12973

This is the story up to now.
What do you say John.

Khalid

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

Khalid-

Yes, try each part of the query as a separate query - exactly as coded now. For example, first run:

SELECT TransactionID,TransactionDate, ClientCIN, ClientName,"" AS Details, ConsignmentNo, Cartons, Weight, Expenses,"" AS AmountReceived, DSum("AmountReceived","FreightCharges","TransactionID <=" & [TransactionID]) AS TotCash, DSum("Expenses","FreightCharges","TransactionID <=" & [TransactionID]) AS TotExpense, DSum("AmountReceived","FreightCharges","TransactionID <=" & [TransactionID])-DSum("Expenses","FreightCharges","TransactionID <=" & [TransactionID]) AS Balance
FROM FreightCharges
WHERE (((FreightCharges.ClientCIN) Like [Enter Client ID]));

Then run the second one:

SELECT TransactionID,TransactionDate, ClientCIN, ClientName, Details,"","","", Expenses, AmountReceived,  DSum("AmountReceived"," MiscAccount","TransactionID <=" & [TransactionID]) AS TotCash, DSum("Expenses"," MiscAccount","TransactionID <=" & [TransactionID]) AS TotExpense, DSum("AmountReceived"," MiscAccount","TransactionID <=" & [TransactionID])-DSum("Expenses"," MiscAccount","TransactionID <=" & [TransactionID]) AS Balance
FROM MiscAccount
WHERE (((MiscAccount.ClientCIN) Like [Enter Client ID]));

And finally the third one:

SELECT TransactionID, TransactionDate, ClientCIN, ClientName, Details,"" ,"" , "" , "" , AmountReceived,  DSum("AmountReceived","PaymentReceived","TransactionID <=" & [TransactionID]) AS TotCash, DSum("Expenses"," PaymentReceived","TransactionID <=" & [TransactionID]) AS TotExpense, DSum("AmountReceived"," PaymentReceived","TransactionID <=" & [TransactionID])-DSum("Expenses","PaymentReceived","TransactionID <=" & [TransactionID]) AS Balance
FROM PaymentReceived
WHERE (((PaymentReceived.ClientCIN) Like [Enter Client ID]));

If each runs fine individually, then they should run fine together in a UNION. Also be sure to explicitly declare the Parameter [Enter Client ID].

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 1:21 PM, Khalid Tanweer <khalidtanweerburrah@yahoo.com> wrote:

John,

Yes i first run these three queries before, but without those null values and they worked smoothly.

OK i will now try these separate with putting null values like for Details "" AS Details and should i try that Dsum function for balance in each query first?

OK let me do some experiments and then i will let you know the results.

Thanks John again.

Khalid

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

Khalid-

No, "as" can be upper, lower, or mixed case.

Can you run each of the three queries separately? Always test any query SQL you plan to put in a UNION before you create the final query.

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 12:35 PM, Khalid Tanweer <khalidtanweerburrah@yahoo.com> wrote:

John,
Thanks again John & sorry that i did not mentioned it in my reply, i did tried it before but was getting error.

Is the "AS" must be written in upper case, i think i was trying it with "As". Its OK now i am getting field name as you suggested.

Now i am coming to another part of this query which i did not mentioned before but it is required.
As in my other queries we have taken "Balance".

Now i am trying with this sql:

SELECT TransactionID,TransactionDate, ClientCIN, ClientName,"" AS Details, ConsignmentNo, Cartons, Weight, Expenses,"" AS AmountReceived, DSum("AmountReceived","FreightCharges","TransactionID <=" & [TransactionID]) AS TotCash, DSum("Expenses","FreightCharges","TransactionID <=" & [TransactionID]) AS TotExpense, DSum("AmountReceived","FreightCharges","TransactionID <=" & [TransactionID])-DSum("Expenses","FreightCharges","TransactionID <=" & [TransactionID]) AS Balance
FROM FreightCharges
WHERE (((FreightCharges.ClientCIN) Like [Enter Client ID]))
UNION ALL
SELECT TransactionID,TransactionDate, ClientCIN, ClientName, Details,"","","", Expenses, AmountReceived, DSum("AmountReceived"," MiscAccount","TransactionID <=" & [TransactionID]) AS TotCash, DSum("Expenses"," MiscAccount","TransactionID <=" & [TransactionID]) AS TotExpense, DSum("AmountReceived"," MiscAccount","TransactionID <=" & [TransactionID])-DSum("Expenses"," MiscAccount","TransactionID <=" & [TransactionID]) AS Balance
FROM MiscAccount
WHERE (((MiscAccount.ClientCIN) Like [Enter Client ID]))
UNION ALL SELECT TransactionID, TransactionDate, ClientCIN, ClientName, Details,"" ,"" , "" , "" , AmountReceived, DSum("AmountReceived","PaymentReceived","TransactionID <=" & [TransactionID]) AS TotCash, DSum("Expenses"," PaymentReceived","TransactionID <=" & [TransactionID]) AS TotExpense, DSum("AmountReceived"," PaymentReceived","TransactionID <=" & [TransactionID])-DSum("Expenses","PaymentReceived","TransactionID <=" & [TransactionID]) AS Balance
FROM PaymentReceived
WHERE (((PaymentReceived.ClientCIN) Like [Enter Client ID]));

But i am getting twice error message:
'The expression you entered as a query parameter produced this error: 'PCTL Accounts can't find the name 'AmountReceived' you entered in the expression'

Then 8 times following error message:

The Microsoft Jet database engine cannot find the input table or query 'MiscAccount'. Make sure it exists and that its name is spelled correctly

Then four times following error message:

The Microsoft Jet database engine cannot find the input table or query 'AmountReceived'. Make sure it exists and that its name is spelled correctly

Finally it shows in database window only first row of "FreightCharges" table with "TotExpense" without any Balance

Khalid

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

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