expression

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

Guest

A very simple database, main table and a table that will store posible
choices (2) for one of the fields in the Main (combo - look up column,
possible choices here are EXIT and ENTRY). What I want a query to do is show
me only entries from the Main table that have both EXIT and ENTRY selected.
How do I write a valid expression??? Thank you in advance...
 
You need to explain a little more what you want. From you description, I
would think that you have one field to store Exit or Entry. So I'm guessing
that you have two records that are similar except for one may have Entry and
another may have Exit and you want to find the matched pairs.

You need to give us a little help. If the scenario I postulated is
accurate.

Can you post the SQL text of a query that will give you all the data you
want if the field is equal to "Entry"? In addition, can you tell us which
fields would be used to match an Entry against an Exit.
 
Thanks for your reply John!

Ok, I have a Main table (Main) with book titles (Title) and another field
(Exit/Entry)in that table that is linked to another table (In/Out). I want to
make a query, report whatever that will help me show ONLY book titles that
were entered at least twice with both ENTRY and EXIT
Example:

BookID Title Date Time In/Out

1 Doors of Perception 27/02/06 08:30 EXIT
1 Doors of Perception 27/02/06 08:30 ENTRY
2 The Perfect Storm 31/01/06 08:30 EXIT
3 L’etranger 12/02/06 08:30 EXIT
3 L’etranger 25/03/06 08:30 EXIT

So I would only need record with ID no 1 in the above example.

Regards,
 
You didn't give me a sample query.
You didn't tell me which fields your need to use to determine whether Exit
and Entry both occur. For example do Entry and Exit both have to be on the
same day.
Do you want both records returned (the entry record and the exit record) and
the other information associated with each record.


The following should return one row from your sample data .

SELECT Main.*
FROM Main
WHERE [In/Out] = "Entry" AND
Exists(Select TEMP.* FROM Main as Temp
WHERE Temp.BookID = Main.BookID
and Temp.[In/Out] = "Exit")

That should return this row from your sample data
1 Doors of Perception 27/02/06 08:30 ENTRY
 
Back
Top