Design for multiple contacts with quarterly data collection and reporting

[csloss]

I'm completely self-taught on Access and have done fairly well in making my rudimentary databases 'work' for me over the years. However...I am stumped on this current one, and have been trying to work it out for nearly a year now...I keep getting stuck and then give up!

The details are as follows:

I have a list of 200+ contacts (physicians) which whom I need to collect data on quarterly, and be able to run various reports on. To get me started in the right direction, at least...does the setup below look accurate, or am I grouping too much into each table?

Table #1


To get me started..I know I should have a "Contacts" Table. In the table, I have:

• ID (Primary Key) (QUESTION: I could use their license # as my primary key instead, couldn't I? I'd like to have that number somewhere in their record anyway)
• Last Name
• First Name
• Company
• Email Address
• Specialty (I was using Lookup Wizard to refer to a specified list of specialties)
• Business Phone
• Mobile Phone
• Fax Number
• Address
• City
• State
• Zip
• Notes
• Attachments
• Status (using Lookup Wizard to refer to one of two different current statuses)
• Initial Appointment Date

Table #2

• Record ID (primary key)
• MD_ID (relating to the specific MD from Table #1)
• Quarter Start Date
• Data Point #1
• Data Point #2
• etc. (~15-20 different questions/data points that are collected on each quarterly)

...after all of that is set up, I'd like to be able to set up my forms so that I can easily input the data, and then report out (1 page per physician, showing their data quarterly across the page so they can see their trending).

[June7]

Might not be easy with that data structure.

If Table2 were like:

RecordID
MD_ID
QtrStart
DataID
DataValue

Then you could do a CROSSTAB query that groups on MD_ID and DataID and pivots on the QtrStart.

Can manipulate the table with a UNION query to get the data structured like that then run CROSSTAB on the UNION table. There is no wizard or designer for UNION, must type into the SQL View of query builder (limit of 50 lines).

SELECT RecordID, MD_ID, QuarterStartDate, DataPoint1 AS DataValue, "DP1" AS Source FROM tablename
UNION SELECT RecordID, MD_ID, QuarterStartDate, DataPoint2, "DP2" AS Source FROM tablename
...
UNION SELECT RecordID, MD_ID, QuarterStartDate, DataPoint20, "DP20" AS Source FROM tablename;

Alternative probably involves a lot of VBA code.


BTW, recommend no spaces or special characters/punctuation (underscore is exception) in any names.

[csloss]

Thanks, June!
For DataID/DataValue .. I'm a little stuck there. Would I AutoNumber the DataID? Or would I have a third table that would reference to the DataID (i.e., #1 = Question #1, #2 = Question #2, etc.)?

Might not be easy with that data structure.

If Table2 were like:

RecordID
MD_ID
QtrStart
DataID
DataValue

Then you could do a CROSSTAB query that groups on MD_ID and DataID and pivots on the QtrStart.

Can manipulate the table with a UNION query to get the data structured like that then run CROSSTAB on the UNION table. There is no wizard or designer for UNION, must type into the SQL View of query builder (limit of 50 lines).

SELECT RecordID, MD_ID, QuarterStartDate, DataPoint1 AS DataValue, "DP1" AS Source FROM tablename
UNION SELECT RecordID, MD_ID, QuarterStartDate, DataPoint2, "DP2" AS Source FROM tablename
...
UNION SELECT RecordID, MD_ID, QuarterStartDate, DataPoint20, "DP20" AS Source FROM tablename;

Alternative probably involves a lot of VBA code.


BTW, recommend no spaces or special characters/punctuation (underscore is exception) in any names.

[June7]

DataID would be a combobox to select the ID of data element. This means, yes, you need a third table of data elements and their ID. Or if the data element name is short, use it instead of a cryptic ID. That would eliminate the issues associated with lookups with alias.

You might be able to emulate a CROSSTAB with your original data structure. Review: http://datapigtechnologies.com/flashfiles/crosstab.html