Microsoft Access Question

[gziegelmeyer]

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?

[#2]

Hi Gary, hope all is well.

I am trying to replicate the database structure you've mentioned but would appreciate some more clarity, is this correct?...

2 tables, one containing "Category" as primary key and another "Location" field.
One containing "BlackOutDate" as primary key and another "Category" field.
You've linked "Category" fields together with a one-to-many relationship.

I don't believe this is your setup but would love to know more so I can replicate and help :)

Chris

[#3]

@chrisgrahamdack 

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.

[#4]

Thanks for the PNG, really helpful. It doesn't appear to me that the one-to-many relationship has been formed as intended upon initial glance. However, I will replicate it with less fields in the "Store Master" table and see if I can help with a solution :)

I will be back in touch ASAP.

[#5]

By "Category" I assume you mean "GEO Code" in this instance?

[#6]

Yes Category = Geo Code. And yes it may not have been set up properly :) I have worked almost any job you can think of in IT, but never a DBA.

[#7]

No worries whatsoever, OK I look forward to seeing if I can help out here :)

[#8]

Hi once more, I've put a meaningful structure together but have a feeling you're wanting to see which applicable stores assigned to the GEO Code in question are not blacked out on the chosen date, more than just the GEO Code itself which my structure aligns to?
It is coming up to 2am over here in the UK so I look forward to your response and will look at this again ASAP as I better get to bed! All the best for now :)

[#9]

Correct. I need all rows (stores) associated with a GEO Code that are not blacked out for a given date. Thanks again

[#10]

Hi @gziegelmeyer I believe I've cracked it for you #fingerscrossed 

[#11]

For the purposes of explaining a potential answer in this thread this is what I did on my database reconstruction...

• I created three tables tblGEOCodes, tblStoreMasterOLD and tblBODates.  The table tblGEOCodes are where each location/category are stored for subsequent assigning in other tables.  As each category in this table is unique I simply created one field as the primary key.
• One to many relationships were formed from the tblGEOCodes listing/table to the appropriately linked fields in tblStoreMasterOLD and tblBODates.
• Created a query called qryBODateStores which showed all stores which correspond to the BO Date entered in the query itself.
• Created an unmatched query (using the Access Query Wizard) to show all store details from tblStoreMasterOLD which haven't appeared in the query above, therefore providing you with the answer specifically for the date in question entered in qryBODateStores.

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

[#12]

@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.