Count Dta Fields

[visidro]

I would like to setup a Form where I can count the number of records that
contain the same date on specific fields. The fields have a short date
dd/mm/yyyy. Please what is the syntax? I have tried several things with
little success
Ultimately I would like to have a button next to this count where the user
can print details of the records found to have the same date
Your assistance is much appreciated
thanks
victor

 

[John W. Vinson]

I would like to setup a Form where I can count the number of records that
contain the same date on specific fields. The fields have a short date
dd/mm/yyyy. Please what is the syntax? I have tried several things with
little success
Ultimately I would like to have a button next to this count where the user
can print details of the records found to have the same date
Your assistance is much appreciated
thanks
victor

Please explain your table structure. Do you have multiple date fields in each
record and you want to compare them across fields within the record, or do you
have one date field and want to find duplicates across records, or what?

 

[visidro]

I have various date fields within the same record. I want to find duplicates
across records for each field
thanks John

 

[John W. Vinson]

I have various date fields within the same record. I want to find duplicates
across records for each field
thanks John

You can do it one field at a time, by creating a Totals query grouping by the
first date field, counting the Primary Key field, and setting a criterion of

1 on the count. You will need to do this for each datefield; you could use a

UNION query to bring all the counts into one recordset.

For a more specific answer please post more details of your table structure
(fieldnames for instance) and just how you want to see the results.

 

[visidro]

Thanks a lot John
My querie knowledge is limited could you possible help me with the syntax
please?
the fields is questions are like this:

Enter dates: _________ _________ __________ ___________ _________

The user will enter up to 5 different dates where the fields are defined as
short date dd/mm/yyyy

much apreciated
victor

 

[John W. Vinson]

Thanks a lot John
My querie knowledge is limited could you possible help me with the syntax
please?
the fields is questions are like this:

Enter dates: _________ _________ __________ ___________ _________

The user will enter up to 5 different dates where the fields are defined as
short date dd/mm/yyyy

The format is irrelevant. Dates are stored as numbers, not as strings.

I will be glad to help if, as requested, you post the relevant structure and
fieldnames of your table. I can't see your screen from here!

 

[visidro]

The table is called Students Database Table (the table is rather large to
post here)
The field names are campweek1, campweek2, campweek3, campweek4, campweek5,
right now I have no data in the fields but they will be in this format
01/07/2008, (the user is free to enter any date), This is what I see when I
enter a date and look at the table. The data type for these is Date/Time and
the format is Short Date

thank you Victor

 

[John W. Vinson]

The table is called Students Database Table (the table is rather large to
post here)
The field names are campweek1, campweek2, campweek3, campweek4, campweek5,

Then your data structure IS WRONG.

If you have five fields with names ending in five digits... you have a one to
many relationship. Rather than having five *FIELDS*, you should really have
two *TABLES* in a one to many relationship, with one date in each record.

You can search this database, if you insist on using this "spreadsheet"
structure - but it will be much harder and require much more complex queries.
Worse, if you ever add a sixth (or a tenth!!) week, you'll need to restructure
your table, rebuild all your queries, rebuild all your forms and reports...
ouch!!!

I still don't clearly understand what you mean by counting duplicates across
records. Do you mean that you want to count instances where one record's
Campweek1 is equal to the *same* records Campweek4? or a *different* record's
Campweek1? or a different record's Campweek3?

 

[visidro]

Maybe I am not explaining it properly:

Here is my table:
Student id Autonumber (key)
Student Name Text
Camp Week1 Date/Time
Camp Week2 Date/Time
Camp Week3 Date/Time
Camp Week4 Date/Time
Camp Week5 Date/Time

What would be the syntax to find:
The number of students that have a Camp Week date of July 7, 2008
The number of students that have a Camp week date of July 14, 2008
The number of students that have a Camp week date of July 21, 2008
etc .....

hope that helps
thanks
victor

 

[John W. Vinson]

Maybe I am not explaining it properly:

Here is my table:
Student id Autonumber (key)
Student Name Text
Camp Week1 Date/Time
Camp Week2 Date/Time
Camp Week3 Date/Time
Camp Week4 Date/Time
Camp Week5 Date/Time

Again:

THIS TABLE STRUCTURE IS WRONG.

You're "Committing spreadsheet upon a table". You're making this query a
*great deal harder to write* by storing one piece of data - a camp week - in
five different places.

A better structure would have two tables in a one to many relationship:

Students
StudentID <Autonumber Primary Key>
LastName
FirstName <do split the name into at least two fields so you can search or
sort by either name>

CampWeeks
StudentID <Long Integer, foreign key to Students>
WeekNo <integer, 1 to 5 at present, may change at some point>
WeekDate <date/time>

YOu would add *five records* - using a continuous subform for convenience -
rather than five fields.

What would be the syntax to find:
The number of students that have a Camp Week date of July 7, 2008
The number of students that have a Camp week date of July 14, 2008
The number of students that have a Camp week date of July 21, 2008
etc .....

With your current structure you will need a Query based on another Query.
First create a UNION query to normalize the data. You 'll need to go into SQL
view to do this:

SELECT [Student ID], [Camp Week1] AS CampWeek FROM yourtable
WHERE [Camp Week1] IS NOT NULL
UNION ALL
SELECT [Student ID], [Camp Week2] FROM yourtable
WHERE [Camp Week2] IS NOT NULL
UNION ALL
SELECT [Student ID], [Camp Week3] FROM yourtable
WHERE [Camp Week3] IS NOT NULL
UNION ALL
SELECT [Student ID], [Camp Week4] FROM yourtable
WHERE [Camp Week4] IS NOT NULL
UNION ALL
SELECT [Student ID], [Camp Week5] FROM yourtable
WHERE [Camp Week5] IS NOT NULL;

Save this as qryAllWeeks. Then create a second query:

SELECT Count([Student ID]), CampWeek
FROM qryAllWeeks GROUP BY CampWeek
WHERE <optional criteria>

 

[visidro]

Thanks for your help John

Maybe I am not explaining it properly:

Here is my table:
Student id Autonumber (key)
Student Name Text
Camp Week1 Date/Time
Camp Week2 Date/Time
Camp Week3 Date/Time
Camp Week4 Date/Time
Camp Week5 Date/Time

Again:

THIS TABLE STRUCTURE IS WRONG.

You're "Committing spreadsheet upon a table". You're making this query a
*great deal harder to write* by storing one piece of data - a camp week - in
five different places.

A better structure would have two tables in a one to many relationship:

Students
StudentID <Autonumber Primary Key>
LastName
FirstName <do split the name into at least two fields so you can search or
sort by either name>

CampWeeks
StudentID <Long Integer, foreign key to Students>
WeekNo <integer, 1 to 5 at present, may change at some point>
WeekDate <date/time>

YOu would add *five records* - using a continuous subform for convenience -
rather than five fields.

What would be the syntax to find:
The number of students that have a Camp Week date of July 7, 2008
The number of students that have a Camp week date of July 14, 2008
The number of students that have a Camp week date of July 21, 2008
etc .....

With your current structure you will need a Query based on another Query.
First create a UNION query to normalize the data. You 'll need to go into SQL
view to do this:

SELECT [Student ID], [Camp Week1] AS CampWeek FROM yourtable
WHERE [Camp Week1] IS NOT NULL
UNION ALL
SELECT [Student ID], [Camp Week2] FROM yourtable
WHERE [Camp Week2] IS NOT NULL
UNION ALL
SELECT [Student ID], [Camp Week3] FROM yourtable
WHERE [Camp Week3] IS NOT NULL
UNION ALL
SELECT [Student ID], [Camp Week4] FROM yourtable
WHERE [Camp Week4] IS NOT NULL
UNION ALL
SELECT [Student ID], [Camp Week5] FROM yourtable
WHERE [Camp Week5] IS NOT NULL;

Save this as qryAllWeeks. Then create a second query:

SELECT Count([Student ID]), CampWeek
FROM qryAllWeeks GROUP BY CampWeek
WHERE <optional criteria>