Supplier Date management

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am relatively new to Access projects though I have managed to figure out a
few things for my employer.

What I need to do now is define a database to track our supplier dates. I
have to be able to report on what percentage on-time/late for each supplier
in a continuously building history.

I have, so far, a supplier table with the info I need there and a
supplierdates table with the two dates that I have to track (requested date
and actual date delivered) and a ID field to keep it straight. I have these
table related by supplier ID. I think I have the basics figured out here,
but I am kind of stumped as to how to generate the reports that we need. I
know that query building will be involved, but I am un familiar and
unexperienced in selecting and calculating in databases.

Any help in greatly appreciated.
Many Thanks in advance.
 
Start a query ussing the date table. In the first field write a calculation
as such.
DaysLate: DateDiff("y",[Date1],[Date2])
 
Steve,

You need tables like these:
TblSupplier
SupplierID
SupplierName

TblDelivery
DeliveryID
SupplierID
PONum
DeliveryDate
OnTime 'Yes/No

Build a query based on both tables. Convert the query to a totals query and
change GroupBy under SupplierID and OnTime to Count. Set the criteria for
OnTime to False. Count of SupplierID will give you total orders by supplier
and Count under OnTime will give you the number of orders that were not on
time by supplier. You can base your report on this query and calculate
percentage directly by (Orders Not On Time)/(Total Orders) * 100. If you run
into problems, you can post back to the newsgroup. In the alternative to all
this, contact me at my email address below. I can build this database for
you for a very reasonable fee.
 
Well, Access doesn't want to accept that.
Says the expression has an invalid (dot) or ! operator or invalid parentheses.

I entered it as such under the criteria sopt
DaysLate: DateDiff("y",[Req_Deliv_Date],[Act_Deliv_Date])
Where Req_Deliv_Date and Act_Deliv_Date are the fields in the supplierdates
table.

This is Access 2000. Does that make a difference?
 
Ok. I will give that a try.

Let me back up just a little though. Re-reading my post showed me that it
didn't say exactly what I wanted it to.

I need the ability to calculate the number of days late per
order/req_deliv_date per supplier. This info can then be built into reports
that show the percentage on on lite and late receipts that can be grouped out
by suppiler. maybe sorted by dates. Maybe sorted by late/not late.
This is for a quality assurance system. I have to provide a proof of 95%
on time delivery by supplier from the data that is going to be fed into this
database. There are Raw material Suppliers and outside process suppliers. I
will need to provide data that uses the requested delivery date against the
actual delivery/arrival date. The front office will not want the PONumbers
tracked through the shipping and inspection tasks as far as I know.
 
Build a query based on both tables. Convert the query to a totals query and
change GroupBy under SupplierID and OnTime to Count. Set the criteria for
OnTime to False. Count of SupplierID will give you total orders by supplier
and Count under OnTime will give you the number of orders that were not on
time by supplier. You can base your report on this query and calculate
percentage directly by (Orders Not On Time)/(Total Orders) * 100. If you run
into problems, you can post back to the newsgroup. In the alternative to all
this, contact me at my email address below. I can build this database for
you for a very reasonable fee.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com
Click to expand...
**********************************************
Steve, why are you advertising and job-hunting again?
**********************************************
-- You abuse this group and others for job-hunting and advertising over and over again
-- You are insulting lots of people here when they ask you to stop this
-- You hide your identity while asking questions
-- You try to sell a CD ($125,--) with FREE code you gathered from these groups here
-- You posted as Steve, Ron, Tom, Rachel, Kathy, Kristine, Heather and ???
(the latest 'star' is: 'Access Resource')
-- There even has been a 'Scam-alert' about you

So why would ANYBODY ever trust you and hire you?
********************************************************

To all:
Why this answer ? ==>> We are trying to stop the advertising here.
Explanation and more on the above: http://home.tiscali.nl/arracom/stopsteve.html

Arno R
 
Sorry, Steve but I can't help you any further here in the newsgroup when
that JERK Arno R interferes.
 
Arno,

Thanks for the heads up. I realize that there may be an issue here, but I
don't know what pc datasheet's real name is. I am just asking for some
assistance with a project that I thought I had mostly figured out (but
realize that I have some way to go). Perhaps his real name is Steve as well?
This is just a little confusing for me right now. As this is off topic I
will not continue. Feel free to contact me to discuss anything you like.
Steve White
(e-mail address removed)
IT and Projects Manager
Fred R. Rippy, Inc.
phone provided if you wish
 
Back
Top