2

I have a table in Access that is set up where there are multiple records with the same ID, they correspond to each other.

I'd like to find certain records that have a specific date value. However, I want all the corresponding information WITH that ID (i.e. all the other records with the same ID). I've tried things like this:

SELECT * 
FROM myTable
WHERE LEFT(Field1,7) = '2016-11'  IN (SELECT ID 
                                      FROM myTable
                                      GROUP BY ID
                                      HAVING COUNT(*)>1)

and 

SELECT *
FROM myTable
WHERE ID = (SELECT * FROM myTable WHERE LEFT(Field1,7) = '2016-11'

Neither of these are giving me the proper output. I think I may need a For loop of some sort but don't have much experience doing this with SQL. That way I can loop through all IDs that are returned with that date-part. Any suggestions? I would put the table format in the post but the table formatting isn't working for me for some reason. The frustration is real!

Haha thanks ahead of time for taking the time to even read my question. Much appreciated.

EDIT

Here is a visual of what my table is like:

ExampleTable

I'd like to choose all the records that occur during November, but also get the corresponding information (i.e. records with same ID number as the November records).

Tianna Wrona
  • 342
  • 2
  • 14
  • Why the subquery: `SELECT * FROM myTable WHERE LEFT(Field1,7) = '2016-11'`? – Parfait Nov 15 '16 at 20:12
  • I'm trying to choose all the records that take place during the current month. However, that column is formatted as text since each value is not necessarily a date. In the end, I'd like to filter out all the records with a date of this month, and then retrieve all the other records that have the same ID as that one. – Tianna Wrona Nov 15 '16 at 20:15

1 Answers1

1

Consider adding WHERE condition in subquery:

SELECT * 
FROM myTable
WHERE ID IN (SELECT ID FROM myTable 
             WHERE LEFT(Field1, 7) = '2016-11');

Alternatively to avoid subquery, try an INNER JOIN on a filtered self join by ID:

SELECT myTable.* 
FROM myTable
INNER JOIN
  (SELECT ID FROM myTable 
   WHERE LEFT(Field1, 7) = '2016-11') sub
ON sub.ID = myTable.ID
Parfait
  • 104,375
  • 17
  • 94
  • 125
  • Unfortunately, that is still only giving me the records that have dates in them :/ I added a screenshot of what my table is sort-of formatted like. Maybe that will help. – Tianna Wrona Nov 15 '16 at 20:26
  • See edit. Also, you have an [entity/attribute value model](http://stackoverflow.com/questions/870808/entity-attribute-value-database-vs-strict-relational-model-ecommerce-question) which is not the best for a relational database. – Parfait Nov 15 '16 at 22:31
  • It's data that comes from an xml file and is imported as you saw. The reason I don't change it is simply because I am not sure if the number of fields per record will change. Columns could be added without my knowledge. If there is a way that we can pivot the table and still maintain the dynamics, I'd love to learn about it though. Also both methods above worked for me so thank you! – Tianna Wrona Nov 16 '16 at 15:03