How to join alias with tables?

[Sharky II]

Hi there

I'm working on a dynamic search query. I have a 'Transaction' table, and a TransactionItem table, and one Transaction may have more than one TransactionItem.

My search looks at 'TransactionQuery', which includes all fields of Transaction & TransactionItem and creates 'Dynamic_Query' which contains the results of the search.

I am creating an alias which is a 'sum' of the cost of the 'items' in each transaction. I'm creating an alias, but I'm not sure how to join the tables with the alias. I'm trying one alias at the moment, but will*need three in final query, like:

Sum(TransactionItem.ItemTotal) AS TotalTransactionCost

Currently this just displays the item totals, not the sum of all item totals given a particular ID (all items with TransactionID 7, or whatever)

Here is my current join line, which obviously joins my two tables:

FROM [Transaction] INNER JOIN TransactionItem ON Transaction.TransactionID=TransactionItem.Transact ionID
GROUP BY <all fields other than the aliases>

Could you suggest how I would modify my from/join lineup incorporate these aliases, so I can search the field in my dynamic search code?

Sorry if i haven't been clear enough!

Thanks!

 

[jdraw]

Please show the full sql.

 

[Sharky II]

Hi there,

Thanks for the reply. Here's the full SQL - as you can see i've added the three 'sum' aliases, but i haven't added the JOIN commands. Thanks again!

SELECT Transaction.TransactionID, Transaction.EntryDate, Transaction.TransactionType, Transaction.SupplierName, Transaction.TransactionDescription, Transaction.CourseNo, Transaction.BudgetCode, Transaction.CUOrderNo, Transaction.SupplierInvNo, Transaction.FinanaceReportDate, Transaction.OracleClearDate, TransactionItem.TransactionItemID, TransactionItem.ItemSubCode, TransactionItem.ItemDescription, TransactionItem.ItemAmountExcVAT, TransactionItem.ItemTotal, TransactionItem.ItemOracleClearDate, TransactionItem.VATAmount, Sum(TransactionItem.ItemTotal) AS TotalTransactionCost, Sum(TransactionItem.VATAmount) AS TotalVATAmount, Sum(TransactionItem.TotalAmountExcVAT) AS TotalExcVATAmount
FROM [Transaction] INNER JOIN TransactionItem ON Transaction.TransactionID=TransactionItem.TransactionID
GROUP BY Transaction.TransactionID, Transaction.EntryDate, Transaction.TransactionType, Transaction.SupplierName, Transaction.TransactionDescription, Transaction.CourseNo, Transaction.BudgetCode, Transaction.CUOrderNo, Transaction.SupplierInvNo, Transaction.FinanaceReportDate, Transaction.OracleClearDate, TransactionItem.TransactionItemID, TransactionItem.ItemSubCode, TransactionItem.ItemDescription, TransactionItem.ItemAmountExcVAT, TransactionItem.ItemTotal, TransactionItem.ItemOracleClearDate, TransactionItem.VATAmount;

 

[pbaldy]

As noted on your other thread, a sample db that demonstrates how you're trying to do this would help figure out the problem.

 

[Sharky II]

Hey man, sure thing. I've attached the real thing - there isn't much data in there, i've just stripped the logos out (which seems to have broken a couple of things, but nothing important). Please press shift on startup.

You can see my 'CompleteSearchForm', this searches 'TransactionQuery' and generates 'Dynamic_Query'.

The 3 'total amounts' for all the items for each transaction doesn't exist, so i've created aliases in 'TransactionQuery', but i haven't added the joins so that they're searchable (as per your advice in the other thread), as i'm not sure how to do that.

Hope this clarifies things a little better!? I just want to be able to search the 'total amount's' (including VAT, excluding VAT, and VAT amount). You can see that in my search form, the fields that are supposed to search these aliases aren't 'hooked up' to any code yet.

Sorry if this isn't what you meant by a sample database!

Thank you

Edit: should be attached now - thanks!

Eddie

 

[pbaldy]

I've attached the real thing

No, you haven't.

Make sure you've done all this:

http://www.access-programmers.co.uk/forums/showthread.php?t=140587

 

[Sharky II]

Ah - so sorry - will edit the post above when i've sorted it, any second now. My zip file is over 2mb so... brb! I just needed to compact and repair...

 

[pbaldy]

The total excluding VAT amount calculation is trying to sum a field that doesn't exist. Should it be summing ItemAmountExcVAT instead of TotalAmountExcVAT?

 

[Sharky II]

Hi - that's correct, i thought i fixed that before i uploaded. Sorry about that.

 

[pbaldy]

What problem are you having? I tweaked one of the code blocks to search one of those fields and it appeared to work correctly. You don't have to join on those fields; the join on transaction ID is appropriate here.

 

[Sharky II]

Sorry for wasting your time guys. You're right, after i corrected the error i could simply reference/access the aliases. I misinterpreted something you said in your post on the othe thread, sorry about that and thanks for taking the time to check things out!

 

[pbaldy]

Glad you got it sorted out.