Solved Find Different Matches in Same Field

[robsmith]

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]

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]

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

 

[theDBguy]

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?

 

[robsmith]

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]

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.

SELECT *
FROM EventData
  WHERE EventData.[Event] In (SELECT EventData.Event
    FROM EventData
      GROUP BY EventData.Event
        HAVING Count(EventData.StartDay)>1);

Hope that helps...

 

[robsmith]

Thanks DBguy. How to do I specify a StartDay?

 

[arnelgp]

check this sample form and the query1.

 

[robsmith]

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.

 

[arnelgp]

when i view the Table, Event 4451 only has Mondays.

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

 

[robsmith]

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]

here i added 2 more query.

 

[robsmith]

Hi arnelgp, this is perfect. Thank you so much for all you help.