rolling up data from 4 similar tables

[Nicole Knapp]

I have 4 tables in my database - the tables are created from 4 different
scantron cards.
The cards all have similar sections

Section 1 - 2 parts - same for each card
PersonID = same person will fill out multiple cards on multiple dates- no
one person has the same ID as another
Date = many cards filled out each date

Section 2
the names of 12 departments - data captured is either blank or a '1'. There
may be none or several departments filled out on each card. Departments are
exactly the same on each of the 4 cards - therefore the same in each table

Section 3
The names of 12 projects - data captured is either a blank or a '1'. There
are none or several projects fillled out on each card. Projects are exactly
the same on each of the 4 cards - therefore the same in each table

Section 4
The attributes none are the same on each card or between cards.
Attributes may only have a '1', '2', or '3' filled in for each attribute.
Multiple attributes per card are allowed.

I need to count number of cards by type and sum those counts by date

I need to count number of cards by individual and sum by date. I have a
separate table with all individuals and IDs.

Then the same for each department, project - I did make tables that only
have project and department names.

Then split out the count of each attribute (1, 2 & 3) by department and/or
project and date.

These will then be trended via pivot charts or exported to excel.

 

[Jeff Boyce]

Nicole

If I'm understanding your description, your cards (?and therefore the tables
you set up) can have multiple repeating columns to indicate specific
departments and/or specific projects and/or ...

If this is how your tables are designed, you have a spreadsheet, not a
relational database table. Both you and Access will have to struggle to do
things that would be simple to accomplish if your data were in a form that
Access understands, i.e., normalized.

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Nicole Knapp]

Jeff,
Thanks for the response.

Yes, they are spreadsheets as well. But the spreadsheets aren't doing a
spectacular job of manipulating the data. (at least not with the set up I
have currently, formulas not VBA).

Because of the large number of people that could potentially fill out a
card, and the formulas involved take time to tabulate and bog down a bit.

In one daily report lists 66 named managers (on the cards by ID# not name),
each has filled out at least 1 card.

So ID303 is Tom K, who did 3 cards on 2/1/09 - 2 A's and a C, 2/2/09 1 B and
1 D; ID1794 is Pat R, who did 1 D on each 1/30/09, 2/1 & 2/3 and a C on 2/2.

Report:
1/30 1/31 2/1 2/2 2/3
Tom K 0 0 3 2 0
Pat R 1 0 1 1 1

This is for some safe practices trending as real time as we can get it. It
will be used heavily for about a month each year. So we want to also trend
against the previous year if possible. This is the first year to start these
cards.

The cards are related, in that the ID#, departments and projects are the
same, aren’t they?

What more information do I need to provide? I’m extremely rusty on my
Access, haven’t used it in many years.

 

[Jeff Boyce]

Nicole

To get the best use of Access' relationally-oriented features and functions,
you can't be feeding it 'sheet data.

Consider turning off the PC and using paper/pencil to sketch out the things
about which you want to keep data (i.e., entities), and how they interaction
(i.e., relationships). Use this as the starting point for the tables (and
relationships) you need in Access.

Then map how to get from the data (and structure) you have in your
spreadsheets/cards to how Access needs to store them. You can do the
physical mapping using queries.

Once all that's done, and you have well-normalized tables, you'll be ready
to use Access queries to select/manipulate your data.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John W. Vinson]

So ID303 is Tom K, who did 3 cards on 2/1/09 - 2 A's and a C, 2/2/09 1 B and
1 D; ID1794 is Pat R, who did 1 D on each 1/30/09, 2/1 & 2/3 and a C on 2/2.

Report:
1/30 1/31 2/1 2/2 2/3
Tom K 0 0 3 2 0
Pat R 1 0 1 1 1

As Jeff suggests, you need to normalize your data. A better structure for this
would be:

Employees
EmployeeID <primary key>
FirstName <e.g. Tom>
LastName


Cards
CardID <primary key>
CardDate <e.g. #2/1/2009#>
EmployeeID <link to Employees, who did it>
Score <e.g. A, B, C>

with one RECORD per card. You can use a Crosstab Query to massage the data
into the depiction you show for presentation purposes, but that layout should
not dictate your table structure.

 

[Nicole Knapp]

Looking at the data more and the charting we want to do off from it, I'll
stick with Excel. Help from the hubby to roll up those attributes.

But I'm off to write a post in the Excel groups about writing a VBA loop to
pull the badge and date so that I can summarize how many cards each person is
filling out (or not) each day.

I'm more than willing to accept your help on this there, if you can.

Thanks for setting me on the right path.

 

[Jeff Boyce]

Please take a look at John's response. He offers a great approach, and the
reminder that the data structure you use in Access is absolutely NOT related
to how you report (i.e., display) data/information.

Regards

Jeff Boyce
Microsoft Office/Access MVP