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

 

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

__._,_.___

Reply via web post

Reply to sender

Reply to group

Start a New Topic

Messages in this topic (7)

Recent Activity:

• New Members 2

Visit Your Group

Switch to: Text-Only, Daily Digest • Unsubscribe • Terms of Use • Send us Feedback

.

__,_._,___