Query that searches a table and ask user for input depending on what it finds. (1 Viewer)

[JahJr]

I have a table with the following columns
Type, Sport1, Team1, Score1, Sport2, Team2, Score2, Sport3, Team3, Score3, Sport4, Team4, Score4

If Type = 1, 2, or 3
Then there will only be information in Sport1 and Team1
If Type = 4 or 7
Then there will only be information in Sport1, Team1, Sport2 and Team2
If Type = 5 or 8
Then there will only be information in Sport1, Team1, Sport2, Team2, Sport3, and Team3
If Type = 6
Then there will be information in all of the fields except the score fields.

I need a form with a query that will search the table and report back all of the sport and teams that are in the field, and then allow me to input the score. Of which would be posted back to the table in all the appropriate score fields.
The sport column is important because for example the eagles have a basketball and a football team.
There can be multiple entries that are the same so it will only need to return the information once for all of the duplicates.
I have searched the forum and internet and can not get my, evidently, small brain around were I even need to begin. Any help would be greatly appreciated.

 

[Guus2005]

What you really need is a normalised database.
Take a few steps back and decide what you want to do.

You should have a table with Sport, a table with Team, etc. Each with it own fields.

Having said that, you can create a query that will give you a record. You will receive all fields and based on the Type number certain fields are used and others not.

My advice to you is: Normalize! Check Wikipedia if you're not sure what it is...

Enjoy!

 

[MSAccessRookie]

Thank you for your response but let me explain a little more about the db. It is for logging sports bets. For example if your on the "Line Bet" form then:
1. Select customer from combo box
2. Select sport from combo box
3. Select Team Name from combo box
4. Enter the line into a text box
5. Click "Log Bet"
6. All of this informaiton is logged into the table logged bets.

There is a primary key "Bet#" in the table that is set to "auto number" because customers can place multiple bets.
Please give me an idea how you would normalize this db.

It sounds like you are thinking of this like a betting sheet instead of a database. I imagine your business equation to be similar to the following:

The basic Unit is the Customer. Each Customer can make one or more Bets. Each Bet can be related to One or more Event (Game). Each Event has One or more Teams to bet on.
​

Each of the words in RED represents an independent data group and needs its own table (See Below).

• Queries to collect, modify and add information to the tables will be needed.
• Forms based on the queries will be needed to allow the data to be entered.
• Reports based on the queries will be needed if receipts for the bets that were placed are required.

This is just the beginning, but the approach is a more normalized one.

tblCustomer
    CustomerID
    FirstName
    LastName
    {Any other important Customer related information} 
 
tblBets
    BetID
    CustomerID
    BetType
    BetAmount
    BetDate
    {Any other important Bet related information} 
 
tblEventsEventID
    EventID
    EventDate
    TeamID
    {Any other important Event related information} 
 
tblTeams
    TeamID
    TeamName
    {Any other important Team related information}