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
|
|||
|
|||
Using Start Date and End Date in Query
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 |
#2
|
|||
|
|||
Using Start Date and End Date in Query
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. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "ChuckW" wrote 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 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 |
#3
|
|||
|
|||
Using Start Date and End Date in Query
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. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "ChuckW" wrote 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 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 . |
#4
|
|||
|
|||
Using Start Date and End Date in Query
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. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "ChuckW" wrote in message ... 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. "ChuckW" wrote 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 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 |
#5
|
|||
|
|||
Using Start Date and End Date in Query
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. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "ChuckW" wrote in message ... 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. "ChuckW" wrote 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 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 . |
#6
|
|||
|
|||
Using Start Date and End Date in Query
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. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "ChuckW" wrote in message ... 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. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "ChuckW" wrote in message ... 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. "ChuckW" wrote 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 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 |
Thread Tools | |
Display Modes | |
|
|