Update Query
in [Queries]
|
Prev: Unable to use filter in datasheet view or add criteria to query in design view
Next: Select to reference an other record or row
From: Iram on 3 Mar 2010 19:53 Hello, For every record in a table I need to update one of four Yes/No check box fields based upon date span. If the "In Date" is in the same month as "Serve D" or "Return D" field place a check in the "DispodWithinSameMonth" yes/no field. If the "In Date" is within 30 days of the "Serve D" or "Return D" field place a check in the "DispodWithin30Days" yes/no field. If the "In Date" is within 60 days of the "Serve D" or "Return D" field place a check in the "DispodWithin60Days" yes/no field. If the "In Date" is within 90 days of the "Serve D" or "Return D" field place a check in the "DispodWithin90Days" yes/no field. There are seven fields total In Date Return D Serve D DispodWithinSameMonth DispodWithin30Days DispodWithin60Days DispodWithin90Days Could you help me understand how I would write the update query? I am not that great with Update Queries and any detail will be greatly appreciated! Thanks. Iram/mcp
From: John W. Vinson on 3 Mar 2010 20:55 On Wed, 3 Mar 2010 16:53:01 -0800, Iram <Iram(a)discussions.microsoft.com> wrote: >Hello, > >For every record in a table I need to update one of four Yes/No check box >fields based upon date span. > >If the "In Date" is in the same month as "Serve D" or "Return D" field place >a check in the "DispodWithinSameMonth" yes/no field. > >If the "In Date" is within 30 days of the "Serve D" or "Return D" field >place a check in the "DispodWithin30Days" yes/no field. > >If the "In Date" is within 60 days of the "Serve D" or "Return D" field >place a check in the "DispodWithin60Days" yes/no field. > >If the "In Date" is within 90 days of the "Serve D" or "Return D" field >place a check in the "DispodWithin90Days" yes/no field. > >There are seven fields total >In Date >Return D >Serve D >DispodWithinSameMonth >DispodWithin30Days >DispodWithin60Days >DispodWithin90Days I think you're misguided here. If you store a Yes or a No in one of these yes/no fields in 100 records in your table... you can be ABSOLUTELY CERTAIN that every one of those records will contain *incorrect* data within 30 days. The Dispod fields *should simply not exist* in your table. These yes/no fields should instead be calculated on the fly, dynamically, from the date, and NOT stored in your table. You can do so with calculated fields using the IIF() and DateDiff() functions. There are anomalies in your rules though! If Serve D is 1/31/2010 and Return D is 2/1/2010 they're just a day apart... but not within the same month. Not all months are thirty days. Some months are 31 days, so it could be within the same month but not within 30 days. Is that OK? Just an example of how to do this: DispodWithin30Days: (DateDiff("d", [In Date], [Serve D]) <= 30 OR DateDiff("d", [In Date], [Return Date]) <= 30) This expression will be TRUE if either of the date ranges is 30 or less, FALSE if neither expression is true. This will fail if any of the three date fields is NULL, you'll need to use the NZ function to convert nulls to a reasonable date. Could you explain the real-life situation you're modeling? -- John W. Vinson [MVP]
From: Iram on 4 Mar 2010 13:24 Hi John, Btw, thanks for your time! This database is used to track a Process Servers Job. The In Date is the date that he receives the papers to serve. He either serves the papers and records the "Serve D" or he returns the papers back to his boss and records the "Returned D". His boss wants to be able to run a report that showed which cases were served or returned within the same month, within 30 days, 60 days or 90 days, (new one) greater than 90 days. As for saving the calculated data into the table, you are right. I'll just run the calculated data off a form or report from a query. As for your question, Yes that is ok! Your Awesome John! Iram/mcp "John W. Vinson" wrote: > On Wed, 3 Mar 2010 16:53:01 -0800, Iram <Iram(a)discussions.microsoft.com> > wrote: > > >Hello, > > > >For every record in a table I need to update one of four Yes/No check box > >fields based upon date span. > > > >If the "In Date" is in the same month as "Serve D" or "Return D" field place > >a check in the "DispodWithinSameMonth" yes/no field. > > > >If the "In Date" is within 30 days of the "Serve D" or "Return D" field > >place a check in the "DispodWithin30Days" yes/no field. > > > >If the "In Date" is within 60 days of the "Serve D" or "Return D" field > >place a check in the "DispodWithin60Days" yes/no field. > > > >If the "In Date" is within 90 days of the "Serve D" or "Return D" field > >place a check in the "DispodWithin90Days" yes/no field. > > > >There are seven fields total > >In Date > >Return D > >Serve D > >DispodWithinSameMonth > >DispodWithin30Days > >DispodWithin60Days > >DispodWithin90Days > > I think you're misguided here. > > If you store a Yes or a No in one of these yes/no fields in 100 records in > your table... you can be ABSOLUTELY CERTAIN that every one of those records > will contain *incorrect* data within 30 days. The Dispod fields *should simply > not exist* in your table. > > These yes/no fields should instead be calculated on the fly, dynamically, from > the date, and NOT stored in your table. You can do so with calculated fields > using the IIF() and DateDiff() functions. > > There are anomalies in your rules though! If Serve D is 1/31/2010 and Return D > is 2/1/2010 they're just a day apart... but not within the same month. Not all > months are thirty days. Some months are 31 days, so it could be within the > same month but not within 30 days. Is that OK? > > Just an example of how to do this: > > DispodWithin30Days: (DateDiff("d", [In Date], [Serve D]) <= 30 > OR DateDiff("d", [In Date], [Return Date]) <= 30) > > This expression will be TRUE if either of the date ranges is 30 or less, FALSE > if neither expression is true. > > This will fail if any of the three date fields is NULL, you'll need to use the > NZ function to convert nulls to a reasonable date. > > Could you explain the real-life situation you're modeling? > > > -- > > John W. Vinson [MVP] > . >
From: John W. Vinson on 4 Mar 2010 13:42 On Thu, 4 Mar 2010 10:24:02 -0800, Iram <Iram(a)discussions.microsoft.com> wrote: >Hi John, >Btw, thanks for your time! > >This database is used to track a Process Servers Job. The In Date is the >date that he receives the papers to serve. He either serves the papers and >records the "Serve D" or he returns the papers back to his boss and records >the "Returned D". His boss wants to be able to run a report that showed which >cases were served or returned within the same month, within 30 days, 60 days >or 90 days, (new one) greater than 90 days. If only one of Serve D and Returned D will be NULL you might be able to use an expression like DispodWithin30Days: (DateDiff("d", [In Date], NZ([Serve D], [Returned D])) <= 30 You'll still want some sort of criterion to find those cases where the paper has neither been served nor returned: it's not clear what you want to do in that case. -- John W. Vinson [MVP]
From: Iram on 5 Mar 2010 11:18
For cases that don't have neither a Served or Returned date can we create a calculated field that counts them as well? "John W. Vinson" wrote: > On Thu, 4 Mar 2010 10:24:02 -0800, Iram <Iram(a)discussions.microsoft.com> > wrote: > > >Hi John, > >Btw, thanks for your time! > > > >This database is used to track a Process Servers Job. The In Date is the > >date that he receives the papers to serve. He either serves the papers and > >records the "Serve D" or he returns the papers back to his boss and records > >the "Returned D". His boss wants to be able to run a report that showed which > >cases were served or returned within the same month, within 30 days, 60 days > >or 90 days, (new one) greater than 90 days. > > If only one of Serve D and Returned D will be NULL you might be able to use an > expression like > > DispodWithin30Days: (DateDiff("d", [In Date], NZ([Serve D], [Returned D])) <= > 30 > > You'll still want some sort of criterion to find those cases where the paper > has neither been served nor returned: it's not clear what you want to do in > that case. > -- > > John W. Vinson [MVP] > . > |