Solved Find Different Matches in Same Field (1 Viewer)

robsmith

New member
Local time
Today, 11:05
Joined
Feb 17, 2020
Messages
26
Hi,

I have a table called EventData containing various fields including StartDay and Events. Some Events have multiple StartDays each of which are separate records.

Is it possible to run a query to return records that match a specified start day (eg Monday) but also have at least one other different start day? So, for example, if an Event only had a start day of Monday it would be excluded but if it had a start day of Monday and Tuesday it would be included.

I thought it would be easy but I can't work out how to do it.

Thanks
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:05
Joined
Oct 29, 2018
Messages
21,357
Hi. Probably easier to come up with something if we had some data to play with. Are you able to share a sample copy of your db with test data?
 

robsmith

New member
Local time
Today, 11:05
Joined
Feb 17, 2020
Messages
26
Hi DBguy, I've attached a small extract. There are 3 Events, two of which have multiple StartDays and one has only one StartDay. Thanks
 

Attachments

  • Database1.accdb
    424 KB · Views: 105

theDBguy

I’m here to help
Staff member
Local time
Today, 04:05
Joined
Oct 29, 2018
Messages
21,357
Hi DBguy, I've attached a small extract. There are 3 Events, two of which have multiple StartDays and one has only one StartDay. Thanks
Hi. Thanks. Now, can you give us a mock up of the result you want out of it?

If I do a simple Totals query, I get this. Is that correct?
1604519096998.png
 

robsmith

New member
Local time
Today, 11:05
Joined
Feb 17, 2020
Messages
26
Hi DBguy, I just want to return a list of the records with multiple StartDays. So in the extract it would be all records apart from the one with the Event starting with 5. Thanks
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:05
Joined
Oct 29, 2018
Messages
21,357
Hi DBguy, I just want to return a list of the records with multiple StartDays. So in the extract it would be all records apart from the one with the Event starting with 5. Thanks
In that case, try this query.
SQL:
SELECT *
FROM EventData
  WHERE EventData.[Event] In (SELECT EventData.Event
    FROM EventData
      GROUP BY EventData.Event
        HAVING Count(EventData.StartDay)>1);
Hope that helps...
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 19:05
Joined
May 7, 2009
Messages
19,169
check this sample form and the query1.
 

Attachments

  • Database1.accdb
    464 KB · Views: 116

robsmith

New member
Local time
Today, 11:05
Joined
Feb 17, 2020
Messages
26
Hi Arnelgp, thanks for this. I've tested it but it seems to be returning results where there is only one StartDay. Eg. in the attached if you select Monday and filter for Event 4451 you'll see the issue.
 

Attachments

  • Database1.accdb
    1.4 MB · Views: 112

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 19:05
Joined
May 7, 2009
Messages
19,169
when i view the Table, Event 4451 only has Mondays.

see Event 298438, it has Monday, Friday and Saturday.
 

robsmith

New member
Local time
Today, 11:05
Joined
Feb 17, 2020
Messages
26
when i view the Table, Event 4451 only has Mondays.
Hi Arnelgp, yes and it is being returned by the query. I want to exclude Events that only have one StartDay, even if it is the day selected in the dropdown. Thanks
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 19:05
Joined
May 7, 2009
Messages
19,169
here i added 2 more query.
 

Attachments

  • Database1 (1).accdb
    1.4 MB · Views: 111

robsmith

New member
Local time
Today, 11:05
Joined
Feb 17, 2020
Messages
26
Hi arnelgp, this is perfect. Thank you so much for all you help.
 

Users who are viewing this thread

Top Bottom