Dates in a Query

  • Thread starter Thread starter labtyda
  • Start date Start date
L

labtyda

I need to use a query where I can change the date. Want to get a percentage
of salespersons collections. I have a pledge date, but want sales through a
certain date, and then collections through today's date. For example sales
through three weeks ago and collections through today. I do have separate
columns for pledge date and date paid. Please, simple, I am not very
proficient in this.

thank you
 
I have a partial query done, it gives me percentages per salesperson
collected, but I need to have my sales date to be about 3 weeks earlier than
the current date to get a more accurate percentage of collections. I also
need to be able to change the date.



Here is what I have for fields: Pledge date, Sale Amount, Paid (yes/no)
Date Paid, Salesperson.

This is what I have so far for my query:
Field: Salesperson
Table: Pledges 2008 Events
Total: Group By

Field: Sale Amount
Table: Pledges 2008 Events
Total: Sum

Field: Collected: Sum(IIf([PAID], [Sale Amount],0))
Total: Expression

Field: Expr1: Sum(IIf([Paid], [Sale Amount],0))/Sum([Sale Amount])
Total: Expression
 
You didn't really answer my question "Do you have some table and field names
as well as sample data and desired output?"

Without know your data very well, If you want to get pledges made during a
particular period (between two dates entered on a form) the column in you
query might look like:
PeriodPledges: Abs(PledgeDate Between Forms!frmDates!txtStart and
Forms!frmDates!txtEnd) * [PledgeAmount]

This column will display the pledge amount if it falls in the date range or
0 if not.
--
Duane Hookom
Microsoft Access MVP


labtyda said:
I have a partial query done, it gives me percentages per salesperson
collected, but I need to have my sales date to be about 3 weeks earlier than
the current date to get a more accurate percentage of collections. I also
need to be able to change the date.



Here is what I have for fields: Pledge date, Sale Amount, Paid (yes/no)
Date Paid, Salesperson.

This is what I have so far for my query:
Field: Salesperson
Table: Pledges 2008 Events
Total: Group By

Field: Sale Amount
Table: Pledges 2008 Events
Total: Sum

Field: Collected: Sum(IIf([PAID], [Sale Amount],0))
Total: Expression

Field: Expr1: Sum(IIf([Paid], [Sale Amount],0))/Sum([Sale Amount])
Total: Expression

Duane Hookom said:
Do you have some table and field names as well as sample data and desired
output?
Click to expand...
Click to expand...
 
My table name is: Pledges 2009 Events

Field names are: Salesperson
Sale Amount
Pledge Date
Paid (in yes/no format)
Date Paid

Sample data would be:

salesperson #101 has sale amounts:

$10 pledge date 1/1 paid-yes date paid--1/5/09
$20 pledge date 1/2 paid-yes date paid--1/5/09
$50 pledge date 1/3 paid-yes date paid--1/5/09
$60 pledge date 1/4 paid-yes date paid--1/5/09
$80 pledge date 1/2 paid-no
$75 pledge date 1/3 paid-no
$50 pledge date 1/4 paid-no

I want to be able to get the total of my paids to date, but would like to
only have my pledge dates be between say 1/1/09 to 1/3/09. So my total sales
(between 1/1 and 1/3 )would be $235 and collections to date would be $140 for
a percentage of 60% collected for salesperson #101.

Duane Hookom said:
You didn't really answer my question "Do you have some table and field names
as well as sample data and desired output?"

Without know your data very well, If you want to get pledges made during a
particular period (between two dates entered on a form) the column in you
query might look like:
PeriodPledges: Abs(PledgeDate Between Forms!frmDates!txtStart and
Forms!frmDates!txtEnd) * [PledgeAmount]

This column will display the pledge amount if it falls in the date range or
0 if not.
--
Duane Hookom
Microsoft Access MVP


labtyda said:
I have a partial query done, it gives me percentages per salesperson
collected, but I need to have my sales date to be about 3 weeks earlier than
the current date to get a more accurate percentage of collections. I also
need to be able to change the date.



Here is what I have for fields: Pledge date, Sale Amount, Paid (yes/no)
Date Paid, Salesperson.

This is what I have so far for my query:
Field: Salesperson
Table: Pledges 2008 Events
Total: Group By

Field: Sale Amount
Table: Pledges 2008 Events
Total: Sum

Field: Collected: Sum(IIf([PAID], [Sale Amount],0))
Total: Expression

Field: Expr1: Sum(IIf([Paid], [Sale Amount],0))/Sum([Sale Amount])
Total: Expression

Duane Hookom said:
Do you have some table and field names as well as sample data and desired
output?

--
Duane Hookom
Microsoft Access MVP


:

I need to use a query where I can change the date. Want to get a percentage
of salespersons collections. I have a pledge date, but want sales through a
certain date, and then collections through today's date. For example sales
through three weeks ago and collections through today. I do have separate
columns for pledge date and date paid. Please, simple, I am not very
proficient in this.

thank you
Click to expand...
Click to expand...
Click to expand...
 
SELECT Sum(Abs([Paid]=True) * [Sale Amount]) As TotalPaid,
Sum(Abs([Pledge Date] Between #1/1/2009# and #1/3/2009#) * [Sale Amount]) As
PeriodSales
FROM [Pledges 2009 Events];

I would swap out the date values with references to controls on forms. Also,
this will return pledges between Jan 1 and Jan 3.
--
Duane Hookom
Microsoft Access MVP


labtyda said:
My table name is: Pledges 2009 Events

Field names are: Salesperson
Sale Amount
Pledge Date
Paid (in yes/no format)
Date Paid

Sample data would be:

salesperson #101 has sale amounts:

$10 pledge date 1/1 paid-yes date paid--1/5/09
$20 pledge date 1/2 paid-yes date paid--1/5/09
$50 pledge date 1/3 paid-yes date paid--1/5/09
$60 pledge date 1/4 paid-yes date paid--1/5/09
$80 pledge date 1/2 paid-no
$75 pledge date 1/3 paid-no
$50 pledge date 1/4 paid-no

I want to be able to get the total of my paids to date, but would like to
only have my pledge dates be between say 1/1/09 to 1/3/09. So my total sales
(between 1/1 and 1/3 )would be $235 and collections to date would be $140 for
a percentage of 60% collected for salesperson #101.

Duane Hookom said:
You didn't really answer my question "Do you have some table and field names
as well as sample data and desired output?"

Without know your data very well, If you want to get pledges made during a
particular period (between two dates entered on a form) the column in you
query might look like:
PeriodPledges: Abs(PledgeDate Between Forms!frmDates!txtStart and
Forms!frmDates!txtEnd) * [PledgeAmount]

This column will display the pledge amount if it falls in the date range or
0 if not.
--
Duane Hookom
Microsoft Access MVP


labtyda said:
I have a partial query done, it gives me percentages per salesperson
collected, but I need to have my sales date to be about 3 weeks earlier than
the current date to get a more accurate percentage of collections. I also
need to be able to change the date.



Here is what I have for fields: Pledge date, Sale Amount, Paid (yes/no)
Date Paid, Salesperson.

This is what I have so far for my query:
Field: Salesperson
Table: Pledges 2008 Events
Total: Group By

Field: Sale Amount
Table: Pledges 2008 Events
Total: Sum

Field: Collected: Sum(IIf([PAID], [Sale Amount],0))
Total: Expression

Field: Expr1: Sum(IIf([Paid], [Sale Amount],0))/Sum([Sale Amount])
Total: Expression

:

Do you have some table and field names as well as sample data and desired
output?

--
Duane Hookom
Microsoft Access MVP


:

I need to use a query where I can change the date. Want to get a percentage
of salespersons collections. I have a pledge date, but want sales through a
certain date, and then collections through today's date. For example sales
through three weeks ago and collections through today. I do have separate
columns for pledge date and date paid. Please, simple, I am not very
proficient in this.

thank you
Click to expand...
Click to expand...
Click to expand...
 
Thanks Duane,

Got it to do what I need. Thank you very much

Duane Hookom said:
SELECT Sum(Abs([Paid]=True) * [Sale Amount]) As TotalPaid,
Sum(Abs([Pledge Date] Between #1/1/2009# and #1/3/2009#) * [Sale Amount]) As
PeriodSales
FROM [Pledges 2009 Events];

I would swap out the date values with references to controls on forms. Also,
this will return pledges between Jan 1 and Jan 3.
--
Duane Hookom
Microsoft Access MVP


labtyda said:
My table name is: Pledges 2009 Events

Field names are: Salesperson
Sale Amount
Pledge Date
Paid (in yes/no format)
Date Paid

Sample data would be:

salesperson #101 has sale amounts:

$10 pledge date 1/1 paid-yes date paid--1/5/09
$20 pledge date 1/2 paid-yes date paid--1/5/09
$50 pledge date 1/3 paid-yes date paid--1/5/09
$60 pledge date 1/4 paid-yes date paid--1/5/09
$80 pledge date 1/2 paid-no
$75 pledge date 1/3 paid-no
$50 pledge date 1/4 paid-no

I want to be able to get the total of my paids to date, but would like to
only have my pledge dates be between say 1/1/09 to 1/3/09. So my total sales
(between 1/1 and 1/3 )would be $235 and collections to date would be $140 for
a percentage of 60% collected for salesperson #101.

Duane Hookom said:
You didn't really answer my question "Do you have some table and field names
as well as sample data and desired output?"

Without know your data very well, If you want to get pledges made during a
particular period (between two dates entered on a form) the column in you
query might look like:
PeriodPledges: Abs(PledgeDate Between Forms!frmDates!txtStart and
Forms!frmDates!txtEnd) * [PledgeAmount]

This column will display the pledge amount if it falls in the date range or
0 if not.
--
Duane Hookom
Microsoft Access MVP


:

I have a partial query done, it gives me percentages per salesperson
collected, but I need to have my sales date to be about 3 weeks earlier than
the current date to get a more accurate percentage of collections. I also
need to be able to change the date.



Here is what I have for fields: Pledge date, Sale Amount, Paid (yes/no)
Date Paid, Salesperson.

This is what I have so far for my query:
Field: Salesperson
Table: Pledges 2008 Events
Total: Group By

Field: Sale Amount
Table: Pledges 2008 Events
Total: Sum

Field: Collected: Sum(IIf([PAID], [Sale Amount],0))
Total: Expression

Field: Expr1: Sum(IIf([Paid], [Sale Amount],0))/Sum([Sale Amount])
Total: Expression

:

Do you have some table and field names as well as sample data and desired
output?

--
Duane Hookom
Microsoft Access MVP


:

I need to use a query where I can change the date. Want to get a percentage
of salespersons collections. I have a pledge date, but want sales through a
certain date, and then collections through today's date. For example sales
through three weeks ago and collections through today. I do have separate
columns for pledge date and date paid. Please, simple, I am not very
proficient in this.

thank you
Click to expand...
Click to expand...
Click to expand...
 
Back
Top