Joining Tables, Group By Name, Select On...

[joesmithf1]

Hi I have 2 tables(tblA and tblB) and I want a query result(tblC) where ‘group by’ only one record per individual, with the total added together and the type starts with ‘01’. Below(tblA and tblB) are examples of the two table. TblC is the query result that I want.

tblA
Name---Type---Amount---Status
Joe---01FAE---$10---New
Joe---01FBE---$10---New
Lee---01FBE---$10---Old
Bob---30FBE---$30---New

tblB
Name---Type----Amount---Status
Joe----01FBE---$5------New
Lee----01NAE---$20-------Old
Lee----30FBE--$30------New

tblC
Name---Type---Amount
Joe---01FAE---$25---New
Lee---01FBE--$30---Old

I have tried linking/joining, adding SUM function, First, Group By, etc. but all the results came out to be all different. Also, am I support to add a ‘join’ of tblA to tblB on BOTH the NAME and TYPE? I am so confused! Please help.

Thank you very much.

Joe

 

[neileg]

You need a union query to join table A and table B 'vertically' not a select query which joins them 'horizontally'. Then add a criteria to return the '01' records and total this.