Urgent - Selective Matching Problem

[CharlieMike73]

Hi,

I need to do some selective matching from two tables and add tthe result to a third (X-Ref) Table

For example - We have the following Tables:

Material
Unit
X-Ref_Unit_Material

In the Material Table we have over 100 different materials.
In the Unit Table we have over 500 units/crews.

Some units do construction, so do maintenance, some do sign post errecting, and others do other activities...

For example:

Material Table

| Mat_ID | Description |
|--------|-------------|
|      1 | Steel Bar   |
|      2 | Crack Seal  |
|      3 | Sign Post   |
|      4 | Ditch Liner |
|      5 | Concrete    |
|      6 | Sand        |
|      7 | Signs       |
...


Unit Table

| Unit_ID | Description         | Type  |
|---------|---------------------|-------|
|       1 | Construction Crew 1 | Const |
|       2 | Sign Crew 1         | Sign  |
|       3 | Construction Crew 2 | Const |
|       4 | Maintenance Crew 1  | Maint |
|       5 | Sign Crew 2         | Sign  |
|       6 | Maintenance Crew 2  | Maint |
|       7 | Maintenance Crew 3  | Maint |
...


X-Ref_Unit_Material Table

| Mat_ID | Unit_ID |
|--------|---------|
|      1 |       1 |
|      1 |       3 |
|      2 |       4 |
|      2 |       6 |
|      2 |       7 |
|      3 |       1 |
|      3 |       2 |
|      3 |       3 |
|      3 |       4 |
|      3 |       5 |
|      3 |       6 |
|      3 |       7 |
|      4 |       1 |
|      4 |       3 |
|      4 |       4 |
|      4 |       6 |
...

As you can see from the X-Ref Table above not all Units need to be associated with each material. ie a sign crew does not need steel bar.

So now that you have an idea what I am working with, here is what I need to do, I need to have some kind of script that can step through each material and see what units need a ref to it and append the new entries to the X-Ref_Material_Unit table.

Some crews/units may already have some of the materials already associated to them so I need something that checks if the material is already linked to that unit also, and if not then adds it.

I have a list of what Unit Type would need what material (in Paper Form), but before I enter it into the computer I wanted to see if anybody could help with this. Otherwise I will have thousands of records that I have to enter one by one.

Unfortunatly I do not have control over the design of the tables as they are part of a grander 140 table database.

PLEASE HELP!

If you dont understand, or have more questions about this problem PLEASE PLEASE contact me, this is a very important and urgent matter and I dont have the time to enter them all manually.

email me at charles.mcgrotty@state.me.us

Regards,
Charles McGrotty

 

[LittleSmudge]

What you have there is a classic Many - to - many relationship with a linking table - nothing complex.

What, I think, your real problem is - is to populate the linking table from the paper copy.

That's not an Access problem - that's a ring the temp agency and get a typist problem !!!

Alternativly scan the paper copy using OCR and convert it into a table.
Check and correct typos.
Import the table into Access and then rename it as "X-Ref_Material_Unit "

Job done -



'ope-that-'elps.

G LS

 

[CharlieMike73]

A friend sent me this and told me that with a little alteration and "creative wizardry". However he is now unreachable for a week, which is too long away to wait, and I don't understand what is going on or what I have to change.

Public Function ConvertAssets()
On Error GoTo Err_ConvertAssets
 
    Dim dbs As Database
    Dim rstRoutes As DAO.Recordset
    Dim rstAssets As DAO.Recordset
    Dim qdfRoutes As QueryDef
    Dim qdfAssets As QueryDef
    Dim SQLStr, Route As String, StartMP, EndMP, CalFactor, test As Variant
    Dim RLMDATE As Date, RouteRec, AssetRec As Integer
    Set dbs = CurrentDb
    Set qdfRoutes = dbs.CreateQueryDef("")
    RLMDATE = Date
    RouteRec = 0
 
    
    SQLStr = "SELECT UpdateMilePoints.RTCODE, UpdateMilePoints.BMP, UpdateMilePoints.EMP, UpdateMilePoints.Cal_Factor"
    SQLStr = SQLStr & " FROM UpdateMilePoints"
    SQLStr = SQLStr & " ORDER BY UpdateMilePoints.RTCODE, UpdateMilePoints.BMP;"
    qdfRoutes.SQL = SQLStr
    
     'Test for no records found, show MsgBox, and Exit
    Set rstRoutes = qdfRoutes.OpenRecordset()
       If rstRoutes.RecordCount = 0 Then
           Exit Function
       End If
 
While Not rstRoutes.EOF
    Route = rstRoutes!RTCODE
    StartMP = rstRoutes!BMP - 0.001
    EndMP = rstRoutes!EMP + 0.001
    CalFactor = rstRoutes!Cal_Factor
    ProgressMonitor.Caption = "Retrieving a set of records.    Please Wait!"
    Repaint
    
    Set qdfAssets = dbs.CreateQueryDef("")
    SQLStr = "SELECT Asset.ROUTE, Asset.B_MILE, Asset.E_MILE, Asset.RLM_DATE"
    SQLStr = SQLStr & " FROM Asset"
    SQLStr = SQLStr & &quot; WHERE (((Asset.ROUTE)='&quot; & Route & &quot;') AND ((Asset.B_MILE)>&quot; & StartMP & &quot;) AND ((Asset.E_MILE)<&quot; & EndMP & &quot;) And ((Asset.RLM_DATE) = #7/2/2001# ))&quot;
    SQLStr = SQLStr & &quot; ORDER BY Asset.ROUTE, Asset.B_MILE;&quot;
    qdfAssets.SQL = SQLStr
    
    RouteRec = 0
     
     'Test for no records found and Exit
    Set rstAssets = qdfAssets.OpenRecordset()
       If rstAssets.RecordCount = 0 Then
           GoTo NextRecord
       End If
       
    While Not rstAssets.EOF
         ' Update Mile points in the Asset Table.&quot;
        rstAssets.Edit
        rstAssets!B_MILE = rstAssets!B_MILE + CalFactor
        rstAssets!E_MILE = rstAssets!E_MILE + CalFactor
        rstAssets!RLM_DATE = #5/20/2002#
        rstAssets.Update
        RouteRec = RouteRec + 1
        ProgressMonitor.Caption = Format(rstAssets.PercentPosition, &quot;##0&quot;) & &quot;% &quot; & RouteRec & &quot; Records of &quot; & rstAssets.RecordCount & &quot;     &quot; & rstRoutes.RecordCount & &quot; record sets left!&quot;
        Repaint
        rstAssets.MoveNext
 

        
    Wend
 
NextRecord:
rstRoutes.Delete
rstRoutes.MoveFirst
 

Wend
 
Exit_ConvertAssets:
    Exit Function
 
Err_ConvertAssets:
    Resume Exit_ConvertAssets
 
End Function

He said what I need is possible he has done it with a simular excersize.

I was told I need to create a table that will be something like:

UT# = Unit Type #

| Mat_ID | Description | UT1 | UT2 | UT3 | UT4 |
|--------|-------------|-----|-----|-----|-----|
|      1 | Steel Bar   | 1   | 2   |     |     |
|      2 | Crack Seal  | 2   |     |     |     |
|      3 | Sign Post   | 1   | 2   | 3   | 4   |
|      4 | Ditch Liner | 1   | 2   |     |     |

I have several queries where I have limited the units by their different types and as told to use an IF statement to figure out which limited list query to run for the unit ID's

Unit Types:
1 = Construction Crews
2 = Maintenance Crews
3 = Sign Crews
...

 

[LittleSmudge]

NO, NO, NO,

NEVER,

Not in the whole wide appliance of Relational Databases anywhere EVER do such a thing.

Read up on Normalisation Rules - ( when you get a change - and you'll understand why. )

ALL you need is one extra field in the X-Ref_Unit_Material Table

| Mat_ID | Unit_ID | QtyUsed|
|--------|---------|--------|
|      1 |       1 |    1   |
|      1 |       3 |    2   |
|      2 |       4 |    3   |
|      2 |       6 |    1   |
|      2 |       7 |    1   |
|      3 |       1 |    2   |
|      3 |       2 |    1   |
|      3 |       3 |    2   |
|      3 |       4 |    1   |
|      3 |       5 |    3   |
|      3 |       6 |    2   |
|      3 |       7 |    1   |
|      4 |       1 |    1   |
|      4 |       3 |    2   |
|      4 |       4 |    1   |
|      4 |       6 |    1   |

- Still shuddering at the thought that anyone would want to do such a thing .. ..

G LS