How do I construct this MS Access Query (or SQL query)

[Clariman]

As you may recall I've created a database with 3 tables to analyse data about some 1000-1500 year old artefacts. The data is stored in MS Access which is supporting my queries pretty well. However, I have a mental block about this one. Can someone put me out of my misery?

It only involves one table which has many columns but I'm only concerned with Column A and B, neither of which have unique values. I want to create a query that does the following:

• Search Column B for value "X"
• For each matching row I want to find the equivalent value in Column A e.g. "Y"
• I then want to search Column A for that value ("Y") and display a list of all rows that contain it
• Note that there will be multiple groupings because more than one Column B row will have value "X"

Does that make sense?
I really hope my table design supports this, otherwise I have messed up a bit!

Thanks
C

[uspaul666]

select B.*
from
MY_TABLE A,
MY_TABLE B
where
A.B = ‘X’
and A.A = B.A

Probably.
Might need a distinct in there.

[Alaric]

I want to create a query that does the following:

You could use the "create view" method. This builds a virtual table, so you first isolate only those rows which contain the desired value. You then apply your search query to the view.

[ReformedCharacter]

As you may recall I've created a database with 3 tables to analyse data about some 1000-1500 year old artefacts. The data is stored in MS Access which is supporting my queries pretty well. However, I have a mental block about this one. Can someone put me out of my misery?

It only involves one table which has many columns but I'm only concerned with Column A and B, neither of which have unique values. I want to create a query that does the following:

• Search Column B for value "X"
• For each matching row I want to find the equivalent value in Column A e.g. "Y"
• I then want to search Column A for that value ("Y") and display a list of all rows that contain it
• Note that there will be multiple groupings because more than one Column B row will have value "X"

Does that make sense?
I really hope my table design supports this, otherwise I have messed up a bit!

Thanks
C

Its a long time since I've done any Access work but isn't that the same as something like:

SELECT *
FROM YourTable
WHERE Column B = 'X' AND Column A = 'Y'

I may have misunderstood the intent of your query though.

RC

[Clariman]

Its a long time since I've done any Access work but isn't that the same as something like:

SELECT *
FROM YourTable
WHERE Column B = 'X' AND Column A = 'Y'

I may have misunderstood the intent of your query though.

RC

You've misunderstood my query, but I probably didn't explain it well! Your query is too restrictive. I want to find an instance of where Column B="X", pick up the corresponding value in Column A ("Y") in that row and then show all rows with that value in Column A.

It may be clearer if I say what the columns are. The artefacts that I am researching have a common set of designs on them (but unique to this culture). I am exploring how these designs are grouped. Each row in my table lists an individual occurrence of a design (Column A) and links it to the Artefact-ID (Column B). So I want to search for artefacts that have a specific design and then show all the other designs on each of those artefacts.

C

[tikunetih]

The query you're describing suggests your database "design" is probably v dodgy, and you haven't properly normalised the data...

I've never used Access, but did once long ago design databases for a living, and I *think* that what you're trying to achieve conceptually is probably this:

"Return all rows that have a value in Column A that is also in the set of values for Column A of all rows that have a value of "X" in Column B."

ie.

SELECT *
FROM my_tab
WHERE col_a IN
(SELECT col_a
FROM my_tab
WHERE col_b = "X")


NB You could do it via a join along the lines of what uspaul666 suggests, but as he mentions you'd likely need a DISTINCT in the SELECT phrase to replicate what the IN is achieving here.

SQL statements are usually conceptually easy to write *IF* you've correctly normalised your data giving you a proper table design. In this case, because the SQL is unintuitive, you almost certainly haven't normalised the design, and your Column A should likely broken out into a separate table.

If you gave the names of the actual data items rather than it being abstract names it would probably become quite clear.

[ReformedCharacter]

Its a long time since I've done any Access work but isn't that the same as something like:

SELECT *
FROM YourTable
WHERE Column B = 'X' AND Column A = 'Y'

I may have misunderstood the intent of your query though.

RC

You've misunderstood my query, but I probably didn't explain it well! Your query is too restrictive. I want to find an instance of where Column B="X", pick up the corresponding value in Column A ("Y") in that row and then show all rows with that value in Column A.

It may be clearer if I say what the columns are. The artefacts that I am researching have a common set of designs on them (but unique to this culture). I am exploring how these designs are grouped. Each row in my table lists an individual occurrence of a design (Column A) and links it to the Artefact-ID (Column B). So I want to search for artefacts that have a specific design and then show all the other designs on each of those artefacts.

C

Right, I think I understand better now. It sounds as if you have a list of artefacts and that each artefact has a number of designs. Although there are doubtless different ways of achieving this I think that one would normally have one table for artefacts and another for artefact designs, a one to many relationship. Something like this:

https://www.lifewire.com/one-to-many-re ... ps-1019756

RC

[Clariman]

The query you're describing suggests your database "design" is probably v dodgy, and you haven't properly normalised the data...

SQL statements are usually conceptually easy to write *IF* you've correctly normalised your data giving you a proper table design. In this case, because the SQL is unintuitive, you almost certainly haven't normalised the design, and your Column A should likely broken out into a separate table.

If you gave the names of the actual data items rather than it being abstract names it would probably become quite clear.

I have 3 tables:

• Artefact Table, including a unique artefact ID, and lots of general information about its type, dimensions, location etc.
• Designs Table, including a unique design ID, and other information about how different scholars have named the design etc.
• Occurrences Table, one row for each appearance of a design on an artefact, so including the design-ID, artefact-ID and other information on its position relative to others and its style

So I want to search the Occurrences table to find which artefacts contain a design - and I want to see all the other designs on the same artefact.

Does that help? If there is a better table design, then I could maybe rework it if need be. I'd prefer not to, but if it is worth the effort to make queries easier I would consider it. I might not change it now, but in the long term I will probably make my data available online for others to study.

Thanks
C

[tikunetih]

OK, clearer now - you have a many-to-many relationship between Artefacts and Designs, implemented by the intersection table Occurrences. Fair enough, normalised correctly in that sense!

If so, the solutions remain as above, ie. uspaul666's join statement or the IN subquery method, with your Occurrences table being the subject in each example, and an ORDER BY clause to sensibly order the results (eg. by ArtifactID, DesignID).

[Nocton]

Why don't you use the built-in Query Designer/wizard? It should handle this. In any case it is always useful to get the 'basic' SQL code which can then be tweaked manually if required.

[Wmnr]

Hi clariman

Database design looks fine

Using an In clause

Select *
From occurences,design
Where occurences.designid=design.designid
And artefactid in (
Select artefactid
From occurences,design
Where occurences.designid=design.designid
And designname='text')

Or using a sub query

Select *
From occurences,design,
(
Select artefactid
From occurences,design
Where occurences.designid=design.designid
And designname='text') sql1
Where occurences.designid=design.designid
And occurrences.artifactid=sql1.artifactid

Or you could create one query to find the artefacts and then include that query like a table in a second query

Select artefactid
From occurences,design
Where occurences.designid=design.designid
And designname='text'

Select *
From occurences,design, myquery
Where occurences.designid=design.designid
And occurrences.artifactid=myquery.artifactid

I've used the old fashioned way of joining tables but you can use Inner Joins if you prefer.

Not near a pc right now so it's all untested