I was thinking:

tblDevice
DeviceID = PK

tblType
TypeID = PK

If Yellow always goes with Wing, for example, then there is no reason to split these.

tblDevice
DeviceID = PK
Type - text

Then for your main table:

tblMain
MainID
DeviceID - FK
TypeID - FK - if this is a seperate table, otherwise omit
Tag - text
Location - probably a look up too
Comm - text or look up if these are phone numbers

so how many Tag/Comm records are associated with each Device / Type combo?


mmcdonal

Each Device/Type combo will have at minimum 1 Tag/Comm but could be up to 4.

Here's what I have setup.
tblDevice
DeviceID = PK
Type - text


tblDevice+AllInfo
DeviceID
Type
Panel
Tag - PK
Location
Comm


The relationship is
Device = Device

My main table is what I'm not following. I thought tblDevice was my main table. Am I supposed to do another make table query or how am I supposed to create my main table?

You shouldn't need Type in both tables. It should be:

tblDevice
DeviceID - PK
Type - text


tblDevice+AllInfo
Tag - PK
DeviceID - FK
Panel
Location
Comm

Based on this structure, I created a table to hold your label values:

tblLabel
Device - text (all text fields)
Type
Tag0
Comm0
Tag1
Comm1
Tag2
Comm2
Tag3
Comm3

The reason for storing the values in a table is that you can do mail merges with this. You can't do much with a query.

I created a query to populate this table called Query1:

SELECT tblDevice.DeviceID, tblDevice.Type, tblDevice_AllInfo.TagID, tblDevice_AllInfo.Comm
FROM tblDevice INNER JOIN tblDevice_AllInfo ON tblDevice.DeviceID = tblDevice_AllInfo.DeviceID;

I also created a DELETE query to depopulate the table called qryDELETE_tblLabel:

DELETE tblLabel.*
FROM tblLabel;

Then I created a form, and put a button on it. The button runs the Delete query to empty the tblLabel, then populates tblLabel from the Query1. This is the code for the button's On Click event:

'---------------Code Starts----------------

Dim rs_D, rs_Q, rs_L As ADODB.Recordset
Dim sSQL_D, sSQL_Q, sSQL_L As String
Dim sDev, sType, sTag, sTagL, sComm, sCommL As String
Dim TArray, CArray As Variant
Dim i As Integer

DoCmd.SetWarnings False
DoCmd.OpenQuery "qryDELETE_tblLabel"
DoCmd.SetWarnings True

sSQL_D = "SELECT * FROM tblDevice"
Set rs_D = New ADODB.Recordset
rs_D.Open sSQL_D, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

Do Until rs_D.EOF
    sDev = rs_D("DeviceID")
    sType = rs_D("Type")

        sSQL_Q = "SELECT * FROM Query1 WHERE [DeviceID] = '" & sDev & "'"
        Set rs_Q = New ADODB.Recordset
        rs_Q.Open sSQL_Q, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

        ReDim TArray(0)
        ReDim CArray(0)

        sSQL_L = "SELECT * FROM tblLabel"
        Set rs_L = New ADODB.Recordset
        rs_L.Open sSQL_L, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

            rs_L.AddNew
            rs_L("Device") = sDev
            rs_L("Type") = sType

            i = 0
            Do Until rs_Q.EOF
                sTag = rs_Q("TagID")
                sComm = rs_Q("Comm")
                ReDim Preserve TArray(i)
                TArray(i) = sTag
                ReDim Preserve CArray(i)
                CArray(i) = sComm
                i = i + 1
            rs_Q.MoveNext
            Loop
            rs_Q.Close
            i = 0
            Do Until i = UBound(TArray) + 1
                sTagL = "Tag" & i
                sCommL = "Comm" & i
                rs_L(sTagL) = TArray(i)
                rs_L(sCommL) = CArray(i)
                i = i + 1
            Loop
            rs_L.Update

            rs_L.Close

rs_D.MoveNext
Loop

rs_D.Close
'-----------Code Ends--------------

Now create your report or mail merge from tblLabel. THIS WILL ONLY WORK WITH UP TO 4 Tag/Comm fields.

HTH

P.S. Since I develop mostly Access/SQL I use ADO. You may want to use DAO, but then the code is up to you. DAO is not typically referenced in 2003, so it makes applications a little dicey on most desktops what with missing reference errors.






mmcdonal

Sorry, I used tblDevice_AllInfo as the table name, not tblDevice+AllInfo, since I don't like the + sign in a table name. I didn't even know Access would support that.

mmcdonal

Also, I know I could have used a multi dimension array for Tag and Comm. I just didn't want to look up the syntax. Can anyone share that with me?


mmcdonal

You know what, if you add more tag/comm fields to tblLabel, this code will continue to function. Just add them in sequence: Tag4, Comm4, Tag5, Comm5, etc. That is the only limiting factor.

I know some posters will also be upset with the denormalized tblLabel, but it speeds label processing and makes it bullet proof.

mmcdonal

Sorry I keep posting on this, but I do a lot of similar development and feel I need to justify some of the methods.

If your main tables are linked to a back end, and you create tblLabel to be local, then all your processing happens locally and you don't have to worry about label conflicts. This is the environment I do most of my work in. Local data pulls and processing to keep the wire cool and locks and conflicts to 0.


mmcdonal

I'm not sure that + works in Access, that was a typo I missed when posting. Sorry about that. I've got it 95% worked out now. Just playing with some visibility and I'll be done. I appreciate all your help!!