Querying linked tables

[atbm]

Hi and thanks for reading.



I work for a company which puts up posters for events in lots of different shops etc around my city. Every day I choose which posters should be put up I am trying to make a database I can query to see which posters have not been put up recently so need to be sent out now. I have a database with two tables: Posters, a list of all the posters and their ID number. The ID number can be shared by several posters and the names are all unique so the name is the primary key.

The other table, PosterDays, is has the fields ID (autonumber primary key), Day (which is the date a poster was put up on) and Poster (the name of the poster). Each record is one poster put up on one day. The Poster field is looked up from Posters.Poster_Name so there is a 1:many relationship.

I want to search for all the Posters in Poster_Name which have not been put up recently or even at all. How can I do this?

[aytee111]

Do not use the poster name as the primary key as this can change (due to spelling mistakes, etc.) - make an autonumber field such as PosterID and use this as the PK.

Day is a reserved word, change it to PutUpDate or something.

Why would an ID number be shared by multiple posters? It seems that you need an additional table (category?) so that multiple posters can link to one of those.

The query will count the number of days since each poster was last put up, such as DaysSincePutup: Date() - Nz(PutUpDate)

[atbm]

Day is not reserved - maybe you mean date? It's a reserved word.

I definitely am happy using the poster name as the primary key . I have already considered what you say and the name does not change.. Due to our process for tagging poster photos, etc, it's much easier.

The ID number is for the order - if a customer orders two kinds of posters they have the same ID. Creating an ID field needlessly would be unhelpful.

I'm not sure, but I think your suggestion for the query works - thank you! It doesn't show posters that haven't been put out yet though. Is there a way to search one table for values in another table and display those not found?

Thank you.

[ssanfu]

"Day" is also a reserved word because it is the name of a built in function.

[Micron]

a list of bad words, where, as noted, you will find "Day" but I post it here for its ongoing usefulness.
http://allenbrowne.com/AppIssueBadWord.html

[ssanfu]

Now why didn't I think of posting the link???

@ Micron ...