Forum Discussion
Link Rows within an Access Table or Query
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.