If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Multi-record to Multi-record - Please help!
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 |
#2
|
|||
|
|||
Multi-record to Multi-record - Please help!
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. -- Duane Hookom Microsoft Access MVP "Jeff @ CI" wrote: 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 |
Thread Tools | |
Display Modes | |
|
|