Appending data from non related tables

[cracky141]

I have a production schedule database that tracks work in progress. Done in A2002. I wan't to get the first value from the first field to contain a value for each customer.
Then append this data to a new table.
I have a schedule table with ,for example Customer(Text);OrderDate(Date);DeliveryDate(Date);Cut(Double);Edge(Double);Sand(Double) etc etc

Each field which is a production area(ie cut;edge;sand) does have a ScheduleLocationNumber which is only stored in a unrelated table.

Ultimatley I want to have my new table with the fields Customer;UnitsOfNextLocation(which would be the first value from the first field-whether its cut or the next so on so on);LocationNumber;DeliveryDate;OrderDate.

How can I append this data for the resulting table?
Here is a sample of my code for the first 3 stages.

Thanks in advance
Cracky141

 

[cracky141]

Sorry Forgot to attatch sample code to previous posting.


Function AddWorkInProgress2(JobNumber)

Dim Criteria As String, StrMsg As String
Dim Db As Database
Dim rst As Recordset
Dim rst1 As Recordset
Dim fld As Fields


On Error GoTo Err_AddWorkInProgress2
AddWorkInProgress2 = False

Set rst = Nothing
Set rst = CurrentDb.OpenRecordset("ScheduledWork", DB_OPEN_DYNASET) 'Open SceduledWork Table


Set rst1 = CurrentDb.OpenRecordset("ScheduleNumber", DB_OPEN_TABLE)
Set fld = rst.Fields


Criteria = "[JobNumber] = """ + JobNumber + """"


rst.FindFirst Criteria
JobNumber = Criteria

'For Each fld In rst
If Not rst.NoMatch Then 'If a match
Do Until rst.EOF
If IsNumeric(rst!Cut) And rst!Cut > "0" Then
StrMsg = rst!Cut
With rst1
.AddNew
!OPS = StrMsg
!JobNumber = rst!JobNumber
!DelivDate = rst!DateRequired
!ProdDate = rst!ProdDate
!CenterNo = 12 'DLookup("[ScedulingCenterNumber]", "ScedulingCenters", "[ScedulingCenter] = Cut&quot
.Update
End With
'Exit Do
End If
rst.MoveNext 'Criteria = Nothing
'Criteria = "[JobNumber] +1 = """ + JobNumber + """"

If IsNumeric(rst!Edge) And rst!Edge > "0" Then
StrMsg = rst!Edge
With rst1
.AddNew
!OPS = StrMsg
!JobNumber = rst!JobNumber
!DelivDate = rst!DateRequired
!ProdDate = rst!ProdDate
!CenterNo = 11 'DLookup("[ScedulingCenterNumber]", "ScedulingCenters", "[ScedulingCenter] = Edge&quot
.Update
End With
Exit Do
End If
rst.FindNext Criteria
If IsNumeric(rst!Bore) And rst!Bore > "0" Then
StrMsg = rst!Bore
With rst1
.AddNew
!OPS = StrMsg
!JobNumber = rst!JobNumber
!DelivDate = rst!DateRequired
!ProdDate = rst!ProdDate
!CenterNo = 10 ' DLookup("[ScedulingCenterNumber]", "ScedulingCenters", "[ScedulingCenter] = Bore&quot
.Update
End With
Exit Do
End If

Thanks again

 

[beetee]

You may consider redisgning your database a bit.

You have customers, orders, and events associated with the orders.

You could create a customer table, with a primary key of the customer (text) field, and an additional autonumber field as an internal id.

Secondly, create an order table that has the customer ID (generated by the autonumber), and whatever order-specific information you have, and, again, an internal order ID that is an autonumber field.

You could have a main form that has customers, with an embedded orders sub-form.

Finally, create an events table to track what events are associated with a given order. The events table would have the order ID, an event type (which should probably be, again, an ID from an event type lookup table), event date/time, event status (again a lookup table), and whatever else you choose.

You could let the users view/enter events by popping up a form when they double click on an order, using the current order ID as a filter on the events form.


This technique should eliminate the need to copy information from one record to another, which can result in non-normalized data.

 

[cracky141]

Thank you for you help.
I almost have my database setup as you suggest, apart from an events table.
How would I set this up to track progress of orders over 10 differant production stages(event stages)?
This is basically so as we know how many units of time are waiting to be done at a certain stage.
This info is gained at an early stage in the syatem when a quote has been requested. There are at least 10 more stages then the data is updated to my scheduling table from there it need to be in an expanded report to reflect production changes made to units.
I have created the function and and it works only for the first record in rst.
I would be happy to change other aspects of the database design if thats a better long term solution.

Thanks again

Cracky141