Percent of ontime deliveries 1

[L0stAngel]

Hello, I have a table in ms access 97 named 'tblMedRecords'. This table contains 'customername', 'date sent', and 'date due'. I need to make a report that will ask for you to input the customer name, then display the customers name on the report, and the percent of the dates that were sent out BEFORE or DURING the date due. I'm pretty new to Access, and would like to know how to do this.

Thanks for any help.

 

[THWatson]

LOstAngel
Here's one approach.

1. Create an unbound form. Let's call it frmCustomerSelect.
2. Create un unbound text box. Let's call it txtCustomer. It will be for entering the desired customer's name.
3. Put a command button to run your report in either Preview or Print mode (I prefer Preview mode, at least when setting things up, and you can add the Print button later). Use the wizard to create the button to open the report you are going to run. It will have a line like this
DoCmd.OpenReport "rptCustomer",,acViewPreview
4. Create a query based on your table tblMedRecords.
In the customername column put the following criteria...
Forms!frmCustomerSelect!txtCustomer
5. Create a report with its record source the query you have just made. Let's call it rptCustomer. (if you call it something different you have to change the name of the report in step 3.
6. Put your fields on the report in the Detail section. In the Report Footer section put the following text box
=Avg(Abs([date sent]<=[date due]))

By the way, I note that you have spaces in your "date sent" and "date due" fields. There is nothing wrong with this except when you do this you always have to enclose those fields in square brackets [] when you refer to them, so it's a little better to eliminate spaces in field names.

Hope this helps.

Tom

 

[THWatson]

LOstAngel
It occured to me I missed something...

The text box that you put in the report footer...there are two ways to make sure that the calculation is done in %. One way is to define the format in the text box's properties as Percent. The other is to make the expression
=Format(Avg(Abs([DeliveryDate]<=[DueDate])),"Percent")

Tom

 

[L0stAngel]

Thank you very much, I have one problem though. I did everything you have said, but for some reason when It goes to make the report, it has two instances of information on the report.

Example:

Brock was Ontime 75%



Brock was Ontime 75%

 

[L0stAngel]

Alright, I think I figured out what was causing it, and it's my fault I think for not explaining right.

In my table, I have can have multiple instances of a customer ordering a medical part. On the report, it needs to see what the percentage of the total times the order has been filled ontime (say if theres 20 instances of the customer buying something, and 10 of the times the part was ontime, and the other 10 times it wasnt ontime, that would be 50%. Does this make sense?

 

[THWatson]

LOstAngel
What you say makes perfect sense, and that's the way I set up my test table, query and report.

Now, as to why you are getting two instances of the % information, I'm not quite sure. That isn't happening in my test case.

So, questions...
1. What do you have in the Detail section of the report?
2. Where are you putting the expression I gave you? It should go in the Report Footer.

By the way, you can make life a little easier in selecting in the form if you change the criteria in the customer name column in the query from what I suggested originally to...
Like "*" & Forms!frmCustomerSelect!txtCustomer & "*"
Then, if you can't remember exactly whether or not the customer is called Brock or Brocken or McBrock, entering "Bro" in the form will get all of those.

Another approach in the form would be: rather than typing in a text box to define the customer, you could have a combo box pull up all customers and then change the criteria in the customer column in the query to reference that combo box in the form.

Anyway, first things first. Let's solve the duplicate information and go on from there.
Tom

 

[THWatson]

LOstAngel
Just checking something...

The text box you put in the Report Footer has to be an unbound text box.

Tom

 

[L0stAngel]

1. I have txtCustomer linked to the column in my table named Customer (this might be my problem? It displays multiple instances on the form as to the number of [customer name i typed]records in the table.

2. I put the expression in the Footer in a textbox, but It says #Error. But if I move it to the Details, it displays the percent correctly.

 

[THWatson]

LOstAngel
1. Yep, that's probably the issue. If you read my original post, you will note I say that txtCustomer, in the form, should be an unbound text box. It's only for entering search criteria.

2. The expression should work in the Report Footer...as long as it's an unbound text box. If you put it in the Detail section, it should produce multiples, showing as many times as there are records. Access is picky about stuff like that.

Tom

 

[L0stAngel]

I just noticed I meant to say Report where I said Form above. txtCustomer in Report (where the customer name is displayed) is linked to CustomerName in the table.

As for the textbox in the footer, it still will not work in the footer. Is there a way I can send you the access file and you take a look to see what step im not getting?

 

[THWatson]

LOstAngel
That might be the simplest thing, if you sent the file.

Because of the possibility of intrusive parties getting into this site, I will have to disguise my e-mail address a bit, but I'm sure you can interpret the following...
twatson at sentex dot "the last 3 letters of fishnet"

Tom

 

[L0stAngel]

alright, thank you very much. Sending it now...

Inside are 1 table, 1 query, 2 forms, and 2 reports (both i redid the steps again in the same access file.

 

[THWatson]

LOstAngel
Two problems...

1. The data type in the DateDue and DateOut fields in the table needed to be Date/Time

2. The expression to return a % needed to be in the Report Footer, not the Page Footer.

Tom

 

[L0stAngel]

Thank you very much THWatson. As I said, I'm fairly new to Access. I usually have just used it to make tables and done the rest of coding in a different programming language. You helped me greatly.

 

[THWatson]

Glad we got it!

Tom