Hockey team query

cturton

Registered User.
Local time
Today, 11:22
Joined
Apr 13, 2000
Messages
10
I'm developing in Access 2002 a mdb for our hockey club that is a scheduler for teams and practice/game times. I need a query that will find the team name in multiple columns.

For instance....Team Name "Rangers" can be scheduled on a date and time where they are the only team on the ice that day. Their team name will appear in a column called Team-Full, which designates they are the only team on the ice that day.

On other dates, teams will share the ice, meaning Team Name Rangers will appear in a column called Team 1 while the ice will be shared with Team Name Ducks. So the user can now tell that on that date and time, two teams are sharing the ice for practice.

The flat file look like this:
Day date time type team full team 1 team 2
Saturday 11/01/02 6:30 AM full Rangers
Sunday 11/02/03 7:45 am split Rangers Ducks

So, I'd like write a query that 1. prompts the user for the team name 2. searches all columns....Full, Team 1 and Team 2 and displays the results so that the user knows all dates and times that a particular team will appear on the schedule.

Thanks in advance for your help
 
You can import this into a table, than write queries against it and break it up how you want, except it would appear the real issue is there is not a good delimiter in the file between team names.
Example:
Rangers Ducks (2 teams)
Falcons (1 team)
Mohawk Reds (1 or 2 teams, can use split or full to tell)
The Red Robins Round (if 2 teams, where does one end and another start)

So if a team has a space in the name, where does one end and the other start? This is your parsing problem.
Do you have a plan to over come this?
 
Good question

Thanks..I didn't make myself clear...I am working in a table called Master Schedule..so it looks like a spreadsheet (datasheet view) to the user when they use the data entry form, but there is a table structure behind the data.

Thanks for your clarification
 
Create a combobox that selects unique team names from your table (might need a UNION query to bring them into a single column). Allow your people to select a team name, than base your parameter query on the combobox to pull any rows with the team name in colA OR colB.
Most likely your best approach.

So, SELECT TeamCol1 as Team from MyTable where TeamCol1 is not null
UNION SELECT TeamCol2 from MyTable where TeamCol2 is not null

Or something close to that for the combobox
 
Thanks for the follow up

Great..thanks for the tip..I'm sure this will get me headed in the right direction!
 

Users who are viewing this thread

Back
Top Bottom