Use a Qry as a filter in a form
in [Form]
|
From: daisy on 26 Mar 2010 11:35 First I want to tell everyone thank you for all of your help on these boards. It is a life saver. Someone mentioned in an earlier post that I could create a query to use as a filter in a form? I'm not sure how to do this? Here's what's happening: There is a data table that has new monthly data appended to it. The newly appended data could have history associated with it for example EffQtr Event CustomerID CustomerName InvoicedAmt 2008-1 New 123456 Customer A $25 2009-1 Renewal 123456 Customer A $50 New Data that gets appended EffQtr Event CustomerID CustomerName InvoicedAmt 2010-1 123456 Customer A $25 I would like to create a form that would have a filter to choose the Customers that have newly appended data and any of their historical data yo update the event and a few other fields. We have to be able to see the historical at the same time to be able to update the fields. -- Thank you in advance!!!!
From: John W. Vinson on 26 Mar 2010 15:12 On Fri, 26 Mar 2010 08:35:02 -0700, daisy <daisy(a)discussions.microsoft.com> wrote: >First I want to tell everyone thank you for all of your help on these boards. >It is a life saver. Someone mentioned in an earlier post that I could create >a query to use as a filter in a form? I'm not sure how to do this? Here's >what's happening: > >There is a data table that has new monthly data >appended to it. The newly appended data could have history associated with it > >for example >EffQtr Event CustomerID CustomerName InvoicedAmt >2008-1 New 123456 Customer A $25 >2009-1 Renewal 123456 Customer A $50 >New Data that gets appended >EffQtr Event CustomerID CustomerName InvoicedAmt >2010-1 123456 Customer A $25 > >I would like to create a form that would have a filter to choose the >Customers that have newly appended data and any of their historical data yo >update the event and a few other fields. We have to be able to see the >historical at the same time to be able to update the fields. It's not so much using a "query as a filter" as "basing the form on a query". Are these two different tables? or are you importing directly into the table? What exactly do you want to do: append the new record? Alter existing records? Allow the user to see the old and the new data in conjunction so they can manually edit it? or what? -- John W. Vinson [MVP]
From: daisy on 26 Mar 2010 16:28 I'd like to append the data into one data table and then be able to make edits based on all of the data associated with that particular customer. So I need the query to be able to pull history data with the new data but only for those customers coming in that month -- Thank you in advance!!!! "John W. Vinson" wrote: > On Fri, 26 Mar 2010 08:35:02 -0700, daisy <daisy(a)discussions.microsoft.com> > wrote: > > >First I want to tell everyone thank you for all of your help on these boards. > >It is a life saver. Someone mentioned in an earlier post that I could create > >a query to use as a filter in a form? I'm not sure how to do this? Here's > >what's happening: > > > >There is a data table that has new monthly data > >appended to it. The newly appended data could have history associated with it > > > >for example > >EffQtr Event CustomerID CustomerName InvoicedAmt > >2008-1 New 123456 Customer A $25 > >2009-1 Renewal 123456 Customer A $50 > >New Data that gets appended > >EffQtr Event CustomerID CustomerName InvoicedAmt > >2010-1 123456 Customer A $25 > > > >I would like to create a form that would have a filter to choose the > >Customers that have newly appended data and any of their historical data yo > >update the event and a few other fields. We have to be able to see the > >historical at the same time to be able to update the fields. > > It's not so much using a "query as a filter" as "basing the form on a query". > Are these two different tables? or are you importing directly into the table? > What exactly do you want to do: append the new record? Alter existing records? > Allow the user to see the old and the new data in conjunction so they can > manually edit it? or what? > -- > > John W. Vinson [MVP] > . >
From: John W. Vinson on 26 Mar 2010 18:44 On Fri, 26 Mar 2010 13:28:01 -0700, daisy <daisy(a)discussions.microsoft.com> wrote: >I'd like to append the data into one data table and then be able to make >edits based on all of the data associated with that particular customer. So I >need the query to be able to pull history data with the new data but only for >those customers coming in that month Then you'll need to record somewhere in the table when the customers "come". I don't see anything in your data that would distinguish new records from old ones, and Access certainly doesn't keep track. You could include a DateAdded field, default value Date() (or if you want to record the exact time, Now()), and run an Append query appending to the other fields from your external source. You could then use this field with a criterion of >= DateSerial(Year(Date()), Month(Date()), 1) to find all data added this month; you could even have a form based on this query, with a subform (linked on customerID) based on a query selecting data older than that. -- John W. Vinson [MVP]
From: daisy on 30 Mar 2010 17:58 thank you how would the form pull in data associated with it that is older than the current data? -- Thank you in advance!!!! "John W. Vinson" wrote: > On Fri, 26 Mar 2010 13:28:01 -0700, daisy <daisy(a)discussions.microsoft.com> > wrote: > > >I'd like to append the data into one data table and then be able to make > >edits based on all of the data associated with that particular customer. So I > >need the query to be able to pull history data with the new data but only for > >those customers coming in that month > > Then you'll need to record somewhere in the table when the customers "come". I > don't see anything in your data that would distinguish new records from old > ones, and Access certainly doesn't keep track. > > You could include a DateAdded field, default value Date() (or if you want to > record the exact time, Now()), and run an Append query appending to the other > fields from your external source. > > You could then use this field with a criterion of > > >= DateSerial(Year(Date()), Month(Date()), 1) > > to find all data added this month; you could even have a form based on this > query, with a subform (linked on customerID) based on a query selecting data > older than that. > -- > > John W. Vinson [MVP] > . >
|
Next
|
Last
Pages: 1 2 Prev: Input form Next: How do I use dlookup in a form to find a specific field |