CODEWITHSUNDEEP
sqlms-access
MZimbric
MZimbric

Reputation: 45

Storing data about objects with a variable number of ordered subparts in Access Database

The situation: I have a database storing biological specimen data. One table contains data about each specimen. Each specimen has between 1 and 8 parts, which are ordered.

I would like to enumerate each subpart in a query, using the specimen id and the number of parts. So if I have 2 specimens, A and B, and A has 2 parts and B has 3 parts, I want the result:

Parts:

A - 1

A - 2

B - 1

B - 2

B - 3

I realize that this is probably a trivial task, but I don't know the correct terminology to talk about it in a way that help pages and Google will understand. Thank you.

Edit to add thoughts: If I were dealing with something like this in a non-SQL context, I'd use a for loop to iterate the enumeration process over each specimen, but I don't understand how to implement anything remotely similar in SQL.

Upvotes: 1

Views: 29

Answers (1)

Caius Jard
Caius Jard

Reputation: 74660

You mentioned "main table" which implies there's some other table for the sub parts. What you're after is likely a simple JOIN:

SELECT
  *
FROM
  maintable
  INNER JOIN
  subtable
  ON
    subtable.mainid = maintable.id

If you want an exact query, post a screenshot of your database tables and their column names and any relationships

Upvotes: 1

Related Questions