Using Start Date and End Date in Query

  • Thread starter Thread starter ChuckW
  • Start date Start date
C

ChuckW

Hi,

I have a form for inputting data which includes text
boxes for the StartDate, EndDate and CustomerName, number
of new customer and number of existing customers. The
form is based on a query called PotentialRevenueQuery
which is based on a table called PotentialRevenue. Sales
reps will input the values for these five things and the
data will be stored in this table PotentialRevenue
table.

What I want to run a query against a transactional table
using the start and end date and CustomerName values.
There is a transactional table with fields that include
CustomerName, TxnDate and Amount. CustomerName will only
appear once in my PotentialRevenue table. Is there a way
to write a query that takes values from my potential
revenue table and then runs a query against the
transactional table. The criteria for the TxnDate would
be "between PotentialRevenue.StartDate and
PotentialRevenue.EndDate". My goal is to ultimately have
a query that contains CustomerName, StartDate, EndDate,
PotentialRevenue, AcutalRevenue and RevenueGap.

Any help would be appreciated.

Thanks,

Chuck
 
In the Criteria row of your query, you can refer to the value in the text
box on the form, e.g.:
[Forms]![MyForm]![StartDate]

Even if you have to stack one query on another (i.e. use one query as a
source "table" for another), that should work.
 
Allen,

Thanks for your help. I think what I want to do is to
run a query in a transaction table based on the start
date and end date of my data input form that will
calculate revenue for a given time period and then store
this information in a table. Right now I have the data
input form storing the values for start date, end date,
customer name, number of new clients and number of
existing clients in a table called PotentialRevenue. Is
it possible when inputting this information for a query
to be kicked off that calculates potential revenue for a
given client based on the start and end dates and then
store this value in the PotentialRevenue table as well?

Thanks,

Chuck
-----Original Message-----
In the Criteria row of your query, you can refer to the value in the text
box on the form, e.g.:
[Forms]![MyForm]![StartDate]

Even if you have to stack one query on another (i.e. use one query as a
source "table" for another), that should work.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a form for inputting data which includes text
boxes for the StartDate, EndDate and CustomerName, number
of new customer and number of existing customers. The
form is based on a query called PotentialRevenueQuery
which is based on a table called PotentialRevenue. Sales
reps will input the values for these five things and the
data will be stored in this table PotentialRevenue
table.

What I want to run a query against a transactional table
using the start and end date and CustomerName values.
There is a transactional table with fields that include
CustomerName, TxnDate and Amount. CustomerName will only
appear once in my PotentialRevenue table. Is there a way
to write a query that takes values from my potential
revenue table and then runs a query against the
transactional table. The criteria for the TxnDate would
be "between PotentialRevenue.StartDate and
PotentialRevenue.EndDate". My goal is to ultimately have
a query that contains CustomerName, StartDate, EndDate,
PotentialRevenue, AcutalRevenue and RevenueGap.

Any help would be appreciated.

Thanks,

Chuck
Click to expand...


.
Click to expand...
 
You could use the AfterUpdate event of the form where the data is entered,
to Execute an Append (or Update) query statement to store the date in the
other table.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

ChuckW said:
Allen,

Thanks for your help. I think what I want to do is to
run a query in a transaction table based on the start
date and end date of my data input form that will
calculate revenue for a given time period and then store
this information in a table. Right now I have the data
input form storing the values for start date, end date,
customer name, number of new clients and number of
existing clients in a table called PotentialRevenue. Is
it possible when inputting this information for a query
to be kicked off that calculates potential revenue for a
given client based on the start and end dates and then
store this value in the PotentialRevenue table as well?

Thanks,

Chuck
-----Original Message-----
In the Criteria row of your query, you can refer to the value in the text
box on the form, e.g.:
[Forms]![MyForm]![StartDate]

Even if you have to stack one query on another (i.e. use one query as a
source "table" for another), that should work.


I have a form for inputting data which includes text
boxes for the StartDate, EndDate and CustomerName, number
of new customer and number of existing customers. The
form is based on a query called PotentialRevenueQuery
which is based on a table called PotentialRevenue. Sales
reps will input the values for these five things and the
data will be stored in this table PotentialRevenue
table.

What I want to run a query against a transactional table
using the start and end date and CustomerName values.
There is a transactional table with fields that include
CustomerName, TxnDate and Amount. CustomerName will only
appear once in my PotentialRevenue table. Is there a way
to write a query that takes values from my potential
revenue table and then runs a query against the
transactional table. The criteria for the TxnDate would
be "between PotentialRevenue.StartDate and
PotentialRevenue.EndDate". My goal is to ultimately have
a query that contains CustomerName, StartDate, EndDate,
PotentialRevenue, AcutalRevenue and RevenueGap.

Any help would be appreciated.

Thanks,

Chuck
Click to expand...
Click to expand...
Click to expand...
 
Allen,

I created text boxes for start and end date, number of
new clients, number of existing clients, patient
frequency and a combo box for customer name. I have an
event of after update set up on patient frequency which
is the last text box on my form which an append query.
The Append query inserts values into a table called
PotentialRevenue. It runs a query against a
transactional table based on customer name and the start
and end dates and then calculates the actual revenue and
places this value is the ActualRevenue field in the
PotentialRevenue table. It also calculates a field
called months which is the number of days/30 and places
this value in a field called months in the same table.

The problem I am now having is that the values for Months
and ActualRevenue and being created in a separate record
than the other values from my form. I want to get
Months, ActualRevenue to appear in the same line or
record as startdate, enddate, customername,
numbernewpatients,numberexistingpatients and
patientfrequency.

Any ideas to what I am doing wrong?

Thanks,

Chuck


-----Original Message-----
You could use the AfterUpdate event of the form where the data is entered,
to Execute an Append (or Update) query statement to store the date in the
other table.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

Thanks for your help. I think what I want to do is to
run a query in a transaction table based on the start
date and end date of my data input form that will
calculate revenue for a given time period and then store
this information in a table. Right now I have the data
input form storing the values for start date, end date,
customer name, number of new clients and number of
existing clients in a table called PotentialRevenue. Is
it possible when inputting this information for a query
to be kicked off that calculates potential revenue for a
given client based on the start and end dates and then
store this value in the PotentialRevenue table as well?

Thanks,

Chuck
-----Original Message-----
In the Criteria row of your query, you can refer to
Click to expand...
the
value in the text
box on the form, e.g.:
[Forms]![MyForm]![StartDate]

Even if you have to stack one query on another (i.e.
Click to expand...
use
one query as a
source "table" for another), that should work.


"ChuckW" <[email protected]> wrote
Click to expand...
in
message
I have a form for inputting data which includes text
boxes for the StartDate, EndDate and CustomerName, number
of new customer and number of existing customers. The
form is based on a query called PotentialRevenueQuery
which is based on a table called PotentialRevenue. Sales
reps will input the values for these five things and the
data will be stored in this table PotentialRevenue
table.

What I want to run a query against a transactional table
using the start and end date and CustomerName values.
There is a transactional table with fields that include
CustomerName, TxnDate and Amount. CustomerName will only
appear once in my PotentialRevenue table. Is there
Click to expand...
a
way
to write a query that takes values from my potential
revenue table and then runs a query against the
transactional table. The criteria for the TxnDate would
be "between PotentialRevenue.StartDate and
PotentialRevenue.EndDate". My goal is to ultimately have
a query that contains CustomerName, StartDate, EndDate,
PotentialRevenue, AcutalRevenue and RevenueGap.

Any help would be appreciated.

Thanks,

Chuck
Click to expand...
Click to expand...


.
Click to expand...
 
Chuck, ActualRevenue is the actual revenue for the customer in the period?
If so, it must not be stored in the PotentialRevenue table. There are just
too many things that can go wrong with that approach. Instead, calculate it
when needed.

Use a query to calculate the actual revenue. If that idea is new, see:
Calculated fields
at:
http://allenbrowne.com/casu-14.html

To get the ActualRevenue, you could use a Totals query: in query design
view, depress the Total icon on the toolbar (upper sigma icon). This adds a
new row to the query design grid. Group By the fields in the
PotenrialRevenue table. Use Where on the date field for the date range. Use
Sum on the Amount field on the transaction table to get the actual revenue.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

ChuckW said:
I created text boxes for start and end date, number of
new clients, number of existing clients, patient
frequency and a combo box for customer name. I have an
event of after update set up on patient frequency which
is the last text box on my form which an append query.
The Append query inserts values into a table called
PotentialRevenue. It runs a query against a
transactional table based on customer name and the start
and end dates and then calculates the actual revenue and
places this value is the ActualRevenue field in the
PotentialRevenue table. It also calculates a field
called months which is the number of days/30 and places
this value in a field called months in the same table.

The problem I am now having is that the values for Months
and ActualRevenue and being created in a separate record
than the other values from my form. I want to get
Months, ActualRevenue to appear in the same line or
record as startdate, enddate, customername,
numbernewpatients,numberexistingpatients and
patientfrequency.

Any ideas to what I am doing wrong?

Thanks,

Chuck


-----Original Message-----
You could use the AfterUpdate event of the form where the data is entered,
to Execute an Append (or Update) query statement to store the date in the
other table.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

Thanks for your help. I think what I want to do is to
run a query in a transaction table based on the start
date and end date of my data input form that will
calculate revenue for a given time period and then store
this information in a table. Right now I have the data
input form storing the values for start date, end date,
customer name, number of new clients and number of
existing clients in a table called PotentialRevenue. Is
it possible when inputting this information for a query
to be kicked off that calculates potential revenue for a
given client based on the start and end dates and then
store this value in the PotentialRevenue table as well?

Thanks,

Chuck
-----Original Message-----
In the Criteria row of your query, you can refer to the
value in the text
box on the form, e.g.:
[Forms]![MyForm]![StartDate]

Even if you have to stack one query on another (i.e. use
one query as a
source "table" for another), that should work.


message

I have a form for inputting data which includes text
boxes for the StartDate, EndDate and CustomerName,
number
of new customer and number of existing customers. The
form is based on a query called PotentialRevenueQuery
which is based on a table called PotentialRevenue.
Sales
reps will input the values for these five things and
the
data will be stored in this table PotentialRevenue
table.

What I want to run a query against a transactional
table
using the start and end date and CustomerName values.
There is a transactional table with fields that include
CustomerName, TxnDate and Amount. CustomerName will
only
appear once in my PotentialRevenue table. Is there a
way
to write a query that takes values from my potential
revenue table and then runs a query against the
transactional table. The criteria for the TxnDate
would
be "between PotentialRevenue.StartDate and
PotentialRevenue.EndDate". My goal is to ultimately
have
a query that contains CustomerName, StartDate, EndDate,
PotentialRevenue, AcutalRevenue and RevenueGap.

Any help would be appreciated.

Thanks,

Chuck
Click to expand...
Click to expand...
Click to expand...
 
Back
Top