Two Date/Time and Memo Fields

[Luke]

I am done with work for the summer in 2 days and my bosses would like me to figure this out for them ASAP. Here's the rundown:

I have a table called tblEntity with the following fields:

• EntityID: primary key, Autonumber
• EntityName: Text

Another table called tblReviews with:

• ReviewID: primary key, Autonumber
• EntityID: Number
• ReviewDate: Date/Time
• Review: Memo

And another table called tblNotes with:

• NoteID: primary key, Autonumber
• EntityID: Number
• NoteDate: Date/Time
• Note: Memo

The EntityID Number fields in my Reviews and Notes tables are linked to the primary key EntityID in my Entity Table. (enforced with referential integrity). This is so I can have multiple reviews and notes for the same EntityName.

Basically the tblReviews and the tblNotes are the exact same thing except "Notes" and "Reviews" are used for different things in the company I work for. I have created a Report with: EntityName, ReviewDate, Review, NoteDate, and Note.

The problem is that when I write a "Review" for a company but don't write a "Note", the review doesn't show up at all, and vice versa. The company only shows up in the report if there is both a "Review" AND a "Note" written about it. I want the Entity to show up if EITHER a review or note has been written about it, not just BOTH.

Does anybody know how to solve this? Thanks a lot.

-Luke

[LillMcGill]

Have you looked at the join type in the query?

[Luke]

Have you looked at the join type in the query?

I'm not exactly sure what you mean when you say "join type"?

[rpeare]

Your databse is not normalized that's why you're having trouble. You should really have a single table that has your NOTES and REVIEWS, and just add a last field to the table that indicates whether the item is a note or review.

To your problem though, you likely will have to use a UNION query.

What I would do in this situation is have a union query just for the memo information then link that query back to your ENTITY table in a second query

so you'd have

SELECT EntityID, ReviewDate as ItemDate, Memo as ItemText, "Memo" as NoteType FROM tblReviews
UNION ALL
SELECT EntityID, NoteDate as ItemDate, Note as ItemText, "Note" as NoteType FROM tblNotes

This will 'normalize' your data that's coming from two different tables you can then create a new query with your tblentities and this union query to give you all the memo documents for that entity.