A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Use a Qry as a filter in a form



 
 
Thread Tools Display Modes
  #1  
Old March 26th, 2010, 04:35 PM posted to microsoft.public.access.forms
daisy
external usenet poster
 
Posts: 94
Default Use a Qry as a filter in a form

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!!!!
  #2  
Old March 26th, 2010, 08:12 PM posted to microsoft.public.access.forms
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Use a Qry as a filter in a form

On Fri, 26 Mar 2010 08:35:02 -0700, daisy
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]
  #3  
Old March 26th, 2010, 09:28 PM posted to microsoft.public.access.forms
daisy
external usenet poster
 
Posts: 94
Default Use a Qry as a filter in a form

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
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]
.

  #4  
Old March 26th, 2010, 11:44 PM posted to microsoft.public.access.forms
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Use a Qry as a filter in a form

On Fri, 26 Mar 2010 13:28:01 -0700, daisy
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]
  #5  
Old March 30th, 2010, 10:58 PM posted to microsoft.public.access.forms
daisy
external usenet poster
 
Posts: 94
Default Use a Qry as a filter in a form

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
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]
.

  #6  
Old March 31st, 2010, 12:12 AM posted to microsoft.public.access.forms
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Use a Qry as a filter in a form

On Tue, 30 Mar 2010 14:58:01 -0700, daisy
wrote:

thank you how would the form pull in data associated with it that is older
than the current data?


By using appropriate criteria. Since I know nothing about your "current data",
nor the structure of your table, nor exactly what you're looking for it's a
bit hard to give a specific answer! For data with a datefield prior to today's
date, you can use

Date()

on the criteria line.
--

John W. Vinson [MVP]
  #7  
Old March 31st, 2010, 04:09 PM posted to microsoft.public.access.forms
daisy
external usenet poster
 
Posts: 94
Default Use a Qry as a filter in a form

Hi John, Maybe this will help. I'm not sure how to build this form with
criteria before the month I've just added?

Here's my table structure
EffQtr
EffYear
OrigDt
Region
CustomerID
CustomerName
DealStDt
DealEndDt
Event
InvoiceAmt
ListAmt
LocalAmt
AnnualFees
AddedDt
--
Thank you in advance!!!!


"John W. Vinson" wrote:

On Tue, 30 Mar 2010 14:58:01 -0700, daisy
wrote:

thank you how would the form pull in data associated with it that is older
than the current data?


By using appropriate criteria. Since I know nothing about your "current data",
nor the structure of your table, nor exactly what you're looking for it's a
bit hard to give a specific answer! For data with a datefield prior to today's
date, you can use

Date()

on the criteria line.
--

John W. Vinson [MVP]
.

  #8  
Old April 1st, 2010, 07:48 PM posted to microsoft.public.access.forms
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Use a Qry as a filter in a form

On Wed, 31 Mar 2010 08:09:01 -0700, daisy
wrote:

Hi John, Maybe this will help. I'm not sure how to build this form with
criteria before the month I've just added?

Here's my table structure
EffQtr
EffYear
OrigDt
Region
CustomerID
CustomerName
DealStDt
DealEndDt
Event
InvoiceAmt
ListAmt
LocalAmt
AnnualFees
AddedDt


Looks like you have a lot of date or date-related fields: EffQtr, EffYr,
OrigDt, DealStDt, DealEndDt, AddedDt. Which of these do you want to use as a
criterion - AddedDt? And what do you mean by "build the form with criteria
before the month"???? Example please!
--

John W. Vinson [MVP]
  #9  
Old April 3rd, 2010, 01:53 AM posted to microsoft.public.access.forms
daisy
external usenet poster
 
Posts: 94
Default Use a Qry as a filter in a form

Hi John, thank you for responding. I would like to create a filter for new
records coming in based on AddedDt

and then I would like to somehow link all data associated with the
CustomerID & CustomerName to pull thru as well?

Example: AddedDt = 2010-03
EffQtr CustomerID CustomerName Event InvoiceAmt
AnnualFees
2010-2 124 Customer A Renewal 2 $1200
$1200


Historical data
Example: AddedDt = 2010-03
EffQtr CustomerID CustomerName Event InvoiceAmt
AnnualFees
2008-1 124 Customer A New $1200
$1200
2009-1 124 Customer A Renewal $xxx
$xxxx



--
Thank you in advance!!!!


"John W. Vinson" wrote:

On Wed, 31 Mar 2010 08:09:01 -0700, daisy
wrote:

Hi John, Maybe this will help. I'm not sure how to build this form with
criteria before the month I've just added?

Here's my table structure
EffQtr
EffYear
OrigDt
Region
CustomerID
CustomerName
DealStDt
DealEndDt
Event
InvoiceAmt
ListAmt
LocalAmt
AnnualFees
AddedDt


Looks like you have a lot of date or date-related fields: EffQtr, EffYr,
OrigDt, DealStDt, DealEndDt, AddedDt. Which of these do you want to use as a
criterion - AddedDt? And what do you mean by "build the form with criteria
before the month"???? Example please!
--

John W. Vinson [MVP]
.

  #10  
Old April 3rd, 2010, 03:29 AM posted to microsoft.public.access.forms
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Use a Qry as a filter in a form

On Fri, 2 Apr 2010 17:53:01 -0700, daisy
wrote:

Hi John, thank you for responding. I would like to create a filter for new
records coming in based on AddedDt


Put a criterion on AddedDt of

DateSerial(Year(Date()), Month(Date()), 1)

and it will pull all records with an AddedDt prior to the first day of the
current month.

and then I would like to somehow link all data associated with the
CustomerID & CustomerName to pull thru as well?


Create a Query. Add this table (whatever it is) and the customer table, joined
by CustomerID. Pull the Event, EffQtr, InvoiceAmt and so on from this table,
and the CustomerName and any other desired fields from the Customer table.

Get to know queries. They are *absolutely basic* to any constructive use of
Access, and they're the very basis of relational database design!

--

John W. Vinson [MVP]
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 01:35 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.