Issue in create a certain query

[Benjamins via AccessMonster.com]

Hi,

I have 2 tables: tblRecord and tblRange

tblRange contain 4 field:
fldCode, fldStart, fldEnd
fldCode (Text)(Only Manager will know the code, User will not know about it)
fldStart (Number)(Set the start number belong to this code)
fldEnd (Number)(Set the end number belong to this code)

tblRecord
fldId, fldNumber, fldDate, fldDesc, fldVoid
fldId (AutoNumber)
fldNumber(Number)(User Type in base on the range given by the Manager)
fldDate(Date)
fldDesc(Text)(Description of record)

Base on the 2 table i need to generate on a report for the manger to view all
the record within the code range
Example:
tblRange
fldCode fldStart fldEnd
ABC 1001 1010
DEF 4001 4005
GHI 7001 7001

tblRecord
fldId fldNumber fldDate fldDesc
1 1002 01/01/09 xxxxxx
2 4003 01/02/09 xxxxxx
3 4005 01/03/09 xxxxxx
4 7001 01/04/09 xxxxxx
5 1004 01/01/09 xxxxxx
6 4003 01/03/09 xxxxxx
7 7001 01/04/09 xxxxxx

When the Manage want to look at the report for code DEF the following must be
generated.
Code: DEF
4001: No of Record (0)
4002: No of Record (0)
4003: No of Record (2)
2 01/02/09 xxxxxx
6 01/03/09 xxxxxx
4004: No of Record (0)
4005: No of Record (1)
3 01/03/09 xxxxxx

Thus i am thinking of creating a query for the report but is not able to come
up with any ideas. Any suggestion?

Thanks

 

[KARL DEWEY]

I think you need two queries, one for main report and other for subreport.
Create a table named CountNumber with field CountNUM containing numbers from
0 (zero) through your maximum spread.
qryfldIDCount --
SELECT [fldCODE] AS CODE, ([fldStart] + [CountNUM]) AS Field, Count([fldID])
AS [No of Record]
FROM [tblRange], [tblRecord], [CountNumber]
WHERE (([fldStart] + [CountNUM]) Between [fldStart] AND [fldEnd]) AND
([fldNumber] Between [fldStart] AND [fldEnd]) AND [fldDate] Between [Start
date] AND [End date]
GROUP BY [fldCODE], ([fldStart] + [CountNUM]);

SELECT [fldId], [fldNumber], [fldDate], [fldDesc]
FROM qryfldIDCount INNER JOIN [tblRecord] ON [qryfldIDCount].[fldCODE] =
[tblRecord].[fldCODE];

 

[Benjamins via AccessMonster.com]

Hi Karl,

Thanks for yours suggestion. Will it give a try

I think you need two queries, one for main report and other for subreport.
Create a table named CountNumber with field CountNUM containing numbers from
0 (zero) through your maximum spread.
qryfldIDCount --
SELECT [fldCODE] AS CODE, ([fldStart] + [CountNUM]) AS Field, Count([fldID])
AS [No of Record]
FROM [tblRange], [tblRecord], [CountNumber]
WHERE (([fldStart] + [CountNUM]) Between [fldStart] AND [fldEnd]) AND
([fldNumber] Between [fldStart] AND [fldEnd]) AND [fldDate] Between [Start
date] AND [End date]
GROUP BY [fldCODE], ([fldStart] + [CountNUM]);

SELECT [fldId], [fldNumber], [fldDate], [fldDesc]
FROM qryfldIDCount INNER JOIN [tblRecord] ON [qryfldIDCount].[fldCODE] =
[tblRecord].[fldCODE];

Hi,

[quoted text clipped - 48 lines]

Thanks