Using the Total line in a query

[Glohamar]

I am not sure where to ask this question, so I am going to start here. I have Access 2000 and I am building a query to give me totals by section of airplane. Example

Wing
Detail Assembly Installations
2 3 5

Tail
Detail Assembly Installation
3 5 6

I have set up the query total line to group by section and do sum on the detail, assembly, and installations. Also, I am trying to filter by a date field by Is Not Null(just needs to be filled in), and has Group By in the Totals line. Why is it that the query will show multiple lines for a section? It shows up on my report the same way. I really did not want to create a new query to get my totals.

What am I doing wrong?

Thanks for any help.

Dave

 

[hneal98]

First, it would help if you could convert your query to SQL and then post it here. It sounds like you have something in the table or datasource that your query is based on that shows multiple times, even though you may only be looking at a few fields, this other field is causing that record to show multiple times.

Not sure if that makes sense to you. Let me know if it does not.

 

[lespaul]

What does your SQL look like? What does the raw data start out like and what do you want your final results to look like?

 

[PaulBricker]

Probably because you have multiple Dates foor each Section. To do what you want, you might find just doing it all in a query will give you want you want. Just filter the Null dates out of the query and then add a GroupHeader for Section and then a GroupHeader for Dates and then the summations can be done in the Section Group Footer or the Date Group Footer.

Paul

 

[Glohamar]

Thanks everyone for responding.

I think it is because of the date field. It wants to group everything by the date also. I am not sure what you are saying Paul. I am listing my SQL statment below.

Thanks for the help.

Dave


SELECT PAMMain.ACSect, Sum(PAMInfo.EstPlngHrs) AS SumOfEstPlngHrs, Sum(PAMDetail.DDetail) AS SumOfDDetail, Sum(PAMDetail.DAssy) AS SumOfDAssy, Sum(PAMDetail.DInstl) AS SumOfDInstl, Sum(PAMDetail.DComp) AS SumOfDComp, Sum(PAMDetail.DMach) AS SumOfDMach, Sum(PAMDetail.DObsolete) AS SumOfDObsolete
FROM PAMInfo INNER JOIN (PAMDetail INNER JOIN PAMMain ON PAMDetail.DwgNum = PAMMain.DwgNum) ON (PAMMain.DwgNum = PAMInfo.DwgNum) AND (PAMInfo.DRev = PAMDetail.DRev) AND (PAMInfo.DwgNum = PAMDetail.DwgNum)
GROUP BY PAMMain.ACSect, PAMInfo.DateCheck
HAVING (((PAMInfo.DateCheck) Is Not Null));

 

[PaulBricker]

Reports are set up to handle different GroupBy situations like you are describing. The outer Group would be Section then the inner Group would be Dates. In the query you get Section repeated for each Date because you have GroupBy both. In the Report, you GroupBy the Section and then GroupBy the Date inside each Section so you only see the Section once no matter how many corresponding Dates you have.

Paul

 

[Glohamar]

I appreciate your help Paul. I am not understanding how to GroupBy Section and then GroupBy Date inside each section.
I have the GroupBy Section ok, just not sure how to group inside of a group.

Dave

 

[PaulBricker]

If you have the query basically returning everything you need then try this.
Start the Report Wizard. Select your query for the Record Source. When the Wizard asks you if you want to group by any values, First select Section and then Select Date. This will put the Section first and then each Date inside it so the info looks like this.

SectionA
              Date
              Date
              Date
SectionB
        Date
        Date
SectionC
        Date

Then you will probably need to tweak it a little so when you open the report in Design View, on the Menu Bar go to View....Sorting and Grouping. You will see the Fields/Expressions box at the top and you should see the Section field and the Date field. In the Group Properties box down lower, you will see properties like
Group Header Yes/No
GroupFooter Yes/No
Group On which if it's a date field allows you to group on Each Value or day, week, month, year, quarter etc.
Group Interval and Keep Together. These are fairly straight forward. Try the wizard and post back with specific problems.

Paul