Forum Discussion
Link Rows within an Access Table or Query
Hi all
I've googled to within an inch of my life, spoken to SQL and Power BI experts and Excel experts, and no-one can seem to help me building a code that will work in Microsoft Access VBA to calculate a linked field from the (minimal!) available data.
I am receiving a .csv report that needs to be imported into an Access table. The supplier of the data is unable to assist with providing a linking field (much effort expended on this option to date and is ongoing but need a solution ASAP). The data appears in a format equivalent to the below.
COLUMNS: [ID], [Name], [DOB], [Address], [Phone], [Value]
Row1: [ID], [Name], [DOB], [Address], [Phone], Criteria1
Row2: [ID], [Name], [DOB], [Address], [Phone], Criteria2
Row3: [ID], [Name], [DOB], [Address], [Phone], Criteria2
Row4: [ID], [Name], [DOB], [Address], [Phone], Criteria1
Row5: [ID], [Name], [DOB], [Address], [Phone], Criteria2
Row6: [ID], [Name], [DOB], [Address], [Phone], Criteria1
Row7: [ID], [Name], [DOB], [Address], [Phone], Criteria2
Row8: [ID], [Name], [DOB], [Address], [Phone], Criteria2
Row9: [ID], [Name], [DOB], [Address], [Phone], Criteria2
Each line with Criteria1 in [Value] is designated the Primary Record.
Each line with Criteria 2 in [Value] is designated the Secondary Record and must be linked back to the first Primary Record above it.
I need to create a new column that will link the Secondary Records to the relevant Primary Record. Unfortunately since the number of Secondary Records per Primary Record varies, we can't just choose every other row.
The data received in the above specific order so that one can eyeball which ones are related. However, due to the amount of data it is no longer practical to do this and we are moving to a searchable Access database with more complex functionality.
I'm looking for a formula for Access (probably VBA) that will enable Microsoft Access to detect these relationships itself.
I apologise for the mash-mash of styles used below, as I don't know much about SQL or VBA, so the best way I can express it for illustration purposes is based on the structure of a nested IF query in Excel.
The below is checking upward for the next row that has a [Value] of "Criteria1" and records the ID number of that row in the new column.
=IF (Row5 [Value] = "Criteria1"), "Row 5 [ID]",
=IF (AND(Row5 [Value] = "Criteria2", Row4 [Value] = "Criteria1"), "Row4 [ID]",
=IF (AND(Row5 [Value] = "Criteria2", Row4 [Value] = "Criteria2", Row3 [Value] = "Criteria1"), "Row3 [ID]",
=IF (AND(Row5 [Value] = "Criteria2", Row4 [Value] = "Criteria2", Row3 [Value] = "Criteria2", Row2 [Value] = "Criteria1"), "Row2 [ID]",
=IF (AND(Row5 [Value] = "Criteria2", Row4 [Value] = "Criteria2", Row3 [Value] = "Criteria2", Row2 [Value] = "Criteria2", Row1 [Value] = "Criteria1"), "Row1 [ID]",
Another way to explain it might be:
If
Row [Value] = "Criteria1", [Linked Column] = Row [ID]
End If
If
If Row [Value] = "Criteria2" AND Row-1 = "Criteria1", [Linked Column] = Row-1 [ID]
End If
If
If Row [Value] = "Criteria2" AND Row-1 = "Criteria2" AND Row-2 = "Criteria1", [Linked Column] = Row-2 [ID]
End If
If
If Row [Value] = "Criteria2" AND Row-1 = "Criteria2" AND Row-2 = "Criteria2" AND Row-3 = "Criteria1", [Linked Column] = Row-3 [ID]
End If
If
If Row [Value] = "Criteria2" AND Row-1 = "Criteria2" AND Row-2 = "Criteria2" AND Row-3 = "Criteria2" AND Row-4 = "Criteria1", [Linked Column] = Row-4 [ID]
End If
I could really use some help on figuring out the right terminology / code / expressions / operators to achieve the above. I do realise this data is set up in a completely rubbish fashion but I can't change this, and while I am currently easily able to calculate these values in Excel, the data is unable to be used properly in Excel and needs a database. I need this to be as quick and easy as possible for my staff, so I can't have them pasting into Excel and then uploading the Excel into Access - I've trialled this and there's too many ways things can / do go wrong with different people handling the data each day.
Any help with the above solution would be very, very appreciated. It's all we can do at the moment to make this work until we can get better data.
5 Replies
- As a general observation, it appears that the heart of the problem is recognizing that Access, being a relational database application, and Excel, being a spreadsheet application, are fundamentally different. The way you handle data in a relational database is, therefore, not the way you have to do it with Excel.
As Arnel has demonstrated, you have TWO tables in Access because you have related data of two types. You have, unfortunately, obscured the process by resorting to aliases (criteria1 and criteria2), making it harder to understand the actual requirement, but there is enough there for an experienced developer to guess at what is really going on. Because the first set of records each seems to be related to one or more record in the second set, that tells us you have what is known as a One-to-Many relationship, which is the basis for a large percentage of situations in a relational database application.George_Hepworth Thanks George, I appreciate your POV. Unfortunately it's not provided to us as 2 tables, it's provided as one. The crux of the issue, unfortunately. We need a way to verify which record are related in order to turn it back into 2 tables for usability in Access.
Jac_C You missed my point. It is delivered as one table, so it's up to you to create the proper two tables. You also added more information in responding to Arnel. I noted that by using aliases you obscured the problem, which is made clear by that response regarding those values. That's a common occurrence when people start posting their questions. They try to simplify the problem by aliasing data and leaving out details. That has the exact opposite effect. It leaves us guessing about what's really going on.
I suspect Arnel will have a VBA function to read the Excel file line by line using a recordset to identify those changes that indicate record changes.
To help him do that, though, you should provide REAL samples of REAL values so the guesswork is minimized.
arnel_gp Hi! Thank you so much for trying to help! Unfortunately the Value Column doesn't contain the same value in the Primary and Secondary lines, so I'm looking add a field that creates that using a Module in Access, e.g., I'm trying to find a way to calculate the NEW CALCULATION column below in Access. The only way that we know that these fields are related is the order in which they appear (each Primary record has a "*" in the Value and the ones directly under it (until the next *) are the Secondary records. Does this make sense?
