Calculations on non-number fields

  • Thread starter Thread starter kreativekreations
  • Start date Start date
K

kreativekreations

Hello all,

I am a newbie, however, I am trying to create a Driver Deliqnency report from
a table with the following fields:

Driver Deliquency Table
________________________

Unit # (truck id)
Driver Name
Supervisor
Due Date (the written report is due before it goes into deliquent status)
Status (reason for deliquency)
Date Contacted (when the driver was contacted about the deliquency)
Month (Month of the Year)
Driver Deliquency (which is the field that should count the number of times
the driver has been deliquent.)

The report is to be sorted by supervisor and the output should look something
like:

Supervisor Driver Name Unit #
Times Deliquent
________ __________ ____
_____________

John Doe Mark Brown 543
4
Oscar Meyer Peter Pan 127
1

This is a two fold question:
1. If this report is based on the table in the first half, how can I count
the number of times the driver was deliquent. Note: There may be multiple
rows of a deliquent driver in the Driver Deliquency Table, that will need to
be counted? Do I use a query? The whole point is to not show repeated names,
only the the total number/count based on the table.

2. What type of field should the driver deliquency be, right now, there is
no data going into this, should this be a calculated field within the table
itself?

Thanks in advance.
 
First, fix your database design. If you have multiple delinquencies, then
your structure is all wrong. You are adding an entire record each time when
all you need to do is record the delinquency. You are trying to use Access
to build a spreadsheet - Don't.

You need two (or more) tables.

The first table would store the driver data. The second would store each
instance of the delinquency.

A few other thoughts. You should not store name in one field. I'd store
last and first in separate fields. This will allow you to sort by last name
or format your output as Last, First or First Last.



DriverTable
---------------------
DriverNumber
DriverFirstName
DriverLastName
Supervisor

DeliquencyTable
 
Another thought. Why do you have a date field and a separate field for
month? That is redundant.

If you need to pull the month out of the date for some reason, there are
several function to do so.

Month(DateContact)

Format([DateContact],"mm")

Datepart

etc.
 
Thanks Rick,

I didn't think of making a seperate date, my last question is the field
titled deliquency is not a inputted field, but rather one in which it should
automatically count the number of rows in a record per driver and return the
number of times that particular driver has been reported deliquent. How can
I make it count the number of rows, so that when I create a report based on
the tables, it will not show:

Driver: Supervisor:
_____ _________

John Doe Wayne Payne
John Doe Wayne Payne
John Doe Wayne Payne

but rather:

Driver: Supervisor: # of times deliquent:
_______ _________ ________________
John Doe Wayne Payne 3


Thanks





Rick said:
Another thought. Why do you have a date field and a separate field for
month? That is redundant.

If you need to pull the month out of the date for some reason, there are
several function to do so.

Month(DateContact)

Format([DateContact],"mm")

Datepart

etc.
Hello all,
Click to expand...
[quoted text clipped - 48 lines]
Thanks in advance.
Click to expand...
Click to expand...
 
Agree with Rick B. Fix the design and eliminate duplicate data by having
linked "driver" and "delinquency event" tables. Then use a "Group By" query
(Grouping on the driver's name) to Count the number of times they appear in
your "delinquency event" table.

Good luck!
 
Back
Top