May 27 2021 09:29 AM
Hi,
I am new to MS Access but have used other MS products for decades. I am trying to figure out a solution to this problem. I have 5 categories (with 1 to many locations associated to a category). Each category can have 1 to many black out dates. I want to run a query with a date to see which category(s) are not blacked out on that date. So far i created 2 tables. 1 has the categories and location and the 2nd has the Black Out date(s) by category. I linked the category between the 2 tables. When i run the query and say black out date not equal a given date, it doesn't show that row for that date, but does show it if there is another black out date associated with that category. I think it has to do with the 1 to many relationship. Anyone have an idea on how to solve this? Or is there a better way to approach it?
May 29 2021 06:30 AM
May 29 2021 08:02 AM
Thanks for taking a look at this!!
I added a screen shot of the tables. There can be 6 categories with multiple rows per category. There can also be multiple blackout dates per category. I want to be able to do a query on a given date and give me back all rows (for a category(s)) that are Not blacked out on that date. When I did a query and put Not (specified date), it returns the row that has multiple black out dates, but with a different blackout date than specified. Example if category 1 has a blackout date of 6/1/21 and 12/13/21, it will still return the row with the 12/13/21 blackout date.
I am not sure these tables are set up the most efficient way either. Let me know if this helps.
May 29 2021 05:17 PM
May 29 2021 05:20 PM
May 29 2021 05:29 PM
May 29 2021 05:30 PM
May 29 2021 05:57 PM
May 29 2021 06:06 PM
May 30 2021 03:57 PM
Hi @gziegelmeyer I believe I've cracked it for you #fingerscrossed
May 30 2021 04:14 PM
For the purposes of explaining a potential answer in this thread this is what I did on my database reconstruction...
I've attached a relationship diagram showing the structure. Notice I've checked the "Enforce Referential Integrity" box when setting the one-to-many relationships as one location/category (GEO Code) can appear multiple times in both corresponding tables.
I tried to attach my database but it isn't being allowed, happy to WeTransfer to you if desired, drop me a private message with your email address if so :)
I look forward to seeing if this has resolved your issue or any further feedback you wish to provide.
Chris
Jun 04 2021 01:34 PM
@gziegelmeyer Great discussion! I'm moving it to the Microsoft Access discussion space as the Tech Community Discussion space is reserved for discussion about the Tech Community website.