Complex Report - is this possible?

[AJ]

I have a simple table which I use to record my daily activities, which need
to be reported to management - by department and source. The columns are:
Name
Activity1
Activity2
Activity3
....
Department
Source

Then, for each person I work with that day, I simply double click in that
row's (record's) field (column) that pertains to that activity, and then use
a combo box for the Department and Source. I have about 8 activities, so it
is fabulously quick to report. I have made a vba script to insert the
current date on double-click. (so its all dates). (Then if I work with a
person over a couple of weeks, I can just go back and see how fast progress
is, etc.

Now, trying to report on this, I am trying to make up some script that says
"count the number of instances of "today's date" in this column" and it has
eluded me how to do that.

I have tried: using an unbound text box with the control source:
=count(Activity1) where activity1 = date and it won't accept that...

do I have to write a query for each activiy?

The full goal is to have a daily, and a weekly report (using a date range).

Any suggestions?

- AJ

 

[Duane Hookom]

Apparently you are storing a date value in each ActivityX field. Is this
correct?

Perhaps you could enter some sample data (only 2-3 activity fields) and then
how you want to see these records in a report.

Your "fabulously quick to report" table structure isn't normalized. This
makes many type of reports very difficult. If you are stuck with this
structure, you may be able to create a union query as the basis for your
report.

 

[AJ]

Thanks. comments below

Apparently you are storing a date value in each ActivityX field. Is this
correct?

Its not correct. I am using tables. Fields are not mandatory.

Your "fabulously quick to report" table structure isn't normalized.

You mean - I have the Name/contact info, with the Activity data?
I was trying to simplify things. But you are right - If I had two tables, I
could make a number of other reports, etc... I am not tied to this. I can
fix that.

Does any of this change your answer to the question in the first posting?

- AJ

 

[Duane Hookom]

You haven't clarified your table structure and sample records.

 

[AJ]

Is that what you mean?

Table 1
AppID (autonumber)
Name (text)
E-mail (text)
Source (text)

Table2
ActivID (autonumber)
AppID (nunber) linking field
Activity1 Date
Activity2 Date
Activity3 Date


My grand question is how do I write the scrip that reads like:

"count" instances where Date = Today

so I can have a report that shows "3" activity1 were done today, and "15"
activity3 were done today, etc.

Thanks in advance.

 

[Duane Hookom]

Your reply is very misleading:
Me: Apparently you are storing a date value in each
ActivityX field. Is this correct?
You: Its not correct. I am using tables. Fields are not mandatory.

This is exactly what I meant. Your table is not normalized. You should have
a table like:

tblApplActivity
=====================
ApplID
ActivityID
ActivityDate

Rather than have activities as columns, they should be records. Each date
should have its own record in the above table. This allows you to find all
the activities on a particular date.

 

[AJ]

This looks like the Activity date would have the default value of =date()
and ApplIP and ActicityID would be look up tables... right?

 

[Duane Hookom]

I guess a default of Date() could be used if you wanted the current date
stored in new records.

The ApplID and ActivityID fields would link to other tables where these are
the primary key values. You could think of these a lookup tables.