I'm not sure what forum to ask this.

I have an excel spreadsheet that I exported over into a database. It has over 3000+ records. I need to make an adjustment by date. What I want to do is add a sequential number to each record that has a different date. For example:

1/1/02 would be number 1
1/2/02 would be number 2
1/2/02 would be number 2
1/5/02 would be number 3
1/5/02 would be 3
2/1/02 would be 4
and so on.

Does anyone know how I could do this without having to do this by hand?

What is the need for this number ???

Also you show 2 entries for 1/2/02 and 2 entries for 1/5/02 ...
How do you propose to number these duplicated entries ???

RDH

Thank you for responding so fast. I'm tearing my hair out and feel I'll be bald in another hour.

The maint department has 39 excel workbooks, each with 6-24 pages or so. I'm trying to pull everything into a database.

What I'm trying to accomplish right now is to create a work order ID (backwards, I admit, but I don't want to lose the history). A piece of equipment (unique id number) could have several procedures done in one day (change oil, replace filters, replace bulb, etc.). I don't want an individual record by date for every thing that was done in one day. I want one workorder for the day for that piece of equipment, listing all maintenance done to it. Does this make sense?

So 2 entries for 1/2/02 could be correct and 1 entry for 1/1/02 could be correct. The spreadsheets have up to 10 entries for 1 piece of equipment for 1 day. Ugh!

I'm open to any suggestions!

Why would the records need to be numbered was my question ....

Each task performed on a piece od equipment would be a seperate record in a table.
Each record would contain the date the task was performed.
A Time value would be needed to determine which task was performed in what order .. not a number value.

RDH

As Ricky pointed out, each service item would be a seperate record, e.g.
EquipID SvcDate Service
123 30 Aug 04 Lube
123 30 Aug 04 Oil change
123 31 Aug 04 R & R muffler bearings
123 01 Sep 04 Reset framus

would result in 3 separate service orders, one for each day (for this piece of equipment).

In response to your original question, the following sub assigns a incremented sequence number
to each new date

HTH - Bob

I admit, I can be looking at this wrong. Actually, I'm sure I am.

Right now, the work history table is related to the equipment table and I get a complete history for that piece of equipment, which is nice. But, I'm thinking I need to take it another step. The history shows what was done, the date it was done, and who did the work. But I also need a way to generate a work order based on a preventative maint schedule (another table) and once that work order has been completed to show in the history. So I was thinking that I needed a work order number in the history. I didn't really want to have a different number for the same day, but if you think that would be the best way to go, then (of course) that would be easy for me to number.

I have been researching all day looking for the best way to set this up and I truly appreciate all your help.

When more than on task was performed on a specific day .. how do you know which one happen first in the day Bob ???
That was one of my points ...
If the order in which the task happened is not relevent .. then why the need for the sequential number ???

RDH

Ricky -

I wouldn't care whether they changed the oil first or did the lube job. Just
that the tasks occurred on the same day. The time is unimportant if what
I'm attempting to do is develop a maintenance record for a vehicle or piece of
equipment.

Not that I'm necessarily in favor of this approach, but the sequential business
has to do with dates only, not date/times, e.g. all the 1 Aug tasks are numbered 1, the 2 Aug tasks
are numbered 2, etc., and the example sub does just that.

Bob

I disagree ...

I spent almost a year developing and implementing a Preventive Maintenance Database for a Fortune 500 company and they wanted all the details in the history of every task performed.
Of course this was dealing with all equipment within a plant and it included all repairs performed and all scheduled preventive maintance tasks on every piece of equipment.

They wanted to know the date and time performed, who done it, how long it took them, what parts and supplies used ...ect.

If this is simply for vehicle maintance ... then the order performed may not be important ...
But ... then what is the numbered record for ????

RDH

This is a sample of what I'm working with. It already has a unique number (Work History ID--WHID), but I'm thinking it needs a Work Order ID to tie it to a work order table, so that when a repair or pm maint is done, it will show up in the history. Because this is all done on the same day, I was thinking that one Work Order number would be better than two. It would be similar to ordering several products on one purchase order, in my mind.


WHID Date EquipNo Work Performed Performed By Time Completed
2527 3/1/2004 AC2 BLOW OUT RADIATORS Randy Yes
2528 3/1/2004 AC2 CHANGE OIL AND FILTER Randy Yes

Please stay with me!

Each task should be a seperate record ... thus a seperate Work Order in my opinion ...
Then each task can be logged back in as completed (with all necessary details) in the database seperately ...

RDH

Ricky -

Suspect the level of detail depends on the application. I can see where, in a
plant with dedicated maintenance personnel, it might be important to know who
did it, when he started and when he finished.

I too have dealt with a preventive maintenance application (for an organization that
could spell Fortune 500, if they brought-in outside consultants), where the equipment
in question was vehicles and the work was farmed-out, not done in-house. We needed
to know when (date) it was done, what was done and by whom (vendor). The time
started and time completed were absolutely irrelevant.


BTSOOM! Just wanted to show how it could be done.

Bob

LMAO ... "BTSOOM!"

I see where this does appear to be just for vehicle maintenance ...
Anyway ... the time the task started and how long it took them dealt with "down time" .. if the task was performed during production hours ...

RDH

Actually, its not just for vehicles. It's for all the equipment (presses, press brakes, assembly guns, forklifts, buildings, tracktors, trucks, etc.

You are all right! 39 Excel workbooks (one for each type of equipment) and between 6 and 26 spreadsheet (one spreadsheet for each individual piece) all having a unique equipment number and asset number. Most all pieces of equipment have history going back to 1999. Some of the history is valid, so to speak, some of it, however, is more like a journal for the daily duties (ugh!).

But if a piece of equipment needs say 17 individual tasks performed in one day, do I really want to print out 17 workorders for one piece of equipment? Change Oil, replace filter, grease zert fittings, etc. Isn't is possible to have a work order or service order print out listing everything that needs to be done (Preventative Maint) and then once all items have been completed, check the work order off as being completed, and still have everything that was done show in the history? There has to be a way to accomplish this.

Please stay with me!

Sure ... that would be the task to perform ... if the same duties are to be done a on a scheduled interval.
And .. I 'm assuming this is a scheduled PM ...

I would name the Task something like: Monthly Press Machine PM
Then in the Task instructions is where you define what exactly is to be performed in this task ...

RDH

Bob, I put your code in a module and changed what I thought I had to. See below:


'Purpose: Assigns sequence number to record
' incrementing sequence number each
' time the record date increments
'Coded by: raskew
'*******************************************

Dim rs As Recordset
Dim datehold As Date
Dim strSQL As String
Dim n As Integer

'I used a maketable query on Northwind's Orders to
'create ztblOrders:
' - OrderID
' - OrderDate
' - SeqNo (long integer)
'You'll need to adjust strSQL to include your date and
'SeqNo fields.

strSQL = "SELECT tblWorkHistory.WHDate, tblWorkOrders.SeqNo" _
& " FROM tblWorkOders" _
& " ORDER BY tblWorkHistory.WHDate;"

Set rs = CurrentDb.OpenRecordset(strSQL)
n = 1
datehold = rs!WHDate
Do While Not rs.EOF
n = IIf(rs!WHDate = datehold, n, n + 1)
With rs
.Edit
!SeqNo = n
.Update
End With
datehold = rs!WHDate
rs.MoveNext
Loop
rs.Close

End Sub

When I call for it in a make-table query, it askes me for a seqnumber parameter. What did I do wrong?

LilAnn -

I've exceeded my wine-limit for a Sunday evening.
Will respond early in the A.M.

Best wishes,

Bob

LilAnn -

I mentioned 'maketable query' only as an indicator as to where I was getting the dates
I needed to work with. Shouldn't be a need for you to do this, since you've already
got your source tables. At most, you'd need to add a SeqNo (number) field if it doesn't
already exist.

This shows two tables (tblWorkHistory, tblWorkOrders) without a join. That's not going
to fly. In rereading your earlier posts, I'm wondering if you're just attempting to
duplicate existing data in a different format--something that can/should be done with
queries and reports.

Would you provide a small sample of your data (in A97, please).

Bob

I don't have a work order table as of yet. I haven't figured out how I need to have it all tie in place. I have attached my database. This is just pulling everything out of excel files. I will pretty much have to start over after I get all the data the way I need it. Open to any all suggestions you may have.