Re-Post --- 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

You examples of having columns across is wrong. You're committing
'spreadsheet' which will get you in trouble with a database. Your table would
have serious normalization problems and just will NOT work correctly in a
relational database.

I highly recommend getting some relational database training or reading
"Database Design for Mere Mortals" by Hernandez before proceeding any further
on this database.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"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

Jerry is right.

The problem with tbl_Numbers is that if you want to add a 5th reporting
time, you have to add columns to your table, then have to modify all of your
forms or reports. It may seem easier, because you are used to working with
spreadsheets, but much of the power of the relational database will be lost
with this type of structure. The way to handle this is to go vertical, not
horizontal with your data.

tblNumbers
ID
ObsDate
ReportingTime
DataField1
DataField2

For your data entry, I would suggest using a subform, linked to your main
form on the ObsDate field (BTW, Date is a reserved word and should not be
used as a field or table name). When you enter a new date on your main
form, the subform would be empty. I would check to see if there are already
values for the reporting periods in tbl_Numbers (Note #1). If not, I would
insert a record for the date, and each of the Reporting Times in the table
(Note#2). Then, I would requery the subform to display all of the mandatory
reporting times.

Note #1: You could determine whether there are any records for a particular
date by using the DCOUNT( ) domain funcion.
RecCount = DCOUNT("ID", "tblNumbers", "ObsDate = #" & me.txtObservationDate
& "#")
Or, if you are using the subform, in the AfterUpdate event of the
ObservationDate textbox, you could check to see how many records are in the
subform.
RecCount = me.subObservations.Form.RecordCount

Note#2: In order to insert a record for each of the reporting times, you
could either create a table of reporting times and write a query that looks
something like:
strSQL = "INSERT INTO tblNumbers (ObsDate, ReportingTime) " _
& "SELECT #" & me.txtObsDate & "#, ReportingTime " _
& "FROM tblReportingTimes "
currentdb.execute strSQL, dbFailOnError

Or, you could create separate insert queries that actually contain the times
you want to insert. The downside of this, is that if you change the
mandatory reporting times, or decide you want 3 or 5 reports, then you will
have to change your code. With the previous example, you only need to
change the elements of tblReportingTimes.

Your last question asks about finding the highest number in a group of
numbers. This is one of the areas that the new data structure will help
with. Access has another domain function DMAX( ) that allows you to
determine the maximum value in a field in a table. The syntax for that is
DMAX("DataField1", "tblNumbers"). Unfortunately, this will give you the
highest value, regardless of date. However, the function accepts an
optional third (Criteria) argument, which allows you to refine which records
you want. So, you would use something like:

DMAX("DataField1", "tblNumbers", "ObsDate = #" & me.txtObsDate & "#")

Or, in a query, you could do something like:
SELECT ObsDate, DMAX("DataField1", "tblNumbers")
FROM tblNumbers
GROUP BY ObsDate

HTH
Dale

"Jeff @ CI" wrote in message
...
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

Dale - thanks for your input. I knew going into this project that I was
facing a beast. I will be working with what you gave me and seeing what I
can do. I appreciate the effort. I have had to teach myself what I know so
far and for the most part, I am making things work here having eliminated
nearly a dozen spreadsheets my company uses. This is the final "key"
spreadsheet to be eliminated.

"Dale Fye" wrote:

Jerry is right.

The problem with tbl_Numbers is that if you want to add a 5th reporting
time, you have to add columns to your table, then have to modify all of your
forms or reports. It may seem easier, because you are used to working with
spreadsheets, but much of the power of the relational database will be lost
with this type of structure. The way to handle this is to go vertical, not
horizontal with your data.

tblNumbers
ID
ObsDate
ReportingTime
DataField1
DataField2

For your data entry, I would suggest using a subform, linked to your main
form on the ObsDate field (BTW, Date is a reserved word and should not be
used as a field or table name). When you enter a new date on your main
form, the subform would be empty. I would check to see if there are already
values for the reporting periods in tbl_Numbers (Note #1). If not, I would
insert a record for the date, and each of the Reporting Times in the table
(Note#2). Then, I would requery the subform to display all of the mandatory
reporting times.

Note #1: You could determine whether there are any records for a particular
date by using the DCOUNT( ) domain funcion.
RecCount = DCOUNT("ID", "tblNumbers", "ObsDate = #" & me.txtObservationDate
& "#")
Or, if you are using the subform, in the AfterUpdate event of the
ObservationDate textbox, you could check to see how many records are in the
subform.
RecCount = me.subObservations.Form.RecordCount

Note#2: In order to insert a record for each of the reporting times, you
could either create a table of reporting times and write a query that looks
something like:
strSQL = "INSERT INTO tblNumbers (ObsDate, ReportingTime) " _
& "SELECT #" & me.txtObsDate & "#, ReportingTime " _
& "FROM tblReportingTimes "
currentdb.execute strSQL, dbFailOnError

Or, you could create separate insert queries that actually contain the times
you want to insert. The downside of this, is that if you change the
mandatory reporting times, or decide you want 3 or 5 reports, then you will
have to change your code. With the previous example, you only need to
change the elements of tblReportingTimes.

Your last question asks about finding the highest number in a group of
numbers. This is one of the areas that the new data structure will help
with. Access has another domain function DMAX( ) that allows you to
determine the maximum value in a field in a table. The syntax for that is
DMAX("DataField1", "tblNumbers"). Unfortunately, this will give you the
highest value, regardless of date. However, the function accepts an
optional third (Criteria) argument, which allows you to refine which records
you want. So, you would use something like:

DMAX("DataField1", "tblNumbers", "ObsDate = #" & me.txtObsDate & "#")

Or, in a query, you could do something like:
SELECT ObsDate, DMAX("DataField1", "tblNumbers")
FROM tblNumbers
GROUP BY ObsDate

HTH
Dale

"Jeff @ CI" wrote in message
...
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