Help with Date Query and Report

[PenC]

Hello, hopefully someone can help me. Im trying to pull dates to put in a
count condition report. Ill go into detail as much as possible...

Table:

TblExample
Section Column A Column B Column C Column D Etc
RowA 1 1/10 1/10 1/09 1/15

RowB 1 1/10 1/15 1/11 1/15

RowC 1 1/15 1/12 1/11 1/15

RowD 2 1/11 1/15 1/11 1/11

Etc

Im looking for a way to pull each date and its count into a report:

Report:

Section 1 (Header)

Column A. (header)

1/10 - 2 count
Sub Total 2 Count (via running sum)
1/15 - 1 count
Sub total 3 Count (via running sum)
Grand total 3 Count (Footer) (via overall running sum)

Column B. (Header)

1/10 - 1 count
Sub Total 1 Count (via running sum)
1/12 - 1 count
Sub Total 2 Count (via running sum)
1/15 - 1 count
Sub Total 3 Count (via running sum)
Grand Total 3 Count (Footer) (via overall running sum)

Etc

Section 2
1/11 - 1 Count


Etc

Query:

When I try to make a query to pull this information via dlookup:

SELECT tblExample.Section, tblExample.ColumnA, Count(DLookUp("[ColumnA]","
[tblExample]","[ColumnA]<>null")) AS custLookColumnA
FROM tblExample
GROUP BY tblExample.Section, tblExample.ColumnA
HAVING (((tblExample.Section)=1) AND ((tblExample.ColumnA) Is Not Null))
ORDER BY tblExample.Section, tblExample.ColumnA;

My report works for one column but when I start adding other columns (Column
B, C, D etc) it gives me inaccurate numbers (which i assume is because of the
rows sql outputs? IE. one column will have 5 dates while another will have
let say 3) or is it cause of the way i structured the report? Im still new to
Access am I going about this the wrong way? Any help with this mess would be
greatful.

 

[Rick Brandt]

Hello, hopefully someone can help me. Im trying to pull dates to put
in a count condition report. Ill go into detail as much as possible...

Table:

TblExample
Section Column A Column B Column C Column D Etc
RowA 1 1/10 1/10 1/09
1/15

RowB 1 1/10 1/15 1/11
1/15

RowC 1 1/15 1/12 1/11
1/15

RowD 2 1/11 1/15 1/11
1/11

Etc

Im looking for a way to pull each date and its count into a report:

Report:

Section 1 (Header)

Column A. (header)

1/10 - 2 count
Sub Total 2 Count (via running sum)
1/15 - 1 count
Sub total 3 Count (via running sum)
Grand total 3 Count (Footer) (via overall running sum)

Column B. (Header)

1/10 - 1 count
Sub Total 1 Count (via running sum)
1/12 - 1 count
Sub Total 2 Count (via running sum)
1/15 - 1 count
Sub Total 3 Count (via running sum)
Grand Total 3 Count (Footer) (via overall running sum)

Etc

Section 2
1/11 - 1 Count


Etc

Query:

When I try to make a query to pull this information via dlookup:

SELECT tblExample.Section, tblExample.ColumnA,
Count(DLookUp("[ColumnA]"," [tblExample]","[ColumnA]<>null")) AS
custLookColumnA
FROM tblExample
GROUP BY tblExample.Section, tblExample.ColumnA
HAVING (((tblExample.Section)=1) AND ((tblExample.ColumnA) Is Not
Null)) ORDER BY tblExample.Section, tblExample.ColumnA;

My report works for one column but when I start adding other columns
(Column B, C, D etc) it gives me inaccurate numbers (which i assume
is because of the rows sql outputs? IE. one column will have 5 dates
while another will have let say 3) or is it cause of the way i
structured the report? Im still new to Access am I going about this
the wrong way? Any help with this mess would be greatful.

You're struggling because your design is that of a spreadsheet, not of a
properly set up database table. All those dates should be in ONE column
with an additional column to make the distinction that you are currently
making by having multiple columns. Then your query and report is a snap.

 

[PenC]

Hello, hopefully someone can help me. Im trying to pull dates to put
in a count condition report. Ill go into detail as much as possible...

[quoted text clipped - 66 lines]

structured the report? Im still new to Access am I going about this
the wrong way? Any help with this mess would be greatful.

You're struggling because your design is that of a spreadsheet, not of a
properly set up database table. All those dates should be in ONE column
with an additional column to make the distinction that you are currently
making by having multiple columns. Then your query and report is a snap.

I was hoping I wasnt a victim of normalization.. Is their anyway to salvage
the table? How would I go about structuring the tables? Table 1 with the
Dates of Column A ,Table 2 of Column B, Table 3 with sections Section linked
to column A and b (etc)? Help me understand the proper way.

 

[PenC]

[quoted text clipped - 6 lines]

with an additional column to make the distinction that you are currently
making by having multiple columns. Then your query and report is a snap.

I was hoping I wasnt a victim of normalization.. Is their anyway to salvage
the table? How would I go about structuring the tables? Table 1 with the
Dates of Column A ,Table 2 of Column B, Table 3 with sections Section linked
to column A and b (etc)? Help me understand the proper way.

Also to add to this I forgot to mention that Column A , B ,C D are part of a
category so would I put those into one table? Thanks for the quick response

 

[Rick Brandt]

Hello, hopefully someone can help me. Im trying to pull dates to
put in a count condition report. Ill go into detail as much as
possible...

[quoted text clipped - 6 lines]

with an additional column to make the distinction that you are
currently making by having multiple columns. Then your query and
report is a snap.

I was hoping I wasnt a victim of normalization.. Is their anyway to
salvage the table? How would I go about structuring the tables?
Table 1 with the Dates of Column A ,Table 2 of Column B, Table 3
with sections Section linked to column A and b (etc)? Help me
understand the proper way.

Also to add to this I forgot to mention that Column A , B ,C D are
part of a category so would I put those into one table? Thanks for
the quick response

One table...

Category Section Date
A 1 1/10
B 1 1/10
C 1 1/09

etc...