How to join alias with tables?

S

Sharky II

Registered User.
Local time
Today, 08:04
Joined
Aug 21, 2003
Messages
354
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!
 
Please show the full sql.
 
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!

Code: 
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;
 
As noted on your other thread, a sample db that demonstrates how you're trying to do this would help figure out the problem.
 
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
 

Attachments

Last edited:
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...
 

Attachments

The total excluding VAT amount calculation is trying to sum a field that doesn't exist. Should it be summing ItemAmountExcVAT instead of TotalAmountExcVAT?
 
Hi - that's correct, i thought i fixed that before i uploaded. Sorry about that.
 
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.
 
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!
 
Glad you got it sorted out.
 
You must log in or register to reply here.

Similar threads

S
Problem with sql syntax with JOIN and WHERE in vba
Replies
8
Views
986
Ken Sheridan
Ken Sheridan
S
Need to create an alias for the totals of a field in query
Replies
3
Views
797
sheckay
S
O
Query optimization: Why does Access use temporary index instead of index or rushmore for a JOIN query with SUM?
Replies
13
Views
849
Josef P.
J
J
Solved Pulling data from three tables into multiple queries.
Replies
3
Views
642
Jomat
J
N
Query to show records EXCEPT those with matching fields
Replies
8
Views
501
nortonm
N

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom