G
Guest
I am needing a query to power a report that my boss wants done by this
friday. i have been working on this report for a couple of weeks now,
without getting anywhere.
this is what i have for my tables.
Parent table: [tbl Defect Count]
ID (autonumber; PK)
Date
Part Number
SortTime
TotalSort
NCM_Num
Containment
PlantNum
Child Table: [tblDefects]
AutoID (autonumber; PK)
ID (linked to [tbl Defect Count].[ID])
DefCode
DefQuantity
the relation ship between these two tables is a 1 to many relationship. for
1 record in [tbl Defect Count] there could be anywhere from 1 to 27 records
in [tblDefects], or no record at all.
what my problem is currently is if there are more records (for a given part
within a given date range) in [tbl Defect Count] than there are records
(records that correspond to an ID in [tbl Defect Count]) in [tblDefects] i
need to have the totals box in the footer of the report set for a running
sum. however, if there are more records (for a given part within a given
date range) in [tblDefects] than there are records (records that correspond
to an ID in [tbl Defect Count]) in [tbl Defect Count] i need to have the
totals box in the footer of the report set for a non-running sum.
example:
[tbl Defect Count] has 2 records (or 2 ID numbers) of a part within a 1
month date range. however [tblDefects] has a total of 3 records between the
two records in [tbl Defect Count]. i need to have my totals box in the
report footer set as a non running sum, otherwise my total is twice than what
it should be.
example 2:
[tbl Defect Count] has 8 records (or 8 ID numbers) for a part within a 1
month date range. [tblDefects] has a total of 6 records between the 8 ID
numbers in [tbl Defect Count] for the date range. I need to have my totals
box in the report footer set as a running sum to count all records and give
me a correct total.
any help in this matter will be greatly appreciated. thanks in advance
friday. i have been working on this report for a couple of weeks now,
without getting anywhere.
this is what i have for my tables.
Parent table: [tbl Defect Count]
ID (autonumber; PK)
Date
Part Number
SortTime
TotalSort
NCM_Num
Containment
PlantNum
Child Table: [tblDefects]
AutoID (autonumber; PK)
ID (linked to [tbl Defect Count].[ID])
DefCode
DefQuantity
the relation ship between these two tables is a 1 to many relationship. for
1 record in [tbl Defect Count] there could be anywhere from 1 to 27 records
in [tblDefects], or no record at all.
what my problem is currently is if there are more records (for a given part
within a given date range) in [tbl Defect Count] than there are records
(records that correspond to an ID in [tbl Defect Count]) in [tblDefects] i
need to have the totals box in the footer of the report set for a running
sum. however, if there are more records (for a given part within a given
date range) in [tblDefects] than there are records (records that correspond
to an ID in [tbl Defect Count]) in [tbl Defect Count] i need to have the
totals box in the footer of the report set for a non-running sum.
example:
[tbl Defect Count] has 2 records (or 2 ID numbers) of a part within a 1
month date range. however [tblDefects] has a total of 3 records between the
two records in [tbl Defect Count]. i need to have my totals box in the
report footer set as a non running sum, otherwise my total is twice than what
it should be.
example 2:
[tbl Defect Count] has 8 records (or 8 ID numbers) for a part within a 1
month date range. [tblDefects] has a total of 6 records between the 8 ID
numbers in [tbl Defect Count] for the date range. I need to have my totals
box in the report footer set as a running sum to count all records and give
me a correct total.
any help in this matter will be greatly appreciated. thanks in advance