How to group records from two different tables

[evanark]

I have two tables, Account and SystemConfiguration. Account primary key is accAccountID, this is tied to SystemConfiguration table by sysAccountID, foreign key. SystemConfiguration has its own primary key sysSystemConfigID. How can I group records together?

Example

accAccountID sysSystemConfigID
1 1
1 2
1 3

2 4
2 5
2 6

The purpose of this is that one Account can have multiple system config records. I need to be able to go back and do some calculation by subtracting from the previous sysSystemConfigID

This is another example

sysAccountID sysSystemConfigID
5 7
5 8
5 12

Is that possble to keep these grouped. I want to subtract a field from sysSystemConfigID 12 from the field that has sysSytemConfigID 8. Is that possible in access?

This scenerio is when a user wants to go back and add another system to an existing account and still be able to subtract from the previous sysSystemConfigID even if sysSystemConfigID is not in order.

 

[rzw0wr]

I don't understand your use of subtract. Mathematically subtract or remove subtract?
If I understand you correctly about having records in order. Access does not care. Order, sort is for display only.

Dale

 

[evanark]

Sorry for the lack of explanation, was in a hurry. There is field in the system configuration table called hop spacing, it stores the difference from the field called hop pattern. hop pattern subtracts from the previous hop pattern. I can do this with new records with a Dlookup because the sysSystemconfigID is in order with new records. But if I go back and add a new system to the sysSystemConfiguration table for an existing account, the sysSystemConfigId is not in order so it subtracts the hop pattern from the provious record that is not associated with the account. Is the away to keep the systems in order based on the account. Like I mentioned, the two tables are connected by accountID. I am sorry if this confusing, I know what I want to do but explaining it is difficult.

Here is an example of the form

 

[rzw0wr]

Sounds like your tables could use a little normalization.
Are you storing calculated fields in a table?

Dale

 

[evanark]

At first hop spacing was just a text box, but I could not get calculations to work right when subtracting from a previous record, the hop spacing had to be part of the record so I am storing this calculation in the table. Here is my table Structure.

 

[evanark]

I found a work around, instead of using an autonumber, I created an new field in the table called Base Number which is unique to the account.

Here is my code using the sysSystemConfigID

sysHopSpacing = Abs([sysHop1] - DLookup("[sysHop1]", "tblSystemConfiguration", "[sysSystemConfigID]=Forms![frmTempestCoordination]![frmSubSystem]![sysSystemConfigID] - 1 "))


And here is my work around using the new field Base Number as a unique Identifier

sysHopSpacing = Abs([sysHop1] - DLookup("[sysHop1]", "tblSystemConfiguration", "[sysBaseNumber]=Forms![frmTempestCoordination]![frmSubSystem]![sysBaseNumber] - 1 "))

The only challenge now is to figure out if I can code the form to put a number in the base number field and increment it by 1 when a new system is added to the account

Base number 1
Base number 2 and so on. I need this to prevent the user from leaving the base number field empty as if it is null then the calculation will not work. So far the system configuration is a datasheet view and I dont know how to throw up an exception if the field in null.

This is my code to try to prevent the user from leaving the base number null but it does not work once I tab over to the next field in the datasheet

Private Sub sysBaseNumber_BeforeUpdate(Cancel As Integer)
If IsNull(Me.sysBaseNumber) Then
MsgBox "Please enter a base number, example for the first base enter 1 for base number and so on for additional bases (1,2,3,4,5,).", vbOKOnly + vbInformation, "Enter Base Number"
Forms!frmTempestCoordination!cmdAddNew.Enabled = False
Me.sysBaseNumber.SetFocus
Else
Forms!frmTempestCoordination!cmdAddNew.Enabled = True
End If
End Sub

Any suggestions?