Newbie needs help - please

  • Thread starter Thread starter Gary Nelson
  • Start date Start date
G

Gary Nelson

In Access 2000, I have been asked to create a report, by date range that
will provide on time percentages of orders processed. Question is - HOW?

I have a "DateIn" field which gives the date the order was received and
entered into the database. Also, there is a "DateDhipped" field which gives
the date the order shipped, and a "DateDue" field which gives the date that
the order is due to the customer. I'm very new at this, and unsure how to
create this query / report. Please help
 
Gary,
Some questions for you. How do you know if an order is
on time? (Dateshipped <=Datedue?) Report by date range -
does this mean orders with DateIn between report date
range?
-----Original Message-----
In Access 2000, I have been asked to create a report, by date range that
will provide on time percentages of orders processed. Question is - HOW?

I have a "DateIn" field which gives the date the order was received and
entered into the database. Also, there is
Click to expand...
a "DateDhipped" field which gives
 
Les,

The DateDue is the agreement between us and the customer when the order will
ship from our facility. In short, the DueDate is the date that we must
meet, or the order is late if shipped after the DueDate.

The date range would be based upon the DateIn. I would be looking for Nov
1st through Nov 15th as an example.

Thanks in advance
 
Gary,

I'm sure there are better ways to do it, but something
like this should work for you.

SELECT Sum(IIf([dateshipped]<=[datedue],1,0)) AS met,
Sum(IIf([dateshipped]>[datedue],1,0)) AS notmet,
IIf([met]=0,0,[met]/([met]+[notmet])) AS pctmet
FROM OrderTable
WHERE (((OrderTable.Datein) Between #11/1/03# And
#11/15/03#));

Basically - counts those that meet and those that don't
meet. If none met, percentage is 0. Otherwise,
percentage is those that met divided by total orders for
period.e
 
You should build a query that returns the information that you want and
contains the date fields.
Below the date fields (is design view) you put as criteria ">[date1]"
whithout thequotes "", where the ">" means greater, "<" is less and "="
equal.
The variable name "[date1]" must be different for each field and i suggest
that you use names that are not in you database fields [dateFrom], [dateTo]
etc.
Then when you run your query a message would display and ask you to fill the
variables that you gave, you should put dates like 21/02/04 or something
like that.
Tell me if that worked.

--
Panagiotis Bouras
CCAI - CCNA
Greece




? "Les said:
Gary,

I'm sure there are better ways to do it, but something
like this should work for you.

SELECT Sum(IIf([dateshipped]<=[datedue],1,0)) AS met,
Sum(IIf([dateshipped]>[datedue],1,0)) AS notmet,
IIf([met]=0,0,[met]/([met]+[notmet])) AS pctmet
FROM OrderTable
WHERE (((OrderTable.Datein) Between #11/1/03# And
#11/15/03#));

Basically - counts those that meet and those that don't
meet. If none met, percentage is 0. Otherwise,
percentage is those that met divided by total orders for
period.e
-----Original Message-----
Les,

The DateDue is the agreement between us and the customer when the order will
ship from our facility. In short, the DueDate is the date that we must
meet, or the order is late if shipped after the DueDate.

The date range would be based upon the DateIn. I would be looking for Nov
1st through Nov 15th as an example.

Thanks in advance



.
Click to expand...
Click to expand...
 
Gary,
If my post looks too intimidating, it really isn't. I
just displayed my query in SQL view instead of design
view.
-----Original Message-----
Gary,

I'm sure there are better ways to do it, but something
like this should work for you.

SELECT Sum(IIf([dateshipped]<=[datedue],1,0)) AS met,
Sum(IIf([dateshipped]>[datedue],1,0)) AS notmet,
IIf([met]=0,0,[met]/([met]+[notmet])) AS pctmet
FROM OrderTable
WHERE (((OrderTable.Datein) Between #11/1/03# And
#11/15/03#));

Basically - counts those that meet and those that don't
meet. If none met, percentage is 0. Otherwise,
percentage is those that met divided by total orders for
period.e
-----Original Message-----
Les,

The DateDue is the agreement between us and the customer when the order will
ship from our facility. In short, the DueDate is the date that we must
meet, or the order is late if shipped after the DueDate.

The date range would be based upon the DateIn. I would be looking for Nov
1st through Nov 15th as an example.

Thanks in advance
Click to expand...
order
is


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