Trying to reduce the number of queries by using expressions

[Mattbro]

Hello. I am trying to reduce the number of queries in a database.It is based on a table (Sheet1) which has 4 fields;


1) Received Date,
2) Serial Number-primary key- (3 types of item by serial no, prefixed by 'H', 'N' and 'G' (e.g. H123/12, N345/12 and G123/12).
3) Checkers (A,B,C,and D)
4) Date Checked
By using a query with the following SQL;
'G'Query;

SELECT Count(Sheet1.[Serial No]) AS [CountOfSerial No], Sheet1.[Date Checked], Count(IIf([Serial No] Like "G*" And [Checker]="A",[Serial No])) AS [A G Total], Count(IIf([Serial No] Like "G*" And [Checker]="B",[Serial No])) AS [B G Total], Count(IIf([Serial No] Like "G*" And [Checker]="C",[Serial No])) AS [C G Total], Count(IIf([Serial No] Like "G*" And [Checker]="D",[Serial No])) AS [D G Total]
FROM Sheet1
WHERE (((Sheet1.[Serial No]) Like "G*"))
GROUP BY Sheet1.[Date Checked];

I can populate textboxes on a form to show how much 'G' each checker did on a given day. Two Similar Queries give me the amount of 'H' and the amount of 'N'.

What I am now trying to formulate is a cumulative query that will tell me the amount they have done from a given date, till today. The date is entered on another form. I can do it using an individual query for each checker and each serial type,

SELECT Count(Sheet1.[Serial No]) AS [CountOfSerial No]
FROM Sheet1
WHERE (((Sheet1.[Date Checked]) Between [Forms]![CumulatorForm]![Text17] And [Forms]![CumulatorForm]![Text19]) AND ((Sheet1.[Serial]) Like "G*") AND ((Sheet1.[Checker])=”A”;

If I can wrap it into just three queries as above, so much the better Does anyone have any ideas? I have been trying to adapt the G Query for days now, but to no avail. Many thanks for looking, Mattbro

[June7]

Have you considered using a report to perform the grouping by serial number and checker ID and calculating summary totals in group and report footers?

Want to provide db for analysis? Follow instructions at bottom of my post.

[Mattbro]

Hullo June7-this file is based on some SQL that you provided for me in an earlier forum! I have half managed to solve it (at 5AM this morning, using the following SQL

SELECT Count(Sheet1.[Serial No]) AS [CountOfSerial No], Count(IIf([Serial No] Like "G*" And [Checker]="A",[Serial No])) AS [A G Total], Count(IIf([Serial No] Like "G*" And [Checker]="R",[Serial No])) AS [R G Total], Count(IIf([Serial No] Like "G*" And [Checker]="G",[Serial No])) AS [G G Total], Count(IIf([Serial No] Like "G*" And [Checker]="M",[Serial No])) AS [M G Total]FROM Sheet1
WHERE (((Sheet1.[Date Report Updated]) Between [Forms]![Cumulator]![Text0] And Now()) AND ((Sheet1.[Serial No]) Like "G*"));

However I am very interested in your idea, so have zipped the file for you to have a look at. (Admittedly, I am getting a 'run-time error '91'' on the cumulator page.....not sure why, but the queries themselves work nicely-if you have any ideas on the run time error, I would be very glad to here them). Anyhoo, if I can populate the forms and use that data to feed to a report, then that might make things better still.NewOutput.zip
Many thanks,
Mattbro

[June7]

This GROUP BY aggregate query produces same data as the 3 cumulative queries:
SELECT Left([Serial No],2) AS SerNumGroup, Sheet1.Checker, Count(Sheet1.[Serial No]) AS [CountOfSerial No]
FROM Sheet1
WHERE (((Sheet1.[Date Report Updated]) Between [Forms]![Cumulator]![Text0] And Now()))
GROUP BY Left([Serial No],2), Sheet1.Checker;

The code you have in the date textbox AfterUpdate will not work because these are aggregate queries and the date field is not available to search in. What you need to do is requery the form(s). For the same reason, setting the OrderBy property of each form to the date field will not work. There is no date value to order by and is triggering an input parameter prompt. Remove it.

Why are you outputting to a form? None of these queries are editable. You could build a report using Grouping & Sorting with aggregate calculations in group and report footers and none of these queries would be needed. This will allow display of individual record details as well as summary totals.

The CUM HG form is missing textbox for FIRJ HG Total.

[Mattbro]

Hello June7,
Your aggregate query produces the same dataas the 3 cumulative queries, so I am going from my original 15 queries down to 1 which is excellent-thanks very much!
I am outputting to a form because the date textbox on both the forms provides a start date on both the cumulative and the 'work by date' queries. I guess I am doing it because it works for me, and as a novice, when I find a method that works, I don't look too hard at alternatives-hence the numerous queries and spaghetti code!! It's an issue that I am coming to terms with! I am not too au fait with reports....I had better have a look at them.

I have removed the 'OrderBy' property of each form to the date field, and it has not affected the running, so you are certainly right to suggest its removal.

I still can't see why the code on the date box works on the master form but not on the cumulator. The queries on the master form are also aggregate, but it has never generated the runtime error......can I negate this by writing to reports? If so, any pointers as to how?

Incidentally, the CUM HG form is missing a textbox for FIRJ because FIRJ doesn't check HG.
Many thanks,
Mattbro

This GROUP BY aggregate query produces same data as the 3 cumulative queries:
SELECT Left([Serial No],2) AS SerNumGroup, Sheet1.Checker, Count(Sheet1.[Serial No]) AS [CountOfSerial No]
FROM Sheet1
WHERE (((Sheet1.[Date Report Updated]) Between [Forms]![Cumulator]![Text0] And Now()))
GROUP BY Left([Serial No],2), Sheet1.Checker;

The code you have in the date textbox AfterUpdate will not work because these are aggregate queries and the date field is not available to search in. What you need to do is requery the form(s). For the same reason, setting the OrderBy property of each form to the date field will not work. There is no date value to order by and is triggering an input parameter prompt. Remove it.

Why are you outputting to a form? None of these queries are editable. You could build a report using Grouping & Sorting with aggregate calculations in group and report footers and none of these queries would be needed. This will allow display of individual record details as well as summary totals.

The CUM HG form is missing textbox for FIRJ HG Total.

[June7]

I was working with the Cumulative form because this is the one opened by the Switchboard. Cumulative does not have a RecordSource. This is why the behavior is different from Master form. However, I am surprised that the master/child link properties of the subforms will synchronize with Master form. I've never done form based on aggregate query. Forms are intended as data entry objects and aggregate query is not editable. Convention is to use report to output aggregate data. However, if you want a quick on-screen look at aggregate data, a form can accommodate.

[Mattbro]

I see what you mean, June7. By feeding to a report rather than a form, I can complete the task, automatically add the averages (my next task) and provide the user with a printable/sendable sheet. Job done. Thanks for your help.
Mattbro