Problems with sum function in query when adding extra fields

[ch4meleon]

Ok, I have a table which has a field populated from a lookup in order to limit to list so only staff on the list can be added in.

Im trying to create a simple query which totals up sales from each member of staff by region, and adds in their id code which is not in the original table. Every time i add the id in, my sum field in my query shows odd results. Ive tried changing the relationships but that doesnt seem to work.

The look up i think stores the id as well as the name (?) is there any way for me to get at both the bits of info in seperate fields on my query ? Or any other suggestion how to fix ? Im sure this is something simple but cannot get my head round it this morning

thanks in hope

 

[PHV]

What is your actual SQL code ?
How are the tables related ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ch4meleon]

when i created the table I chose a look up field type and choose the salesname field from sales through the wizard. so the SQL for the row source is - SELECT [sales].[salesid], [sales].[salesName] FROM sales;

Im wondering if it would be easier to make a new table and start again but i dont know how to make it automatically match up the sales id with the sales name if i just type the sales name in (and i had hoped to just be able to do that via a query)

thanks for any help / suggestions

 

[PHV]

I don't see any sum function in your query ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ch4meleon]

Sorry, that is the info from the original table (sales), I am then adding that table, and another table (RepInfo) into a query and trying to add the salesid from that 2nd table (RepInfo)onto my query.

So from sales i have - Salesname, sales region, ammount and from repinfo I have salesid, area manager. the two are linked in the query through the salesname field which is common to both (and which is the look up on the sales table). They are all set to group by on the totals row except for ammount which is set to sum, if i run the query without the additional data from repinfo then I get correct totals but as soon as I add in the second table my ammount sum goes a little mad so i thought I must have set the join properties wrong when i joined them but it doesnt make any difference sum ammount is wrong on all the join options.

does that make sense ?

 

[SkipVought]

Where is your query that contains the SUM function? Not the DESCRIPTION regarding what you did -- rather WHAT YOU DID.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[PHV]

Seems like RepInfo may have several rows for a given salesman.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ch4meleon]

PHV - Repinfo has one line per salesperson, sales has many lines. repinfo is basically just name, id, manager, contact details where sales is all the sales records.

Skip, not sure how to post a query in here other than to describe what it looks like, (eg two tables in top of screen, criteria, totals box etc in bottom) Im working on the more recent version of access with the ribbon, and whilst I have a hazy memory that you can turn a basic query into SQL language for the life of me i dont know how now.

 

[PHV]

Choose View SQL on the left of the ribbon.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ch4meleon]

thank you !!

SELECT calcStudentRecahrges1.[Student Name], calcStudentRecahrges1.[charge type], Sum(calcStudentRecahrges1.Expr1) AS SumOfExpr1, Students.[Pupil Code]
FROM Students RIGHT JOIN calcStudentRecahrges1 ON Students.[Pupil Name] = calcStudentRecahrges1.[Student Name]
GROUP BY calcStudentRecahrges1.[Student Name], calcStudentRecahrges1.[charge type], Students.[Pupil Code];

 

[PHV]

What is the SQL code of calcStudentRecahrges1 ?
Anyway, seems like a totally different query than exposed in your first post ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ch4meleon]

aargh, you are quite right, its the wrong one. not my morning. I will dig out the other one and apologies for being a complete plank..