Query text within a table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am creating a database which allows me to track racing information on
pigeons. I have a table for bird information (contains a unique bird
identifyer), races, weather condition, wind direction etc, and another table
for race information. The race information table is entered via a form. On
this form (and in the table) are repetitive type fields like - 1st Bird
identifyer, 1st bird position, 1st bird time in, then again for the second
bird - 2nd Bird identifyer, 2nd bird position, 2nd bird time in, and so on
for 30 birds. The data entered on the form fills into the table ok.
OK - the problem. I want to creat a query that will allow me to select a
bird identifyier and return all the races it has entered and its position in
that race. Although all the data I want is in one table, I can't seem to
figure out how the search will look at all the races line by line and find
all the positions for a single bird. Hope that makes sense. Can you offer
any advice.
 
You could create a form with an unbound field (FindBird). Base your form off
a query (which includes the race table). On the criteria line, under 1st
Bird, point to the FindBird field. Example: Forms!frmRaceInquiry!FindBird.
Then on the next criteria line, put the same field reference under the 2nd
Bird field. Make sure it is on the second criteria line since that will
create the "or" in your SQL statement. Continue in the same format for the
rest of the places. Be sure to include all needed fields in the query.
Then, on the After Update event of your FindBird field, put:

Me.Requery

Hope this helps.
 
In your query design, use both tables. Join the two tables with the Bird ID
field. It should be a one-to-many; that is the Bird Table is the "one" and
the Race Table is the "many". Select the data from the Bird Table that you'd
like to have in the query and drop it into the query design grid. Select the
data from the race table and drop that into the query design grid. You can
then sort or use criteria to find the exact data you are looking for.

One other thing that I would change is take the dates out of the Bird Table
as they don't have any relationship to the bird directly. They do have a
relationship to the Race Table however.

Bird Table:

BirdID
BirdColor
BirdWeight
BirdHatSize

Race Table:

BirdID
RaceDate
RaceWeather
RaceLocation
BirdPositionStart
BirdPositionFinish

Your Entry Form will be about the Bird, make a subform from the Race Table.
With the Wizards in Access you should be able to easily link them on the main
form. When you enter data in the main form, it will only go into the Bird
Table, when you enter data in the subform, it will only go in the Race Table.
 
Back
Top