Multi-record to Multi-record - Please help!

[Jeff @ CI]

Designing in A2k. I need to create a beast in which I can track for a team,
progress reports that are collected 4 times a day. I am thinking along these
lines:

tblStaff
ID = PK
Name
Active

tblNumbers
ID
Date
Data11 (first field of two collected at the first reporting time)
Data12 (second field of two collected at the first reporting time)
Data21 (first field of two collected at the second reporting time)
Data22 (second field of two collected at the secondreporting time)
(etc)

Use a crosstab query to base a form off of (c) ID, (r) Name, (r) Data11,
(r) Data12, etc.

The form will need to be based on date. Thought I could use a calendar
control to determine the date with a subform based on the crosstab query.
Access didn't like that - wanted defined column headings (help???)

Using this form, I would be able to select a date and display what data was
collected on that specific date - planned to filter based on calendar control.

I also want to use this form for data entry. Ideally, it would list all
active employees (versus those no longer with the company) and have textboxes
for entering the data. I also intend to insert calculating text boxes -
which I can do.

The form would be similar to the following

Date 10:30 1:30
3:30
----------------------------------------------------------------------------------------
Name1 Data11 Data12 Data21 Data 22 Data 31
Data 32
Name2 Data11 Data12 Data21 Data 22 Data 31
Data 32
Name3 Data11 Data12 Data21 Data 22 Data 31
Data 32
....


The other criteria that I may need help on is being able to use a function
similar to Excel's =max() function. This would search on each employee for
that day the highest number in each column - first the column of Data11,
data21, data31 and then a second textbox to find the highest number in the
columns of Data12, Data22, Data32.

I hope I have outlined my problem. What queries would I need and what
special VBA - of which I know little about - would I need to make this work?

T I A!!!!

Jeff

 

[Duane Hookom]

I wouldn't use that structure for tblNumbers. My progress table would have a
parent table of ProgDateID (primary key autonumber), staff ID, and
ProgressDate. Then I would create a related child table with ProgDateID,
FirstOrSecond, and Data.

It would take some code if you really wanted to be able to edit your data in
a "spreadsheet" type view. Your reporting could be based on a crosstab which
you should have all the column heading information.