Count to 8 and start over

  • Thread starter Thread starter NeoFax
  • Start date Start date
N

NeoFax

I would like to create a query that has a running total to 8 items and
places this total in a field. Then use this as the basis of a report
to show the first 4 items on the left hand side and the next 4 items
on the right hand side. (Possibly via two subreports). How would I go
about counting 8 records and then start over? Also, how would I add
extra blank records for items that do not make the full 8?
 
I hope your tables are normalized.
If so, it sounds like your report needs to have a sub-report to print those
items.
What will you do if there are more than 8 items?
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".
 
For a report? have a running sum counter (+1), and test its value. If

(running MOD 8) <= 4

then make visible the control to the left, else, make visible the control to
the right. Hide the other control. Sure, you have two controls, one at the
left, one at the right, both 'bound' to the same value, and only one of them
visible at a time. Extra work, maybe, but the end result is obtained, and
playing with the visibility is not that expensive.


Vanderghast, Access MVP
 
This will probably work, but I need all 8 boxes of info to show
whether they have info in them or not. So, for the first box, it
would be (running MOD 8) = 1, then place info, else null?
 
I cannot seem to get this to work, as I do not have a running tally
yet. So, here is my SQL for my query, but the RunningTtl field does
not want to work:

SELECT tblDiscHeader.workorderNum, tblDiscHeader.workorderName,
tblDiscHeader.workorderMaterial, tblDiscHeader.workorderSerialNum,
tblDiscHeader.workorderComponent, tblProductLine.ProductName,
tblDiscrepancy.Discrepancy, tblDiscrepancy.dateDisc,
tblQualityInspectors.qualCNumber, tblQualityInspectors.qualFName,
tblQualityInspectors.qualLName, tblQualityInspectors.qualRole,
Count(tblDiscrepancy.[Discrepancy]) AS RunningTtl
FROM tblQualityInspectors INNER JOIN (tblProductLine INNER JOIN
(tblDiscHeader INNER JOIN tblDiscrepancy ON tblDiscHeader.ID =
tblDiscrepancy.workorderDisc) ON tblProductLine.ID =
tblDiscHeader.workorderProductline) ON tblQualityInspectors.ID =
tblDiscrepancy.qualDisc
WHERE (((tblDiscHeader.workorderNum)<=[workorderNum]))
GROUP BY tblDiscHeader.workorderNum, tblDiscHeader.workorderName,
tblDiscHeader.workorderMaterial, tblDiscHeader.workorderSerialNum,
tblDiscHeader.workorderComponent, tblProductLine.ProductName,
tblDiscrepancy.Discrepancy, tblDiscrepancy.dateDisc,
tblQualityInspectors.qualCNumber, tblQualityInspectors.qualFName,
tblQualityInspectors.qualLName, tblQualityInspectors.qualRole;

I am using count, but it should count 4 records and put 1, 2, 3, 4 but
does not. My assumption is that at the time the query is ran there is
only 1 record to count for the first line so it moves to the second
line and yet again only 1 line so the count is 1. I do not have any
fields that contain a number, so SUM probably will not work.
 
Use the report capability to produce a running sum, not the query, so the
query will be 'easier'.

In the report, appropriate section, add a textbox control, make its control
source = 1, and make its running sum property (have the property sheet
visible) to yes (by default, it is no).

Also sort (order by) with the report, not within the query, which can then
be without the COUNT and the GROUP BY (unless I missed another point).


Vanderghast, Access MVP




I cannot seem to get this to work, as I do not have a running tally
yet. So, here is my SQL for my query, but the RunningTtl field does
not want to work:

SELECT tblDiscHeader.workorderNum, tblDiscHeader.workorderName,
tblDiscHeader.workorderMaterial, tblDiscHeader.workorderSerialNum,
tblDiscHeader.workorderComponent, tblProductLine.ProductName,
tblDiscrepancy.Discrepancy, tblDiscrepancy.dateDisc,
tblQualityInspectors.qualCNumber, tblQualityInspectors.qualFName,
tblQualityInspectors.qualLName, tblQualityInspectors.qualRole,
Count(tblDiscrepancy.[Discrepancy]) AS RunningTtl
FROM tblQualityInspectors INNER JOIN (tblProductLine INNER JOIN
(tblDiscHeader INNER JOIN tblDiscrepancy ON tblDiscHeader.ID =
tblDiscrepancy.workorderDisc) ON tblProductLine.ID =
tblDiscHeader.workorderProductline) ON tblQualityInspectors.ID =
tblDiscrepancy.qualDisc
WHERE (((tblDiscHeader.workorderNum)<=[workorderNum]))
GROUP BY tblDiscHeader.workorderNum, tblDiscHeader.workorderName,
tblDiscHeader.workorderMaterial, tblDiscHeader.workorderSerialNum,
tblDiscHeader.workorderComponent, tblProductLine.ProductName,
tblDiscrepancy.Discrepancy, tblDiscrepancy.dateDisc,
tblQualityInspectors.qualCNumber, tblQualityInspectors.qualFName,
tblQualityInspectors.qualLName, tblQualityInspectors.qualRole;

I am using count, but it should count 4 records and put 1, 2, 3, 4 but
does not. My assumption is that at the time the query is ran there is
only 1 record to count for the first line so it moves to the second
line and yet again only 1 line so the count is 1. I do not have any
fields that contain a number, so SUM probably will not work.
 
Also, it would be <4, not <=4, since the possible modulo are 0, 1, 2, 3
and 4, 5, 6, 7 (not 1 to 8 , but 0 to 7). But since the running sum
starts at 1, not 0, you will have to use something like:

(Me.RunningSumControl.Value - 1 ) MOD 8 < 4

in the format event of the section, since it is there that you will
hide/unhide the 'movable' textbox (setting its visibility property).



Vanderghast, Access MVP


I cannot seem to get this to work, as I do not have a running tally
yet. So, here is my SQL for my query, but the RunningTtl field does
not want to work:

SELECT tblDiscHeader.workorderNum, tblDiscHeader.workorderName,
tblDiscHeader.workorderMaterial, tblDiscHeader.workorderSerialNum,
tblDiscHeader.workorderComponent, tblProductLine.ProductName,
tblDiscrepancy.Discrepancy, tblDiscrepancy.dateDisc,
tblQualityInspectors.qualCNumber, tblQualityInspectors.qualFName,
tblQualityInspectors.qualLName, tblQualityInspectors.qualRole,
Count(tblDiscrepancy.[Discrepancy]) AS RunningTtl
FROM tblQualityInspectors INNER JOIN (tblProductLine INNER JOIN
(tblDiscHeader INNER JOIN tblDiscrepancy ON tblDiscHeader.ID =
tblDiscrepancy.workorderDisc) ON tblProductLine.ID =
tblDiscHeader.workorderProductline) ON tblQualityInspectors.ID =
tblDiscrepancy.qualDisc
WHERE (((tblDiscHeader.workorderNum)<=[workorderNum]))
GROUP BY tblDiscHeader.workorderNum, tblDiscHeader.workorderName,
tblDiscHeader.workorderMaterial, tblDiscHeader.workorderSerialNum,
tblDiscHeader.workorderComponent, tblProductLine.ProductName,
tblDiscrepancy.Discrepancy, tblDiscrepancy.dateDisc,
tblQualityInspectors.qualCNumber, tblQualityInspectors.qualFName,
tblQualityInspectors.qualLName, tblQualityInspectors.qualRole;

I am using count, but it should count 4 records and put 1, 2, 3, 4 but
does not. My assumption is that at the time the query is ran there is
only 1 record to count for the first line so it moves to the second
line and yet again only 1 line so the count is 1. I do not have any
fields that contain a number, so SUM probably will not work.
 
This did not work either as all 8 blocks are on the report, so the
txtRunningTtl never changes from 1 unless I have more than 8 items.
Unless of course you meant to make the report detail blank and put the
txtRunningTtl in a subreport. How would I keep the group of
workorderNum's from bleeding onto one another? Does the grouping in
the main report force the subreport to group as well? How would I
place blank records to fill the 8 blocks?

Also, it would be <4, not <=4, since the possible modulo are 0, 1, 2,  3
and   4, 5, 6, 7    (not  1 to 8 , but 0 to 7). But since the running sum
starts at 1, not 0, you will have to use something like:

    (Me.RunningSumControl.Value - 1 )  MOD 8  <  4

in the format event of the section, since it is there that you will
hide/unhide the 'movable' textbox (setting its visibility property).

Vanderghast, Access MVP


I cannot seem to get this to work, as I do not have a running tally
yet.  So, here is my SQL for my query, but the RunningTtl field does
not want to work:

SELECT tblDiscHeader.workorderNum, tblDiscHeader.workorderName,
tblDiscHeader.workorderMaterial, tblDiscHeader.workorderSerialNum,
tblDiscHeader.workorderComponent, tblProductLine.ProductName,
tblDiscrepancy.Discrepancy, tblDiscrepancy.dateDisc,
tblQualityInspectors.qualCNumber, tblQualityInspectors.qualFName,
tblQualityInspectors.qualLName, tblQualityInspectors.qualRole,
Count(tblDiscrepancy.[Discrepancy]) AS RunningTtl
FROM tblQualityInspectors INNER JOIN (tblProductLine INNER JOIN
(tblDiscHeader INNER JOIN tblDiscrepancy ON tblDiscHeader.ID =
tblDiscrepancy.workorderDisc) ON tblProductLine.ID =
tblDiscHeader.workorderProductline) ON tblQualityInspectors.ID =
tblDiscrepancy.qualDisc
WHERE (((tblDiscHeader.workorderNum)<=[workorderNum]))
GROUP BY tblDiscHeader.workorderNum, tblDiscHeader.workorderName,
tblDiscHeader.workorderMaterial, tblDiscHeader.workorderSerialNum,
tblDiscHeader.workorderComponent, tblProductLine.ProductName,
tblDiscrepancy.Discrepancy, tblDiscrepancy.dateDisc,
tblQualityInspectors.qualCNumber, tblQualityInspectors.qualFName,
tblQualityInspectors.qualLName, tblQualityInspectors.qualRole;

I am using count, but it should count 4 records and put 1, 2, 3, 4 but
does not.  My assumption is that at the time the query is ran there is
only 1 record to count for the first line so it moves to the second
line and yet again only 1 line so the count is 1.  I do not have any
fields that contain a number, so SUM probably will not work.
This will probably work, but I need all 8 boxes of info to show
whether they have info in them or not. So, for the first box, it
would be (running MOD 8) = 1, then place info, else null?
Click to expand...
Click to expand...
 
I am lost with your design. When you say you have 8 items you mean 8 records
or 8 fields. If you have 8 records, then the running property should
increase one by one if the control is on the same detail level than EACH
record (ie, the detail section). If it is the case and if it still display
1, either you missed to set the running sum to yes, either you have an
additional group that makes each record in its own group, but in general,
running sum works quite well. Fix that first, since the whole logic I
proposed is based on that.

The sub reports should follow the relevant properties
(LinkMasterFields/LinkChildFields), or none if left blank. I don't see why
you would need sub-report, though. Try to keep things simple.


To print always 8 rows of details even if there are less in the given group,
the only solution that I see at the moment is quite complicated: it is to
use the feature that the report can run twice (as for the case of printing
the total number of pages). You have to keep the number of rows in the
details for each group at the first pass, then, at the second pass, to use
the report runtime variables MoveLayout, NextRecord, PrintSection ( = true,
true, false) to fill the missing number of details until their number reach
8. That is a complex process, and the best example that approximatively does
some thing similar is given at
http://www.mvps.org/access/reports/rpt0013.htm, but again, that example does
not exactly what I described, but rather use the two passes technique, the
example does not look for the number of records per group, but look, for
each group, the first and last page on which it appears. There will be
additional code to supply, in your case, for the second pass, in the detail
event. You may be better to use the report newsgroup, instead of this one
which is about query, though.



Vanderghast, Access MVP




This did not work either as all 8 blocks are on the report, so the
txtRunningTtl never changes from 1 unless I have more than 8 items.
Unless of course you meant to make the report detail blank and put the
txtRunningTtl in a subreport. How would I keep the group of
workorderNum's from bleeding onto one another? Does the grouping in
the main report force the subreport to group as well? How would I
place blank records to fill the 8 blocks?

Also, it would be <4, not <=4, since the possible modulo are 0, 1, 2, 3
and 4, 5, 6, 7 (not 1 to 8 , but 0 to 7). But since the running sum
starts at 1, not 0, you will have to use something like:

(Me.RunningSumControl.Value - 1 ) MOD 8 < 4

in the format event of the section, since it is there that you will
hide/unhide the 'movable' textbox (setting its visibility property).

Vanderghast, Access MVP


I cannot seem to get this to work, as I do not have a running tally
yet. So, here is my SQL for my query, but the RunningTtl field does
not want to work:

SELECT tblDiscHeader.workorderNum, tblDiscHeader.workorderName,
tblDiscHeader.workorderMaterial, tblDiscHeader.workorderSerialNum,
tblDiscHeader.workorderComponent, tblProductLine.ProductName,
tblDiscrepancy.Discrepancy, tblDiscrepancy.dateDisc,
tblQualityInspectors.qualCNumber, tblQualityInspectors.qualFName,
tblQualityInspectors.qualLName, tblQualityInspectors.qualRole,
Count(tblDiscrepancy.[Discrepancy]) AS RunningTtl
FROM tblQualityInspectors INNER JOIN (tblProductLine INNER JOIN
(tblDiscHeader INNER JOIN tblDiscrepancy ON tblDiscHeader.ID =
tblDiscrepancy.workorderDisc) ON tblProductLine.ID =
tblDiscHeader.workorderProductline) ON tblQualityInspectors.ID =
tblDiscrepancy.qualDisc
WHERE (((tblDiscHeader.workorderNum)<=[workorderNum]))
GROUP BY tblDiscHeader.workorderNum, tblDiscHeader.workorderName,
tblDiscHeader.workorderMaterial, tblDiscHeader.workorderSerialNum,
tblDiscHeader.workorderComponent, tblProductLine.ProductName,
tblDiscrepancy.Discrepancy, tblDiscrepancy.dateDisc,
tblQualityInspectors.qualCNumber, tblQualityInspectors.qualFName,
tblQualityInspectors.qualLName, tblQualityInspectors.qualRole;

I am using count, but it should count 4 records and put 1, 2, 3, 4 but
does not. My assumption is that at the time the query is ran there is
only 1 record to count for the first line so it moves to the second
line and yet again only 1 line so the count is 1. I do not have any
fields that contain a number, so SUM probably will not work.
This will probably work, but I need all 8 boxes of info to show
whether they have info in them or not. So, for the first box, it
would be (running MOD 8) = 1, then place info, else null?
Click to expand...
Click to expand...
 
OK, let me explain myself better and see if I understand. Here is
what my form looks like:

---------------------------------------------------------
| List of Discrepancies |
---------------------------------------------------------
|PL|P/N |S/N |W/O#|Work Order Name |
| | | | | |
---------------------------------------------------------
|#1|Discrepanc|Correction|QC|#5|Discrepanc|Correction|QC|
| | | | | | | | |
| | | | | | | | |
| |QC Name |TECH Name | | |QC Name |TECH Name | |
|#2|Discrepanc|Correction|QC|#6|Discrepanc|Correction|QC|
| | | | | | | | |
| | | | | | | | |
| |QC Name |TECH Name | | |QC Name |TECH Name | |
|#3|Discrepanc|Correction|QC|#7|Discrepanc|Correction|QC|
| | | | | | | | |
| | | | | | | | |
| |QC Name |TECH Name | | |QC Name |TECH Name | |
|#4|Discrepanc|Correction|QC|#8|Discrepanc|Correction|QC|
| | | | | | | | |
| | | | | | | | |
| |QC Name |TECH Name | | |QC Name |TECH Name | |
---------------------------------------------------------

I can get a runningTtl to show and it increments Over Group, which is
my assumption is what it should be as the group is W/O and the
increment is the multiple discrepancies. So, I am assuming I should
only draw the 20 some boxes to make block 1 and group them? Then in
the detail put the (Me.RunningSumControl.Value - 1 ) MOD 8 < 4 in
OnFormat. However, I do not want to hide/unhide anything as I always
want 8 to show whether they have information in them or not.
I am lost with your design. When you say you have 8 items you mean 8 records
or 8 fields. If you have 8 records, then the running property should
increase one by one if the control is on the same detail level than EACH
record (ie, the detail section). If it is the case and if it still display
1, either you missed to set the running sum to yes, either you have an
additional group that makes each record in its own group, but in general,
running sum works quite well. Fix that first, since the whole logic I
proposed is based on that.

The sub reports should follow the relevant properties
(LinkMasterFields/LinkChildFields), or none if left blank. I don't see why
you would need sub-report, though. Try to keep things simple.

To print always 8 rows of details even if there are less in the given group,
the only solution that I see at the moment is quite complicated: it is to
use the feature that the report can run twice (as for the case of printing
the total number of pages). You have to keep the number of rows in the
details for each group at the first pass, then, at the second pass, to use
the report runtime variables MoveLayout, NextRecord, PrintSection ( = true,
true, false) to fill the missing number of details until their number reach
8. That is a complex process, and the best example that approximatively does
some thing similar is given athttp://www.mvps.org/access/reports/rpt0013.htm, but again, that example does
not exactly what I described, but rather use the two passes technique, the
example does not look for the number of records per group, but look, for
each group, the first and last page on which it appears. There will be
additional code to supply, in your case, for the second pass, in the detail
event. You may be better to use the report newsgroup, instead of this one
which is about query, though.

Vanderghast, Access MVP


This did not work either as all 8 blocks are on the report, so the
txtRunningTtl never changes from 1 unless I have more than 8 items.
Unless of course you meant to make the report detail blank and put the
txtRunningTtl in a subreport.  How would I keep the group of
workorderNum's from bleeding onto one another?  Does the grouping in
the main report force the subreport to group as well?  How would I
place blank records to fill the 8 blocks?

Also, it would be <4, not <=4, since the possible modulo are 0, 1, 2,3
and 4, 5, 6, 7 (not 1 to 8 , but 0 to 7). But since the running sum
starts at 1, not 0, you will have to use something like:
Click to expand...
(Me.RunningSumControl.Value - 1 ) MOD 8 < 4
Click to expand...
in the format event of the section, since it is there that you will
hide/unhide the 'movable' textbox (setting its visibility property).
Click to expand...
Vanderghast, Access MVP
Click to expand...
"NeoFax" <[email protected]> wrote in message
Click to expand...
I cannot seem to get this to work, as I do not have a running tally
yet. So, here is my SQL for my query, but the RunningTtl field does
not want to work:
Click to expand...
SELECT tblDiscHeader.workorderNum, tblDiscHeader.workorderName,
tblDiscHeader.workorderMaterial, tblDiscHeader.workorderSerialNum,
tblDiscHeader.workorderComponent, tblProductLine.ProductName,
tblDiscrepancy.Discrepancy, tblDiscrepancy.dateDisc,
tblQualityInspectors.qualCNumber, tblQualityInspectors.qualFName,
tblQualityInspectors.qualLName, tblQualityInspectors.qualRole,
Count(tblDiscrepancy.[Discrepancy]) AS RunningTtl
FROM tblQualityInspectors INNER JOIN (tblProductLine INNER JOIN
(tblDiscHeader INNER JOIN tblDiscrepancy ON tblDiscHeader.ID =
tblDiscrepancy.workorderDisc) ON tblProductLine.ID =
tblDiscHeader.workorderProductline) ON tblQualityInspectors.ID =
tblDiscrepancy.qualDisc
WHERE (((tblDiscHeader.workorderNum)<=[workorderNum]))
GROUP BY tblDiscHeader.workorderNum, tblDiscHeader.workorderName,
tblDiscHeader.workorderMaterial, tblDiscHeader.workorderSerialNum,
tblDiscHeader.workorderComponent, tblProductLine.ProductName,
tblDiscrepancy.Discrepancy, tblDiscrepancy.dateDisc,
tblQualityInspectors.qualCNumber, tblQualityInspectors.qualFName,
tblQualityInspectors.qualLName, tblQualityInspectors.qualRole;
Click to expand...
I am using count, but it should count 4 records and put 1, 2, 3, 4 but
does not. My assumption is that at the time the query is ran there is
only 1 record to count for the first line so it moves to the second
line and yet again only 1 line so the count is 1. I do not have any
fields that contain a number, so SUM probably will not work.
Click to expand...
Click to expand...
 
Have you tried the Page Setup tab, Columns, specify 2 columns instead of
1 by default, and click "Down, then Across" instead of the default
"Across, then Down" ?

Vanderghast, Access MVP


OK, let me explain myself better and see if I understand. Here is
what my form looks like:

---------------------------------------------------------
| List of Discrepancies |
---------------------------------------------------------
|PL|P/N |S/N |W/O#|Work Order Name |
| | | | | |
---------------------------------------------------------
|#1|Discrepanc|Correction|QC|#5|Discrepanc|Correction|QC|
| | | | | | | | |
| | | | | | | | |
| |QC Name |TECH Name | | |QC Name |TECH Name | |
|#2|Discrepanc|Correction|QC|#6|Discrepanc|Correction|QC|
| | | | | | | | |
| | | | | | | | |
| |QC Name |TECH Name | | |QC Name |TECH Name | |
|#3|Discrepanc|Correction|QC|#7|Discrepanc|Correction|QC|
| | | | | | | | |
| | | | | | | | |
| |QC Name |TECH Name | | |QC Name |TECH Name | |
|#4|Discrepanc|Correction|QC|#8|Discrepanc|Correction|QC|
| | | | | | | | |
| | | | | | | | |
| |QC Name |TECH Name | | |QC Name |TECH Name | |
---------------------------------------------------------

I can get a runningTtl to show and it increments Over Group, which is
my assumption is what it should be as the group is W/O and the
increment is the multiple discrepancies. So, I am assuming I should
only draw the 20 some boxes to make block 1 and group them? Then in
the detail put the (Me.RunningSumControl.Value - 1 ) MOD 8 < 4 in
OnFormat. However, I do not want to hide/unhide anything as I always
want 8 to show whether they have information in them or not.
I am lost with your design. When you say you have 8 items you mean 8
records
or 8 fields. If you have 8 records, then the running property should
increase one by one if the control is on the same detail level than EACH
record (ie, the detail section). If it is the case and if it still display
1, either you missed to set the running sum to yes, either you have an
additional group that makes each record in its own group, but in general,
running sum works quite well. Fix that first, since the whole logic I
proposed is based on that.

The sub reports should follow the relevant properties
(LinkMasterFields/LinkChildFields), or none if left blank. I don't see why
you would need sub-report, though. Try to keep things simple.

To print always 8 rows of details even if there are less in the given
group,
the only solution that I see at the moment is quite complicated: it is to
use the feature that the report can run twice (as for the case of printing
the total number of pages). You have to keep the number of rows in the
details for each group at the first pass, then, at the second pass, to use
the report runtime variables MoveLayout, NextRecord, PrintSection ( =
true,
true, false) to fill the missing number of details until their number
reach
8. That is a complex process, and the best example that approximatively
does
some thing similar is given
athttp://www.mvps.org/access/reports/rpt0013.htm, but again, that example
does
not exactly what I described, but rather use the two passes technique, the
example does not look for the number of records per group, but look, for
each group, the first and last page on which it appears. There will be
additional code to supply, in your case, for the second pass, in the
detail
event. You may be better to use the report newsgroup, instead of this one
which is about query, though.

Vanderghast, Access MVP


This did not work either as all 8 blocks are on the report, so the
txtRunningTtl never changes from 1 unless I have more than 8 items.
Unless of course you meant to make the report detail blank and put the
txtRunningTtl in a subreport. How would I keep the group of
workorderNum's from bleeding onto one another? Does the grouping in
the main report force the subreport to group as well? How would I
place blank records to fill the 8 blocks?

Also, it would be <4, not <=4, since the possible modulo are 0, 1, 2, 3
and 4, 5, 6, 7 (not 1 to 8 , but 0 to 7). But since the running sum
starts at 1, not 0, you will have to use something like:
Click to expand...
(Me.RunningSumControl.Value - 1 ) MOD 8 < 4
Click to expand...
in the format event of the section, since it is there that you will
hide/unhide the 'movable' textbox (setting its visibility property).
Click to expand...
Vanderghast, Access MVP
Click to expand...
"NeoFax" <[email protected]> wrote in message
Click to expand...
I cannot seem to get this to work, as I do not have a running tally
yet. So, here is my SQL for my query, but the RunningTtl field does
not want to work:
Click to expand...
SELECT tblDiscHeader.workorderNum, tblDiscHeader.workorderName,
tblDiscHeader.workorderMaterial, tblDiscHeader.workorderSerialNum,
tblDiscHeader.workorderComponent, tblProductLine.ProductName,
tblDiscrepancy.Discrepancy, tblDiscrepancy.dateDisc,
tblQualityInspectors.qualCNumber, tblQualityInspectors.qualFName,
tblQualityInspectors.qualLName, tblQualityInspectors.qualRole,
Count(tblDiscrepancy.[Discrepancy]) AS RunningTtl
FROM tblQualityInspectors INNER JOIN (tblProductLine INNER JOIN
(tblDiscHeader INNER JOIN tblDiscrepancy ON tblDiscHeader.ID =
tblDiscrepancy.workorderDisc) ON tblProductLine.ID =
tblDiscHeader.workorderProductline) ON tblQualityInspectors.ID =
tblDiscrepancy.qualDisc
WHERE (((tblDiscHeader.workorderNum)<=[workorderNum]))
GROUP BY tblDiscHeader.workorderNum, tblDiscHeader.workorderName,
tblDiscHeader.workorderMaterial, tblDiscHeader.workorderSerialNum,
tblDiscHeader.workorderComponent, tblProductLine.ProductName,
tblDiscrepancy.Discrepancy, tblDiscrepancy.dateDisc,
tblQualityInspectors.qualCNumber, tblQualityInspectors.qualFName,
tblQualityInspectors.qualLName, tblQualityInspectors.qualRole;
Click to expand...
I am using count, but it should count 4 records and put 1, 2, 3, 4 but
does not. My assumption is that at the time the query is ran there is
only 1 record to count for the first line so it moves to the second
line and yet again only 1 line so the count is 1. I do not have any
fields that contain a number, so SUM probably will not work.
Click to expand...
Click to expand...
 
Back
Top