multiple dates fields (1 Viewer)

[SLI]

I have a DB that consists of Movie titles and the multiple dates ( as many as 10) on which they will be used in the coming year.
I built a flat table with 10 date fields.
Then tried to build a relational table with just movie title and dates linked to ID.

I cannot work out an ability to SEARCH :banghead:the Database for a SPECIFIC DATE and get returned a listing of ALL Movie titles that will air on that date,
Can someone help me design this?

I am a slightly better than beginner user of Access.
Thanks
SLI

 

[AlexN]

You need to create two tables : tblMovies, and tblDates with a one-to-many relationship on a field DateID that would exist on both tables.
Then create a query from both tables.
This would normalize the structure of your database, and put you in the right path for accomplishing your mission.
Any further assistance needed, don’t hesitate.

 

[SLI]

Thanks Alex.
I guess I did not create the relationships correctly.
Main table
AUTO ID
1 Shane Warner Bros
2. ET Luca Films
3. Help Viacom
4 Shaft Warner Bros

AUTO ID movie air dates1 air date 2 air date 3
1 Shane 1/15/15 2/15/15 3/15/15
2 ET 12/24/14 12/25/14 1/15/15
3 HELP 12/24 14 1/15/15 4/15/15
4 Shaft 2/15/15 2/16/15 2/17/15

I joined them by AUTO ID But still have the problem of the Dates are in different columns and I do not know how to create a query that will give me the moves airing on 1/15/15
I use the or criteria in the query but that only gives me what is in a specific air date column, thus not including ALL the air date columns.

I guess I have built myself into a corner here. I may not understand the one to many relationships This database thing iis frustrating and yet also fun.
If you have time and can help, I would be grateful.

SLI from Chicago

 

[CJ_London]

your dates need to be one per record

Main table
MovieID MovieName Distributor
1 Shane Warner Bros
2. ET Luca Films
3. Help Viacom
4 Shaft Warner Bros

AirID movieID airDate
1 1 1/15/15
2 1 2/15/15
3 1 3/15/15
4 2 12/24/14
5 2 12/25/14
6 2 1/15/15

 

[SLI]

thanks for the quick reply I need to ponder how to do that.

sli

 

[AlexN]

Hope this can show you the way.
Try opening the qry

 

[SLI]

YAHOO!!!! That works. I was making the relationship in the wrong direction!
My first day on the forum has been wonderful. You ROCK! and you are KIND TOO! Taking the time to really SHOW me how it could work was thoughtful .
sli

 

[jdraw]

Follow CJ's example. Store Dates as Date/Time datatype.
You don't need 3 tables, 2 as per CJ should be just fine.
Good luck.

@AlexN
Don't need 3 tables
Dates should be stored in Date/Time datatype

 

[AlexN]

Follow CJ's example. Store Dates as Date/Time datatype.
You don't need 3 tables, 2 as per CJ should be just fine.
Good luck.

@AlexN
Don't need 3 tables
Dates should be stored in Date/Time datatype

Apparently you're right. My design idea is too elementary.

 

[jdraw]

Your idea was good. You just don't need the separate Dates table and that removes the need for junction table.

In this case 1 movie title could be shown on Many Dates, so a 1 to Many -- 2 tables

If you have Students and Classes where
1 student could take many Classes, and
1 Class could be attended by Many Students -- a Many to Many, then a third table (junction table) would be needed.

Good luck with your projects.

 

[ntyler1]

Learned from this post. Thanks.