How do I get a total, based on dates?

[larryo]

I have created a database and input form for a non-profit. Among the services
the non-profit offers, it allows 'clients' to come in once per week and get
bag(s) of groceries from its food cupboard. When they come in, a 'date' entry
is made in the form corresponding to the date they come in. So if a client
comes in once a week, every week of the year, there would be 52 dates
entered, and the total or 'count' or 'sum' of the visits would be 52.
However, very few of the 'clients' come in every week. But the non-profit
would like to get a tally of the number of visits within a specified period
of time per client. So, what I would like to do, however it may be done -
either in a query or a report, is to total the number of times a client comes
in by 'counting' or 'summing' the 'dates'. No matter what I have tried, I
just can't get it to work. Any help will be greatly appreciated

 

[tina]

seems like a simple Totals query should work okay. just GroupBy the client
ID field, and Count the visit date field. set criteria on the visit date
field to pull only the records with dates in a specified time frame, using
Between...And...

hth

 

[larryo]

Tina,
That worked (thank you very much) for getting a total for the entries in the
column, but how do I get a total across rows? I have headings for each month,
and when a client gets a bag of groceries, a date entry is made in that
column. I'm certainly not proficient with Access, but what I think I also
need to do is create another column in my query and use an expression for
this purpose. I've tried 'sum', I've tried =, and then the + sign to add the
months. I either get the addition of the dates (not a count) or after trying
other possibilities, error messages. I hope you can give me a simple solution
for this one, too.

 

[John W. Vinson]

I have headings for each month,

Then your table structure IS WRONG.

You're "committing spreadsheet". Each month's value should be in *a new record
in a related table*, not in a field in this table.

See the tutorials at

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

A free tutorial written by Crystal (MS Access MVP):
http://allenbrowne.com/casu-22.html

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

 

[larryo]

John,
Thanks for your comeback, but it didn't help. I have already purchased and
poured through several books in my efforts to learn Access. I have also
studied many of the Microsoft tutorials on many of the subjects. I have
labored through setting up my tables and creating forms, and the non-profit
for which I am volunteering is so far delighted with what I have been able to
provide them. As bad as you seem to think my database is, it is far better
than the database they were working with in MS Works. I appreciate you
telling me that I am "Committing spreadsheet", but I have absolutely no clue
as to what that means, and providing me with links to hundreds of tutorials
is not the kind of help I was hoping to get.

 

[John W. Vinson]

I appreciate you
telling me that I am "Committing spreadsheet", but I have absolutely no clue
as to what that means, and providing me with links to hundreds of tutorials
is not the kind of help I was hoping to get.

I'll be willing to help if you'll post the names and datatypes of (at least
some of) the relevant fields in your table.

"Committing spreadsheet" means that you 're using a typical spreadsheet
design, with column names like January, February, March and so on. That's good
design for a spreadsheet, but inappropriate for a relational table; if you
have data like

Client; January; February; March; April; ...
"John Doe"; 5; 10; <null>; 5

you should instead consider turning this structure "on its side":

Clients
ClientID <primary key, e.g. 31>
LastName <e.g. "Doe">
FirstName <e.g. "John">
<other data about the person>

Distributions
ClientID
DistDate <#1-Feb-2009#>
Amount <5>

with one *RECORD* per month, rather than one *FIELD* per month.

 

[tina]

well, unfortunately there's no real simple solution here, larry. your
table's structure doesn't adhere to relational design principles. since the
Access software is designed based on those principles, it's much much harder
to do just about anything when you're working with a structure that doesn't
fit the proper design parameters (like trying to turn a flathead screw by
using a Phillips-head screwdriver).

since you're volunteering your services to a nonprofit group, i'm willing to
volunteer some time to give you a hand, if you like. if your db is in any
version between A97 and A2003, you can send me a copy of it (sans
proprietary data, dummy in some typical but fake data) and i'll review it,
address normalization issues, and send you back a notated copy that you can
study "hands on". if you want to go this route, post back and we'll go from
there.

hth

 

[larryo]

John,
I don’t know how to thank you for staying with me. I believe I have listed
below the information you requested. My database for this food cupboard/soup
kitchen consists of 7 tables as follows: clients, finances, adults, children,
contacts, 07-08 Aid Prog, and 08-09 Aid Prog. To facilitate input, I created
an input form, with the main form being clients, and ‘tabs’ to access the
other 6 which I created as subforms. The subforms for adults, children, have
the capability to accommodate multiple inputs since the client may have a
spouse as well as other adults in the housing unit. Likewise there may be
more than one child. And there is the need to maintain a record of the
‘contacts’. The forms are set up to collect considerable information such
as date of birth, ethnicity, all sources of income, housing, etc.
The non-profit operates on a fiscal year (10/1 thru 9/30) and an example of
how the aid tables are set up is as follows (md stands for milk and dairy):
clientID visitNo mdoct mdnov mddec mdjan
etc.
001 23 10/5/2008 11/23/2008 12/18/2008
017 44 10/14/2008 12/28/2008 1/5/2009
2/7/2009
143 64 10/21/2008 11/2/2008 1/17/2009
208 124 11/21/2008 12/3/2008 2/4/2009
In the query/report I have been attempting to create, I need to know how
many visits per month or specified period, but I also need to know ‘number of
visits per client’ per specified period. In other words, total the above
dates (counts) both horizontally and vertically.
I began developing this database around March of 2007, and the organization
went ‘on-line’ with it on October 1, 2007. As of yesterday, 2/9/2009, they
had entered data for 675 ‘clients’. They are now hoping that I can create
detailed specific reports. I believe the database collects the necessary
information, now I need to work on how to ‘access’ it. Any and all help will
be greatly appreciated.

 

[John W. Vinson]

John,
I don’t know how to thank you for staying with me. I believe I have listed
below the information you requested. My database for this food cupboard/soup
kitchen consists of 7 tables as follows: clients, finances, adults, children,
contacts, 07-08 Aid Prog, and 08-09 Aid Prog. To facilitate input, I created
an input form, with the main form being clients, and ‘tabs’ to access the
other 6 which I created as subforms. The subforms for adults, children, have
the capability to accommodate multiple inputs since the client may have a
spouse as well as other adults in the housing unit. Likewise there may be
more than one child. And there is the need to maintain a record of the
‘contacts’. The forms are set up to collect considerable information such
as date of birth, ethnicity, all sources of income, housing, etc.
The non-profit operates on a fiscal year (10/1 thru 9/30) and an example of
how the aid tables are set up is as follows (md stands for milk and dairy):
clientID visitNo mdoct mdnov mddec mdjan
etc.
001 23 10/5/2008 11/23/2008 12/18/2008
017 44 10/14/2008 12/28/2008 1/5/2009
2/7/2009
143 64 10/21/2008 11/2/2008 1/17/2009
208 124 11/21/2008 12/3/2008 2/4/2009
In the query/report I have been attempting to create, I need to know how
many visits per month or specified period, but I also need to know ‘number of
visits per client’ per specified period. In other words, total the above
dates (counts) both horizontally and vertically.
I began developing this database around March of 2007, and the organization
went ‘on-line’ with it on October 1, 2007. As of yesterday, 2/9/2009, they
had entered data for 675 ‘clients’. They are now hoping that I can create
detailed specific reports. I believe the database collects the necessary
information, now I need to work on how to ‘access’ it. Any and all help will
be greatly appreciated.

If I were you I would jump to accept Tina's offer to take your database and
propose a normalized structure for it. As she says, your current structure
simply makes it very, very difficult to do what you ask; it needs to have its
basic structure rebuilt in a normalized way in order for Access' tools to make
effective use of it.

The aid table is... I fear... completely non-normalized. I don't even
understand the logic; how does a "VistNo" relate to the dates in the mdoct,
mdnov, etc. fields??? I would expect each instance of aid to have fields such
as ClientID (who received the aid), Commodity (what did they receive -
milk/dairy, meat, household supplies, ...), AidDate (a simple date/time
field). There would NOT be tables named 07-08 Aid Prog or the like! There'd
just be A TABLE for all aid; the dates would be in a (single!) date field in
the table, and you could easily use a Query to extract fiscal year 2008 data,
or fiscal year 2009 data, or any particular month's data.

If you choose to take up Tina's generous offer I expect you'll be pleased.
She's a real pro and should be able to set you on the right track.

 

[tina]

John, there's nobody in these ngs whose respect would mean more to me, and
especially in the subject of relational design; thank you for your kind
words. <bows low>

 

[larryo]

John and Tina,
Thank you both for your responses. If nothing else, you've made me aware,
after having struggled with Access since about March of 2007, of how little I
know, and how far I have to go. Today I went to a presentation on Salesforce,
and was quite impressed. It appears to be much less technical, and much more
user friendly, so I'm going to back away from my attempts to work with Access
and look into that program. It may be just what the non-profit I'm working
with needs. Thank you again.
--
larryo

John, there's nobody in these ngs whose respect would mean more to me, and
especially in the subject of relational design; thank you for your kind
words. <bows low>

 

[John W. Vinson]

John and Tina,
Thank you both for your responses. If nothing else, you've made me aware,
after having struggled with Access since about March of 2007, of how little I
know, and how far I have to go. Today I went to a presentation on Salesforce,
and was quite impressed. It appears to be much less technical, and much more
user friendly, so I'm going to back away from my attempts to work with Access
and look into that program. It may be just what the non-profit I'm working
with needs. Thank you again.

If they can afford it, a tested commercial turnkey system may in fact be
preferable to "reinventing the wheel" yourself in Access. Good luck!

 

[larryo]

John,
As I indicated before, I am doing this project as a volunteer for a
non-profit. As such, as I understand, non-profits can obtain a license from
Salesforce that enables them to install the software on up to 10 pc's for no
charge. From what I further understand, the learning curve for their program
is nowhere near as steep as that for Access. Thanks again for your help.

 

[John W. Vinson]

John,
As I indicated before, I am doing this project as a volunteer for a
non-profit. As such, as I understand, non-profits can obtain a license from
Salesforce that enables them to install the software on up to 10 pc's for no
charge.

From what I further understand, the learning curve for their program
is nowhere near as steep as that for Access. Thanks again for your help.

Sure; it's like getting a preassembled garden shed rather than a truckload of
lumber, nails, and shingles <g>