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
|
|||
|
|||
Update query????
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 |
#2
|
|||
|
|||
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# 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 # 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 # 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 # 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" wrote: 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 |
#3
|
|||
|
|||
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 -----Original Message----- 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 . |
#4
|
|||
|
|||
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" wrote in message ... 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# 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 # 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 # 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 # 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" wrote: 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 |
#5
|
|||
|
|||
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 "kathy" wrote: 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 -----Original Message----- 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 . |
#6
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Big number gives error! | Sara Mellen | Running & Setting Up Queries | 8 | October 11th, 2004 02:48 AM |
Update table from Result of Query | JoeCL | Running & Setting Up Queries | 5 | October 4th, 2004 12:30 AM |
Update table from Totals query | Brian T. | Running & Setting Up Queries | 1 | September 14th, 2004 12:58 AM |
Update another table with a Max record query | Ngan | Running & Setting Up Queries | 2 | June 22nd, 2004 05:01 PM |
Update Query Problem | Casa | General Discussion | 1 | June 14th, 2004 04:17 PM |