Update query????

E

Ed Coleman

Access 2000
W2K

I have a DB that has approx 20 fields with over 6000 records. I have added
a new field called "Shift", which is based on the time of day an event
happened.

All new records added to the DB are being entered with the "Shift" field
completed. The problem is, there are over 6000 records that do not have a
value entered into the "Shift" field.

The field to update is either a 1, 2, or 3.. The field used to base the
update on is Date/Time in the form "10/13/2004 12:39:51 AM."

The "Shift" field will be updated accordingly:

"Date" field is between 11:30:00 PM and 7:29:59 AM then "Shift" = 1
"Date" field is between 7:30:00 AM and 3:29:59 PM then "Shift" = 2
"Date" field is between 3:30:00 PM and 11:29:59 PM then "Shift" = 3.

The "problem" is getting an update query to find all dates with only the
timeframe wanted.

Is this the way to go or should I try some other way???? Any ideas????

TIA
ed
 
G

Gary Walter

Hi Ed,

I believe you are looking for the
function "TimeValue" to "strip out"
only the time from your Date/Time
field.

Dte=#10/13/2004 12:39:51 AM#
?TimeValue(Dte)
12:39:51 AM

Behind the scenes (so to speak), you
really would be comparing times for
all values with a date of 12/30/1899.

?Format(TimeValue(Dte),"mm/dd/yyyy hh:nn:ss AM/PM")
12/30/1899 12:39:51 AM

?Format(#12:39:51 AM#,"mm/dd/yyyy hh:nn:ss AM/PM")
12/30/1899 12:39:51 AM

If I understand you correctly, you are only
going to do this once?

If so, I would start a new query with your table
in the query designer.

Drag and drop the "Shift" field down to a Field
row in a column of the grid.

In the Criteria row of this column, type

IS NULL

Drag and drop the "YourDateTime" field down to a Field
row in a column of the grid.

In the Criteria row of this column, type

IS NOT NULL

(TimeValue() does not like Null)

In Field row of another column in the grid, type

TimeOnly: TimeValue([YourDateTime])

(replacing "YourDateTime" with true name of field)

--------------
Shift 3
--------------

In the Criteria row of this column, type
= #3:30:00 PM# AND < #11:30:00 PM#
Click to expand...

Run the query to verify you are getting Shift 3
records.

In top menu, Select "Query" and choose "Update Query"

In "Update To:" row under "Shift", type in 3.

Click on red exclamation to update records for
shift 3 that previously had no value for Shift.

--------------
Shift 2
--------------

In the Criteria row of "TimeOnly" column, type
= #7:30:00 AM # AND < #3:30:00 PM #
Click to expand...

Run the query to verify you are getting Shift 2
records.

In "Update To:" row under "Shift", type in 2.

Click on red exclamation to update records for
shift 2 that previously had no value for Shift.

--------------
Shift 1a
--------------

In the Criteria row of "TimeOnly" column, type
= #11:30:00 PM #
Click to expand...

Run the query to verify you are getting Shift 1
records.

In "Update To:" row under "Shift", type in 1.

Click on red exclamation to update records for
shift 1 that previously had no value for Shift.

--------------
Shift 1b
--------------

In the Criteria row of "TimeOnly" column, type
= #00:00:00 AM# AND < #7:30:00 AM #
Click to expand...

Run the query to verify you are getting Shift 1
records.

In "Update To:" row under "Shift", type in 1.

Click on red exclamation to update records for
shift 1 that previously had no value for Shift.

Please respond back if I have misunderstood.

Good luck,

Gary Walter
 
K

kathy

Could you share with me how you've accomplished this? I
am trying to obtain counts of various fields by shifts and
am stuck.
Did you do this in sql?
Thanks - Kathy
 
E

Ed Coleman

Gary,

This is exactly what I needed. I guess if it wasn't so late when I was
trying to get this to work, I may have found the TimeValue() function. I
knew what I wanted to do, but I do not know much about the Date/Time
functions.

Thanks for all the help,

ed



Gary Walter said:
Hi Ed,

I believe you are looking for the
function "TimeValue" to "strip out"
only the time from your Date/Time
field.

Dte=#10/13/2004 12:39:51 AM#
?TimeValue(Dte)
12:39:51 AM

Behind the scenes (so to speak), you
really would be comparing times for
all values with a date of 12/30/1899.

?Format(TimeValue(Dte),"mm/dd/yyyy hh:nn:ss AM/PM")
12/30/1899 12:39:51 AM

?Format(#12:39:51 AM#,"mm/dd/yyyy hh:nn:ss AM/PM")
12/30/1899 12:39:51 AM

If I understand you correctly, you are only
going to do this once?

If so, I would start a new query with your table
in the query designer.

Drag and drop the "Shift" field down to a Field
row in a column of the grid.

In the Criteria row of this column, type

IS NULL

Drag and drop the "YourDateTime" field down to a Field
row in a column of the grid.

In the Criteria row of this column, type

IS NOT NULL

(TimeValue() does not like Null)

In Field row of another column in the grid, type

TimeOnly: TimeValue([YourDateTime])

(replacing "YourDateTime" with true name of field)

--------------
Shift 3
--------------

In the Criteria row of this column, type
= #3:30:00 PM# AND < #11:30:00 PM#
Click to expand...

Run the query to verify you are getting Shift 3
records.

In top menu, Select "Query" and choose "Update Query"

In "Update To:" row under "Shift", type in 3.

Click on red exclamation to update records for
shift 3 that previously had no value for Shift.

--------------
Shift 2
--------------

In the Criteria row of "TimeOnly" column, type
= #7:30:00 AM # AND < #3:30:00 PM #
Click to expand...

Run the query to verify you are getting Shift 2
records.

In "Update To:" row under "Shift", type in 2.

Click on red exclamation to update records for
shift 2 that previously had no value for Shift.

--------------
Shift 1a
--------------

In the Criteria row of "TimeOnly" column, type
= #11:30:00 PM #
Click to expand...

Run the query to verify you are getting Shift 1
records.

In "Update To:" row under "Shift", type in 1.

Click on red exclamation to update records for
shift 1 that previously had no value for Shift.

--------------
Shift 1b
--------------

In the Criteria row of "TimeOnly" column, type
= #00:00:00 AM# AND < #7:30:00 AM #
Click to expand...

Run the query to verify you are getting Shift 1
records.

In "Update To:" row under "Shift", type in 1.

Click on red exclamation to update records for
shift 1 that previously had no value for Shift.

Please respond back if I have misunderstood.

Good luck,

Gary Walter

Ed Coleman said:
Access 2000
W2K

I have a DB that has approx 20 fields with over 6000 records. I have added
a new field called "Shift", which is based on the time of day an event
happened.

All new records added to the DB are being entered with the "Shift" field
completed. The problem is, there are over 6000 records that do not have a
value entered into the "Shift" field.

The field to update is either a 1, 2, or 3.. The field used to base the
update on is Date/Time in the form "10/13/2004 12:39:51 AM."

The "Shift" field will be updated accordingly:

"Date" field is between 11:30:00 PM and 7:29:59 AM then "Shift" = 1
"Date" field is between 7:30:00 AM and 3:29:59 PM then "Shift" = 2
"Date" field is between 3:30:00 PM and 11:29:59 PM then "Shift" = 3.

The "problem" is getting an update query to find all dates with only the
timeframe wanted.

Is this the way to go or should I try some other way???? Any ideas????

TIA
ed
Click to expand...
Click to expand...
 
G

Gary Walter

Hi Kathy,

"counts of various fields" can open a whole
can of worms...especially without providing
example data and results that you want...

But, maybe a simple example will help:

simple table "ShiftData" w/ 3 fields:

ID f1 Dte
1 A 10/14/2004
2 B 10/14/2004 7:29:59 AM
3 C 10/14/2004 7:30:00 AM
4 D 10/14/2004 11:29:59 AM
5 A 10/14/2004 11:30:00 AM
6 B 10/14/2004 12:00:00 PM
7 C 10/14/2004 2:24:00 PM
8 D 10/14/2004 3:29:59 PM
9 A 10/14/2004 3:30:00 PM
10 B 10/14/2004 7:30:00 PM
11 C 10/14/2004 11:59:59 PM

One straight-forward method is to create a range table
for your shifts ("ShiftRange"):

ID ShiftMin ShiftMax Shift
1 11:30:00 PM 11:59:59 PM 1
2 12:00:00 AM 7:29:59 AM 1
3 7:30:00 AM 3:29:59 PM 2
4 3:30:00 PM 11:29:59 PM 3

Assuming, like our example, that all "Dte" are
not Null, the following select query might show
where we are headed:

SELECT
SD.ID,
SD.f1,
SD.Dte,
TimeValue([Dte]) AS TimeOnly,
SR.Shift
FROM ShiftData AS SD, ShiftRange AS SR
WHERE
(((TimeValue([Dte]))
Between [SR].[ShiftMin]
And [SR].[ShiftMax]));

producing from our example data:

ID f1 Dte TimeOnly Shift
1 A 10/14/2004 12:00:00 AM 1
2 B 10/14/2004 7:29:59 AM 7:29:59 AM 1
3 C 10/14/2004 7:30:00 AM 7:30:00 AM 2
4 D 10/14/2004 11:29:59 AM 11:29:59 AM 2
5 A 10/14/2004 11:30:00 AM 11:30:00 AM 2
6 B 10/14/2004 12:00:00 PM 12:00:00 PM 2
7 C 10/14/2004 2:24:00 PM 2:24:00 PM 2
8 D 10/14/2004 3:29:59 PM 3:29:59 PM 2
9 A 10/14/2004 3:30:00 PM 3:30:00 PM 3
10 B 10/14/2004 7:30:00 PM 7:30:00 PM 3
11 C 10/14/2004 11:59:59 PM 11:59:59 PM 1

Each record has been associated with a specific shift.

For one "type of count" ("count of fields" can mean
so many different things), we might change this query
to a crosstab to count f1's in each shift:

TRANSFORM Nz(Count(SD.f1),0) AS CountOff1
SELECT SD.f1
FROM ShiftData AS SD, ShiftRange AS SR
WHERE
(((TimeValue([Dte]))
Between [SR].[ShiftMin]
And [SR].[ShiftMax]))
GROUP BY SD.f1
PIVOT "SHIFT" & SR.Shift & "Count";

producing from our example data:

f1 SHIFT1Count SHIFT2Count SHIFT3Count
A 1 1 1
B 1 1 1
C 1 2 0
D 0 2 0

Does that help?

If there is a chance that your DateTime field
("Dte" in our example) could be Null, the function
TimeValue() will choke on it.

One workaround might be to filter out records
where Dte is null:

TRANSFORM Nz(Count(SD.f1),0) AS CountOff1
SELECT SD.f1
FROM ShiftData AS SD, ShiftRange AS SR
WHERE
((((TimeValue([Dte]))
Between [SR].[ShiftMin]
And [SR].[ShiftMax]))
AND ((SD.Dte) Is Not Null))
GROUP BY SD.f1
PIVOT "SHIFT" & [SR].[Shift] & "Count";

Maybe that is something you will not have to worry about?


Good luck,

Gary Walter
 
G

Gary Walter

Hi Kathy,

One option if you might have a "Dte"
that was Null would be to change
type of "Shift" in table "ShiftRange"
to Text. Then add one more record.

ID ShiftMin ShiftMax Shift
1 11:30:00 PM 11:59:59 PM 1
2 12:00:00 AM 7:29:59 AM 1
3 7:30:00 AM 3:29:59 PM 2
4 3:30:00 PM 11:29:59 PM 3
5 Null

where I have spelled out the word
"Null" for Shift in the final record.
Then, if your ShiftData looked like:

ID f1 Dte
1 A 10/14/2004
2 B 10/14/2004 7:29:59 AM
3 C 10/14/2004 7:30:00 AM
4 D 10/14/2004 11:29:59 AM
5 A 10/14/2004 11:30:00 AM
6 B 10/14/2004 12:00:00 PM
7 C 10/14/2004 2:24:00 PM
8 D 10/14/2004 3:29:59 PM
9 A 10/14/2004 3:30:00 PM
10 B 10/14/2004 7:30:00 PM
11 C 10/14/2004 11:59:59 PM
12 D

then, the following xtab

TRANSFORM Nz(Count(SD.f1),0) AS CountOff1
SELECT SD.f1
FROM ShiftData AS SD, ShiftRange AS SR
WHERE
(((IIf([Dte] Is Null,SR.Shift="Null",
TimeValue([Dte]) Between [SR].[ShiftMin] And [SR].[ShiftMax]))<>False))
GROUP BY SD.f1
PIVOT "SHIFT" & [SR].[Shift] & "Count";

would give you a "NullDteCount" also:

f1 SHIFT1Count SHIFT2Count SHIFT3Count SHIFTNullCount
A 1 1 1 0
B 1 1 1 0
C 1 2 0 0
D 0 2 0 1
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Return records entered today 14
Time - Show records 1
Changing constants in a query to user input values 2
5 minute time increments 2
Case Statement with the Between Date function 3
Iif statement and time format 2
query comes up blank 1
working with time date in a query 4

Top