Missing Something Easy (I hope)

[P3ndrag0n]

Table Structure:

tblUpgradeDate

AVDate text
BoardDate text
SeatingDate text
TableDate text

In each of the above the user can input one of the following:

Summer 2015
Spring 2015
Winter 2015


Spring 2016
Summer 2016
Winter 2016
(so on and so forth through 2020.)

I want to produce a report showing me all entries by whatever time frame I enter.

If I enter Summer 2016 at a prompt I want it to return ALL entries regardless of column with Summer 2016.

I know I can filter individual columns (and query individual columns) but I need to quickly answer the question: "Tell me every upgrade we have coming up for Spring 2016"

Is there a way to query an entire table for a text string (Summer 2016) to return all results with Summer 2016 in it? Or am I way off base? I really feel like I'm over-thinking this.

[pbaldy]

Well, your table isn't normalized, which makes it harder. That said, you can create a query and put the criteria under the date fields, each on a separate criteria line. That will create an "OR" criteria and should do what you want.

[P3ndrag0n]

Hmm let me give a little more info I neglected to input before so maybe its a little clearer. Actual table structure is as follows:

tblUpgradeDate

ID (PK) (autonumber)
Building (text)
Room (text)
AVDate text
BoardDate text
SeatingDate text
TableDate text

Every single building and room will only ever have 1 Avdate or 1 BoardDate or 1 SeatingDate etc etc. Unless I'm way off there's no way to further normalize this particular table?

[ssanfu]

PMFJI Paul,

P3ndrag0n,
Well, yes the table can be normalized further. "AVDate", "BoardDate", etc are actually data as field names.
And what happens in a year when you (or someone) might want to add another date, say "GroupDate" (or whatever). Using your table structure, you would have to modify the table, queries, forms, reports and possibly code.

Consider:

tblBuildings
-----------------
BuildingsID (PK) (autonumber)
Building (text)
Room (text)

tblUpgrades
-----------------
UpgradesID (PK) (autonumber)
BuildingID_FK Long  (foreign key to buildings
UpgradeDate (text)    (values can be: Summer 2015, Spring 2015, Winter 2015 or  Winter 2016)
UpgradeType (text)  (values can be: AV, Board, Seating, Table)

Because there are so many values for "UpgradeDate", I would also have a (look up) table for Upgrade Dates.. Then the structure would be:

tblBuildings
-----------------
BuildingsID (PK) (autonumber)
Building (text)
Room (text)

tblUpgrades
-----------------
UpgradesID (PK) (autonumber)
BuildingID_FK Long  (foreign key to buildings)
UpgradeDateID_FK (Long)    (foreign key to tblUpgradeDates)
UpgradeType (text)  (values can be: AV, Board, Seating, Table)


tblUpgradeDates
-----------------
UpgradeDateID_PK (autonumber)
DateWhen (text)  (values can be: Summer 2015, Spring 2015, Winter 2015, Winter 2016......, Winter 2020)

This way, if there is no date for "Seating date" or "Table date" there is not a record. All "dates" are in one field.... searching is Soooo much easier.


My $0.02..............

[P3ndrag0n]

Steve,

I gotta say it makes a lot of sense. It never dawned on me that the blank fields would register as records therefore keeping me from querying the information I was looking for. I'm going to try breaking this out into separate tables (I did this for a completly differnt set of "Upgrade Dates") and see if this gets me where I want to be. Going to marked as answered.

Thanks!

[abeha]

Is there a way to query an entire table for a text string (Summer 2016) to return all results with Summer 2016 in it? Or am I way off base? I really feel like I'm over-thinking this.

__________________
emma

[pbaldy]

Same answer as post 2.