Autofill every possible record

[Steven P via AccessMonster.com]

Hello,
My question is probably elementary but I need help. I have numerous machines
listed in a column. Records will be entered daily, usually for each machine
and some machines will have multiple entries. What I wish to do is autofill a
column "available hours" with 24 for each machine on a daily basis. I need
the autofill to take place whether or not data is entered for the machine, or
if multiple entries are made, the available hours still must total 24 for
each machine daily.
Also, how can I use a column of holiday days so the update will not autofill
on the days in the holiday column.

Thanks,
Steven

 

[Allen Browne]

I take it that you book machines in 1-hour blocks, so you need to create 24
x 1-hour blocks, for each machine, for each date, omitting the dates listed
on a table of holidays.

The solution below uses a table of hours, a table of dates, and your
existing table of machines. Combining them in a query without joins (a
Cartesian Product), gives you every possible combination. You then execute
this as an Append query to populate your table with all the desired records.
(I assume the target table has fields for MachineID and the date/time.) We
will use a lower-level query to skip the holidays.

Steps:

1. Build a table of hours.
Create a table with one field named CountID, Number type, Long Integer size.
Mark the field as primary key. Save the table as tblCount. Enter 24
records - values from 0 to 23. Close.

2. Build a table of dates.
Create another table, with a field named TheDate, Date/Time type, primary
key. Save the table as tblDate. Populate the table with all dates for the
range you wish to use.

3. Build the query to skip the holidays.
Create a query using tblDate and your table of holidays, joined on the date
fields. Double-click the line joining the 2 tables. Access pops up a dialog
offering 3 options. Choose:
All records from tblDate, and any matches from Holidays.
Drag TheDate into the grid form tblDate.
Drag the holiday date into the grid, and uncheck the Show box for this
field.
In the Criteria row under this field, enter:
Is Null
Save the query as qryWorkDays.

4. Build the query to generate all the records.
Create a query that uses these source "tables":
- your table of machines
- qryWorkDays
- tblCount.
There must be no line joining the tables in the upper pane of query design.
Drag the MachineID field from the Machine table into the grid.
In the next column of the field row, enter:
DateAdd("h", tblCount.CountID, qryWorkDays.TheDate)
Test the output: you should see a record for each hour of each work day for
each machine.

5. Execute the query.
Change the query to an Append query (Append on Query menu.)
Tell Access the name of the table to append to.
Run the query (Run on Query menu.)
You now have all the records in your target table.


If you have a large range of dates at step 2, you can use the code below to
programmatically populate the table of dates:

Function MakeDates(dtStart As Date, dtEnd As Date) As Long
Dim dt As Date
Dim rs As DAO.Recordset

Set rs = DBEngine(0)(0).OpenRecordset("tblDate")
With rs
For dt = dtStart To dtEnd
.AddNew
!TheDate = dt
.Update
Next
End With
rs.Close
Set rs = Nothing
End Function

 

[Steven P via AccessMonster.com]

I don't use 1 hour blocks so to speak. I only need to insert a total of 24
hours per machine, making sure that I do not add the time for multiple
entries per machine, and have 24 hours per machine per day total. I already
have inputs for day, and machines. Machine comes from a drop down combo list
tied to the machine table. I had previously inserted a column for available
time for the operator to enter, but the fear was at the end of the day all
the seperate entries would not total 24. It does not matter if the machine
ran at all, the available time is still 24 hours. The addition is I need to
have it recognize around 12 holidays from the holiday table and omit these
from auto update to available hours.

I take it that you book machines in 1-hour blocks, so you need to create 24
x 1-hour blocks, for each machine, for each date, omitting the dates listed
on a table of holidays.

The solution below uses a table of hours, a table of dates, and your
existing table of machines. Combining them in a query without joins (a
Cartesian Product), gives you every possible combination. You then execute
this as an Append query to populate your table with all the desired records.
(I assume the target table has fields for MachineID and the date/time.) We
will use a lower-level query to skip the holidays.

Steps:

1. Build a table of hours.
Create a table with one field named CountID, Number type, Long Integer size.
Mark the field as primary key. Save the table as tblCount. Enter 24
records - values from 0 to 23. Close.

2. Build a table of dates.
Create another table, with a field named TheDate, Date/Time type, primary
key. Save the table as tblDate. Populate the table with all dates for the
range you wish to use.

3. Build the query to skip the holidays.
Create a query using tblDate and your table of holidays, joined on the date
fields. Double-click the line joining the 2 tables. Access pops up a dialog
offering 3 options. Choose:
All records from tblDate, and any matches from Holidays.
Drag TheDate into the grid form tblDate.
Drag the holiday date into the grid, and uncheck the Show box for this
field.
In the Criteria row under this field, enter:
Is Null
Save the query as qryWorkDays.

4. Build the query to generate all the records.
Create a query that uses these source "tables":
- your table of machines
- qryWorkDays
- tblCount.
There must be no line joining the tables in the upper pane of query design.
Drag the MachineID field from the Machine table into the grid.
In the next column of the field row, enter:
DateAdd("h", tblCount.CountID, qryWorkDays.TheDate)
Test the output: you should see a record for each hour of each work day for
each machine.

5. Execute the query.
Change the query to an Append query (Append on Query menu.)
Tell Access the name of the table to append to.
Run the query (Run on Query menu.)
You now have all the records in your target table.

If you have a large range of dates at step 2, you can use the code below to
programmatically populate the table of dates:

Function MakeDates(dtStart As Date, dtEnd As Date) As Long
Dim dt As Date
Dim rs As DAO.Recordset

Set rs = DBEngine(0)(0).OpenRecordset("tblDate")
With rs
For dt = dtStart To dtEnd
.AddNew
!TheDate = dt
.Update
Next
End With
rs.Close
Set rs = Nothing
End Function

My question is probably elementary but I need help. I have numerous
machines

[quoted text clipped - 10 lines]

autofill
on the days in the holiday column.

 

[Allen Browne]

If you are not using 1-hour blocks, presumably a booking for a machine could
be for any timeslot at all, e.g. 9:22am - 10:43am. If so, the available
hours will be just the sum of minutes for all the bookings for the machine
for the day, subtracted from 24 hours. Are you just asking how to make that
calculation?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I don't use 1 hour blocks so to speak. I only need to insert a total of 24
hours per machine, making sure that I do not add the time for multiple
entries per machine, and have 24 hours per machine per day total. I
already
have inputs for day, and machines. Machine comes from a drop down combo
list
tied to the machine table. I had previously inserted a column for
available
time for the operator to enter, but the fear was at the end of the day all
the seperate entries would not total 24. It does not matter if the machine
ran at all, the available time is still 24 hours. The addition is I need
to
have it recognize around 12 holidays from the holiday table and omit these
from auto update to available hours.

I take it that you book machines in 1-hour blocks, so you need to create
24
x 1-hour blocks, for each machine, for each date, omitting the dates
listed
on a table of holidays.

The solution below uses a table of hours, a table of dates, and your
existing table of machines. Combining them in a query without joins (a
Cartesian Product), gives you every possible combination. You then execute
this as an Append query to populate your table with all the desired
records.
(I assume the target table has fields for MachineID and the date/time.) We
will use a lower-level query to skip the holidays.

Steps:

1. Build a table of hours.
Create a table with one field named CountID, Number type, Long Integer
size.
Mark the field as primary key. Save the table as tblCount. Enter 24
records - values from 0 to 23. Close.

2. Build a table of dates.
Create another table, with a field named TheDate, Date/Time type, primary
key. Save the table as tblDate. Populate the table with all dates for the
range you wish to use.

3. Build the query to skip the holidays.
Create a query using tblDate and your table of holidays, joined on the
date
fields. Double-click the line joining the 2 tables. Access pops up a
dialog
offering 3 options. Choose:
All records from tblDate, and any matches from Holidays.
Drag TheDate into the grid form tblDate.
Drag the holiday date into the grid, and uncheck the Show box for this
field.
In the Criteria row under this field, enter:
Is Null
Save the query as qryWorkDays.

4. Build the query to generate all the records.
Create a query that uses these source "tables":
- your table of machines
- qryWorkDays
- tblCount.
There must be no line joining the tables in the upper pane of query
design.
Drag the MachineID field from the Machine table into the grid.
In the next column of the field row, enter:
DateAdd("h", tblCount.CountID, qryWorkDays.TheDate)
Test the output: you should see a record for each hour of each work day
for
each machine.

5. Execute the query.
Change the query to an Append query (Append on Query menu.)
Tell Access the name of the table to append to.
Run the query (Run on Query menu.)
You now have all the records in your target table.

If you have a large range of dates at step 2, you can use the code below
to
programmatically populate the table of dates:

Function MakeDates(dtStart As Date, dtEnd As Date) As Long
Dim dt As Date
Dim rs As DAO.Recordset

Set rs = DBEngine(0)(0).OpenRecordset("tblDate")
With rs
For dt = dtStart To dtEnd
.AddNew
!TheDate = dt
.Update
Next
End With
rs.Close
Set rs = Nothing
End Function

My question is probably elementary but I need help. I have numerous
machines

[quoted text clipped - 10 lines]

autofill
on the days in the holiday column.

 

[Steven P via AccessMonster.com]

Allen,
Not exactly. The time is of no importance, that is we do not enter data for
a specific time of day. At the end of a shift (usually) an operator will
enter his production data for a machine. The night shift person will also
enter data sometime that night. For all records entered for each machine, I
want the available time to total 24 hrs. for the machine per calendar day,
excluding holidays. A machine could have several entries per day. Make sense?

Steven

If you are not using 1-hour blocks, presumably a booking for a machine could
be for any timeslot at all, e.g. 9:22am - 10:43am. If so, the available
hours will be just the sum of minutes for all the bookings for the machine
for the day, subtracted from 24 hours. Are you just asking how to make that
calculation?

I don't use 1 hour blocks so to speak. I only need to insert a total of 24
hours per machine, making sure that I do not add the time for multiple

[quoted text clipped - 97 lines]

 

[Allen Browne]

So you want to know if the sum of all entries for a machine on a date comes
to 24 hours or not.

What fields do you have in your table? Presumably MachineID as a foreign
key. Do you have a Start Time and Duration? A StartTime and EndTime? Is the
date date in these fields as well, or is it a separate Date/Time field?

Do you have shifts that roll past midnight (i.e. part of the shift must
count for one day, and the rest of another)?

The specifics on how to query this and get the sum of hours will depend on
how the data structure is set up.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen,
Not exactly. The time is of no importance, that is we do not enter data
for
a specific time of day. At the end of a shift (usually) an operator will
enter his production data for a machine. The night shift person will also
enter data sometime that night. For all records entered for each machine,
I
want the available time to total 24 hrs. for the machine per calendar day,
excluding holidays. A machine could have several entries per day. Make
sense?

Steven

If you are not using 1-hour blocks, presumably a booking for a machine
could
be for any timeslot at all, e.g. 9:22am - 10:43am. If so, the available
hours will be just the sum of minutes for all the bookings for the machine
for the day, subtracted from 24 hours. Are you just asking how to make
that
calculation?

I don't use 1 hour blocks so to speak. I only need to insert a total of
24
hours per machine, making sure that I do not add the time for multiple

[quoted text clipped - 97 lines]

autofill
on the days in the holiday column.

 

[Steven P via AccessMonster.com]

Allen,
I'm not after a sum because there will be no entry for "available time". I
wish to have "available time" automatically entered, for each machine, each
day and the total hours need to be 24 regardless of how many entries are made
for the day. My table is similar to this.

Todays date
Employee ID
Machine #
Goal
Good Pieces
Available hours
etc.

The available hours was initially going to be entered manually. I want to
auto update this because if no entry is made for the machine for the day the
available time is still 24 hours.I am using this to calculate machine
uptime/efficiencies etc.

Steven

So you want to know if the sum of all entries for a machine on a date comes
to 24 hours or not.

What fields do you have in your table? Presumably MachineID as a foreign
key. Do you have a Start Time and Duration? A StartTime and EndTime? Is the
date date in these fields as well, or is it a separate Date/Time field?

Do you have shifts that roll past midnight (i.e. part of the shift must
count for one day, and the rest of another)?

The specifics on how to query this and get the sum of hours will depend on
how the data structure is set up.

Allen,
Not exactly. The time is of no importance, that is we do not enter data

[quoted text clipped - 23 lines]

 

[Allen Browne]

Sorry, Steven, I think I need to bow out, as I cannot follow what you are
doing.

Storing an amount that depends on what other records are present, and trying
to keep it up to date makes no sense to me at all.

Hopefully someone else can help.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen,
I'm not after a sum because there will be no entry for "available time". I
wish to have "available time" automatically entered, for each machine,
each
day and the total hours need to be 24 regardless of how many entries are
made
for the day. My table is similar to this.

Todays date
Employee ID
Machine #
Goal
Good Pieces
Available hours
etc.

The available hours was initially going to be entered manually. I want to
auto update this because if no entry is made for the machine for the day
the
available time is still 24 hours.I am using this to calculate machine
uptime/efficiencies etc.

Steven

So you want to know if the sum of all entries for a machine on a date
comes
to 24 hours or not.

What fields do you have in your table? Presumably MachineID as a foreign
key. Do you have a Start Time and Duration? A StartTime and EndTime? Is
the
date date in these fields as well, or is it a separate Date/Time field?

Do you have shifts that roll past midnight (i.e. part of the shift must
count for one day, and the rest of another)?

The specifics on how to query this and get the sum of hours will depend on
how the data structure is set up.

Allen,
Not exactly. The time is of no importance, that is we do not enter data

[quoted text clipped - 23 lines]

autofill
on the days in the holiday column.

 

[Steven P via AccessMonster.com]

Allen,
I know what I'm attempting may be a bit unusual but I think maybe it's not
as bad as what you think. Simply put, can I not have a field that is updated
to 24 for every machine I have listed in a table, and done for every day
except holidays? What about this makes no sense?
Steven

Sorry, Steven, I think I need to bow out, as I cannot follow what you are
doing.

Storing an amount that depends on what other records are present, and trying
to keep it up to date makes no sense to me at all.

Hopefully someone else can help.

Allen,
I'm not after a sum because there will be no entry for "available time". I

[quoted text clipped - 40 lines]

 

[Guest]

Steven,

Let me see if I understand your situaiton here...
You have workers using 1 (or more) machine(s).
They enter time/usage/output data into a table like the structure you posted
here.

If Joe works an 8 hour shift on "Machine 1", and then Frank works 3 more
hours on "Machine 1" and nobody works on "Machine 2" on that day, you want to
see, what data records, exactly, in that table?

Today, EmplID, Machine#, Goal, Good, Available Hrs., ...
1/1/06, Joe, Machine 1, 80, 72, 8
1/1/06, Frank, Machine 1, 30, 27, 3
1/1/06, , Machine 1, 0, 0, 13
1/1/06, , Machine 2, 0, 0, 24

....? or what?
(and if this is what you want, the best way I can think of to do this is to
run a small VBA update routine on the table at the end of each day that will
total the hours per machine for existing data entered earlier that day and
append the "blank" records to force the 24-hour available hours totals per
machine.)

BTW, Allen is right - using a formula in a query something along the lines
of <AvailHrs: 24 - sum([Available Hrs])> to calculate your "Machine Available
Hours" makes much more sense that trying to force 24-hours worth of "machine
available" data into a table like this...

....just my $0.02

Allen,
I'm not after a sum because there will be no entry for "available time". I
wish to have "available time" automatically entered, for each machine, each
day and the total hours need to be 24 regardless of how many entries are made
for the day. My table is similar to this.

Todays date
Employee ID
Machine #
Goal
Good Pieces
Available hours
etc.

The available hours was initially going to be entered manually. I want to
auto update this because if no entry is made for the machine for the day the
available time is still 24 hours.I am using this to calculate machine
uptime/efficiencies etc.

Steven

So you want to know if the sum of all entries for a machine on a date comes
to 24 hours or not.

What fields do you have in your table? Presumably MachineID as a foreign
key. Do you have a Start Time and Duration? A StartTime and EndTime? Is the
date date in these fields as well, or is it a separate Date/Time field?

Do you have shifts that roll past midnight (i.e. part of the shift must
count for one day, and the rest of another)?

The specifics on how to query this and get the sum of hours will depend on
how the data structure is set up.

Allen,
Not exactly. The time is of no importance, that is we do not enter data

[quoted text clipped - 23 lines]

autofill
on the days in the holiday column.

 

[Steven P via AccessMonster.com]

Mark and Allen,
How can I sum hours that aren't there. I don't mean to be a pain but the
point of interest is still not shared between us. I DO NOT intend on
inputting any hours for available time. I need to update the records to have
24 hours in this column. If I can do this with VBA code thats great, can you
tell me how? The 8 hours and 3 hours you have in your example would require
input-there is none. The example for machine 2 is correct in that no operator
ran the machine but the availability was 24. Machine one shows the difference
in 24 and 11, all I need is it to show 24 total for both entries (inclusive).

Thanks again,
Steven

Steven,

Let me see if I understand your situaiton here...
You have workers using 1 (or more) machine(s).
They enter time/usage/output data into a table like the structure you posted
here.

If Joe works an 8 hour shift on "Machine 1", and then Frank works 3 more
hours on "Machine 1" and nobody works on "Machine 2" on that day, you want to
see, what data records, exactly, in that table?

Today, EmplID, Machine#, Goal, Good, Available Hrs., ...
1/1/06, Joe, Machine 1, 80, 72, 8
1/1/06, Frank, Machine 1, 30, 27, 3
1/1/06, , Machine 1, 0, 0, 13
1/1/06, , Machine 2, 0, 0, 24

...? or what?
(and if this is what you want, the best way I can think of to do this is to
run a small VBA update routine on the table at the end of each day that will
total the hours per machine for existing data entered earlier that day and
append the "blank" records to force the 24-hour available hours totals per
machine.)

BTW, Allen is right - using a formula in a query something along the lines
of <AvailHrs: 24 - sum([Available Hrs])> to calculate your "Machine Available
Hours" makes much more sense that trying to force 24-hours worth of "machine
available" data into a table like this...

...just my $0.02

Allen,
I'm not after a sum because there will be no entry for "available time". I

[quoted text clipped - 35 lines]

 

[Guest]

Steven,

I'm still not quite following you.
Can you post a revised version of my sample that illustrates what the data
you're looking for looks like? It seems to my you are saying something
self-contrdictory, so plase give me a precise data example along the lines I
gave you.

Mark and Allen,
How can I sum hours that aren't there. I don't mean to be a pain but the
point of interest is still not shared between us. I DO NOT intend on
inputting any hours for available time. I need to update the records to have
24 hours in this column. If I can do this with VBA code thats great, can you
tell me how? The 8 hours and 3 hours you have in your example would require
input-there is none. The example for machine 2 is correct in that no operator
ran the machine but the availability was 24. Machine one shows the difference
in 24 and 11, all I need is it to show 24 total for both entries (inclusive).

Thanks again,
Steven

Steven,

Let me see if I understand your situaiton here...
You have workers using 1 (or more) machine(s).
They enter time/usage/output data into a table like the structure you posted
here.

If Joe works an 8 hour shift on "Machine 1", and then Frank works 3 more
hours on "Machine 1" and nobody works on "Machine 2" on that day, you want to
see, what data records, exactly, in that table?

Today, EmplID, Machine#, Goal, Good, Available Hrs., ...
1/1/06, Joe, Machine 1, 80, 72, 8
1/1/06, Frank, Machine 1, 30, 27, 3
1/1/06, , Machine 1, 0, 0, 13
1/1/06, , Machine 2, 0, 0, 24

...? or what?
(and if this is what you want, the best way I can think of to do this is to
run a small VBA update routine on the table at the end of each day that will
total the hours per machine for existing data entered earlier that day and
append the "blank" records to force the 24-hour available hours totals per
machine.)

BTW, Allen is right - using a formula in a query something along the lines
of <AvailHrs: 24 - sum([Available Hrs])> to calculate your "Machine Available
Hours" makes much more sense that trying to force 24-hours worth of "machine
available" data into a table like this...

...just my $0.02

Allen,
I'm not after a sum because there will be no entry for "available time". I

[quoted text clipped - 35 lines]

autofill
on the days in the holiday column.

 

[Steven P via AccessMonster.com]

Mark,
Here is an example of my table, everything is operator entry except for
available hours.

Date Machine# Goal Good Pieces Available Hours
2/12/06 APK#3 26 212 24
2/12/06 APK#3 14 188 24
2/12/06 APK#7 30 355 24

The problem here of course is the sum for machine APK#3 would be 48 hours. So,
how can I have a total of 24 for each machine and have it as a default or
autoupdate? Am I attempting something that is unusual or not suggested, if so
I don't want to create a monster here.

Steve

Steven,

I'm still not quite following you.
Can you post a revised version of my sample that illustrates what the data
you're looking for looks like? It seems to my you are saying something
self-contrdictory, so plase give me a precise data example along the lines I
gave you.

Mark and Allen,
How can I sum hours that aren't there. I don't mean to be a pain but the

[quoted text clipped - 45 lines]

 

[Guest]

Steven,

Well, then why are you trying to sum up AvailableHours at all?
It is just always 24 hours. If you must pull it into a report or query, use
a grouping operator other than Sum, like, say First, or Max, or Last, or Min.
All of these would produce the results I think you're after.
Since this field *always* shows 24 hours available, it arguably doesn't even
belong in the table as a data item (data, kind of by definition, can vary
....but for your database, unless the # of hours in a day changes
substantially sometime, the 24 hours available in a day will never change, so
why even have it in there since there are several other ways to show that
value in your reports...).

Mark,
Here is an example of my table, everything is operator entry except for
available hours.

Date Machine# Goal Good Pieces Available Hours
2/12/06 APK#3 26 212 24
2/12/06 APK#3 14 188 24
2/12/06 APK#7 30 355 24

The problem here of course is the sum for machine APK#3 would be 48 hours. So,
how can I have a total of 24 for each machine and have it as a default or
autoupdate? Am I attempting something that is unusual or not suggested, if so
I don't want to create a monster here.

Steve

Steven,

I'm still not quite following you.
Can you post a revised version of my sample that illustrates what the data
you're looking for looks like? It seems to my you are saying something
self-contrdictory, so plase give me a precise data example along the lines I
gave you.

Mark and Allen,
How can I sum hours that aren't there. I don't mean to be a pain but the

[quoted text clipped - 45 lines]

autofill
on the days in the holiday column.