Try changing it to a Left JOIN
I don't know why the IN is a problem for you. I use it regularly
--
Dave Hargis, Microsoft Access MVP
:
Little update:
Since last I posted I started playing with queries and came up with the
following queries which I think are getting me a step closer:
SELECT tblAction.Seat
FROM tblMain RIGHT JOIN tblAction ON tblMain.ID_Main = tblAction.Person
WHERE (((tblAction.Seat) Is Not Null) AND ((tblAction.Event)=4) AND
((tblMain.RefNum)="206CBR2985"));
This query (qryShowAllPeopleWithSeatEvents) returns the seat numbers
currently assigned in 206CBR2985. Next, I did this query:
SELECT tblSeat.Seat
FROM tblSeat RIGHT JOIN qryShowAllPeopleWithSeatEvents ON tblSeat.Seat <>
qryShowAllPeopleWithSeatEvents.Seat;
This query returns a peculuar list. In tblSeat.Seat there are 200 records
(1, 2, 3 ... 198, 199, 200). It is just that list of numbers corresponding
to the 200 seats. Right now, for testing purposes, I created a test set of
data of people in tblMain and then randomly chose them to assign to seats. I
assigned three people from tblMain to seats 1, 2, and 3 respectively. This
second query returns 597 records. Record 1 - 197 are seats 4 - 200, but
record 198 through 397 are seats 1 through 200 and record 398 - 597 are seats
1 through 200. I tried selecting distinct but that didn't get me there
either. I feel like this may be the way to proceed if I can just figure out
how to rewrite my sql to not query for each seat in both (which is what i
think is going on here, though I could be wrong). If I can figure out how to
get this query working I can next start working on determining the most
recent event for a given seat. Thanks for your help!
:
hhmmmm?
I don't know why that is happening. Try putting in parenthesis like this:
NOT IN (SELECT [Seat] FROM tblAction WHERE [Event] = Forms!frmAction!cmbEvent)
--
Dave Hargis, Microsoft Access MVP
:
I feel like I'm getting so close... Trouble is I got the following error:
Syntax error (missing operator) in query expression 'NOT IN SELECT [Seat]
FROM tblAction WHERE [Event] = Forms!frmAction!cmbEvent'.
When I click OK it leaves "IN" highlighted... so I wonder if that's where
the trouble lies. Thoughts? Thanks so much for your help on this.
:
No, I am referring to Event in tblAction. That would be to limit the seats
available to only those in the current event.
--
Dave Hargis, Microsoft Access MVP
:
I think I follow all of that... but I have one clarifying question... the
event number... are you refering to the seat number or the tblEvent.ID_Event
or something else? Thanks!
:
It might look something like this:
SELECT [Seat] From tblSeat WHERE NOT IN SELECT [Seat] FROM tblAction WHERE
[Event] = Forms!MyForm!txtSeat;
Hopefully, you have the event number in a control on your form. It the
above, I call it txtSeat. You will have to change that to use your actual
name. Also, note that in Forms!MyForm you will need to change MyForm to the
name of your form. Stored queries do not understand Me.
Notice the second SELECT statment. This is known as a sub query. It is a
query within a query. The above query will only return seat numbers that are
not in the sub query.
--
Dave Hargis, Microsoft Access MVP
:
So... I don't know if this is going to make talking about my little (read
/overwhelmingly large for a noob like me/) project easier or more difficult
but I'm going to give it a shot and hope for the best. Here are my tables:
tblMain
.ID_Main, AutoNumber, PrimaryKey
.FirstName, text, First Name of Person
.LastName, text, Last Name of Person
.UserIdentifier, text, Person’s Driver’s License
.RefNum, text, Unique ref num for show
.Status, num, link to status
tblRefNum
.RefNum, text, PrimaryKey
.BatchNum, text, PrimaryKey
.ShowName, text, name of the show
.Location, num, link to location
.Type, num, link to type
.Stage, num, link to stage
tblStatus
.ID_Status, AutoNumber, PrimaryKey
.Status, text, is person seated, waiting, etc.
tblSeat
.Seat num, PrimaryKey, fixed list of seat numbers
tblAction
.ID_Action, AutoNumber, PrimaryKey
.Person, num, link to person in tblMain
.Event, num, link to event
.Reason, num, link to reason
.Seat, num, link to seat
tblEvent
.ID_Event, AutoNumber, PrimaryKey
.Event, text, WaitList, Seated, Moved, etc.
tblReason
.ID_Reason, AutoNumber, PrimaryKey
.Reason, text, WillCall, Return, BetterSeat, etc.
tblLocation
.ID_Location, AutoNumber, PrimaryKey
.Location, text, Theater Name
tblType
.ID_Type, AutoNumber, PrimaryKey
.Type, text, Dramatic, Musical, Variety, etc.
tblStage
.ID_Stage, AutoNumber, PrimaryKey
.Stage, text, Pre-Sale, DayOf, etc.
Relationships (one : many):
tblMain.ID_Main : tblAction.Person
tblMain.RefNum : tblRefNum.RefNum
tblMain.Status : tblStatus.ID_Status
tblRefNum.Location : tblLocation.ID_Location
tblRefNum.Type : tblType.ID_Type
tblRefNum.Stage : tblStage.ID_Stage
tblAction.Event : tblEvent.ID_Event
tblAction.Reason : tblReason.ID_Reason
tblAction.Seat : tblSeat.Seat
The way I have this setup is to have the user enter information about a
person into a form, storing said data in tblMain. Then the user using a
separate form creates events for that person (like being seated in a
particular seat or being moved to another seat or being put on a waitlist).
Each of those events have subevents (being seated would require a user to
select a seat number, or being moved would require a user to select a new
seat number). I hope some of this is making sense… It’s a tiny bit more
clear in my head than it probably is in this post and it's becoming less and
less amorphous as I work through this but it is still a struggle for me.
Anyhow… that’s where I am now… Does this look like I’m going about this
right? From this is it possible to query to return only the seats not
currently used? Do I need to add or ditch any fields? Any help will be
greatly appreciated. Thanks!
:
Does this Action table include seat assignments?
Another question. Does the Action table specify an event? One would think
that for each event, the seat could be assigned.
--
Dave Hargis, Microsoft Access MVP
:
Thanks for your message. To answer your question, I've been struggling with
the question of how to demonstrate that a seat is assigned. I've been
wondering if, on a very basic level, there is something I should do to the
table's structure so each seat can only be assigned to one person at a time.
I just don't know how to do that. As a response I've created this
form-subform situation where the sub has a list of each action taken for a
given person. One action is to seat the person. When this action is
selected, using afterupdate, a seat combo box is changed from visible = false
and locked = true to visible = true and locked = false.
I suppose the query would be to look at all the records in tblAction and
find the most recent assignment to a particular seat and then limit the list
by that query... I just don't know how to do that... It's causing my blood
pressure to rise and it’s giving me a headache... Often these two symptoms
together mean I'm taking the more difficult path but in this case I'm not
sure what I should do to make this easier. I'd like to ultimately be able to
generate a reports showing all actions taken, all actions taken for a given
person, all actions taken for a given seat, and current seats assigned...
which is why I've done this the way I've done this... though I am certainly
not married to it and would gladly try something else if you have any
suggestions on an easier way for me to do this. Your guidance is greatly
appreciated! Thanks!
:
First, how do you know if a seat is assigned?
You would need to modify the row source of your comb box to be a query
filtered on seats not assigned. Then, it is necessary to requery the combo
each time a seat is assigned.
--
Dave Hargis, Microsoft Access MVP
:
I've got a continuous subform that contains a combo box that has numbers
that correspond to seat numbers. The form (frmMain) brings up the record for
a given person and the subform (frmAction) allows the user to assign a seat
number if the user doesn't currently have one assigned or move the user from
the seat to which they are currently assigned to a new seat. There are a
fixed number of seats. The seat numbers are being stored in a table
(tblSeat). The action taken using sub form frmAction is stored in tblAction.
I'd like to limit the seats selectable to the seats not currently assigned.
Is there a simple way of doing this? Thanks for your help!
