Query help - sold / still available results

  • Thread starter Thread starter Lakeuk
  • Start date Start date
L

Lakeuk

I have the following tables setup in Access 97:-

timing table:-
fields-timing_id,f_date,f_time
values-1,25/12/2003,10pm
values-2,26/12/2003,10pm

venue table:-
fields-pos_id,row,seat,x,y
values-1,A,1,1,0
values-2,A,1,2,0
values-3,B,1,1,1
values-4,B,1,2,1

available table:-
fields-av_id,timing_id,venue_id
values-1,1,2
values-2,2,3

When a ticket is sold the details are placed in the available table, the
above shows two tickets sold one for each date, I want to run a query that
will give the following results so I can create a plan of whats sold and
still available:-

example for date 25/12/2003,10pm
fields-av_id,row,seat,x,y
values-null,A,1,1,0
values-1,A,1,2,0
values-null,B,1,1,1
values-null,B,1,2,1

The best query I've comeup with is the following:-

SELECT venue.row,
venue.seat,
venue.x,
venue.y,
available.av_id
FROM venue LEFT JOIN available
ON venue.venue_id = available.venue_id
WHERE timing_id = 1
OR timing_id Is Null;

which would give the following results:-

example for date 25/12/2003,10pm
fields-av_id,row,seat,x,y
values-null,A,1,1,0
values-2,A,1,2,0
values-null,B,1,2,1

Note that the 3rd record from the 1st example doesn't show above which I
would like it to as it is an unsold ticket for the first date.

Any ideas on how to do it, is it possible with one query.

Thanks

Dave
 
Dave,

I am trying to follow your tables and I 'm not sure I can follow you.
But I will try anyway, comments in your text and below:



Lakeuk said:
I have the following tables setup in Access 97:-

timing table:-
fields-timing_id,f_date,f_time
values-1,25/12/2003,10pm
values-2,26/12/2003,10pm

venue table:-
fields-pos_id,row,seat,x,y
Click to expand...

I Changed pos-id into: venue-id
values-1,A,1,1,0
values-2,A,1,2,0
values-3,B,1,1,1
values-4,B,1,2,1

available table:-
fields-av_id,timing_id,venue_id
values-1,1,2
values-2,2,3

When a ticket is sold the details are placed in the available table, the
above shows two tickets sold one for each date, I want to run a query that
will give the following results so I can create a plan of whats sold and
still available:-

example for date 25/12/2003,10pm
fields-av_id,row,seat,x,y
values-null,A,1,1,0
values-1,A,1,2,0
values-null,B,1,1,1
values-null,B,1,2,1

The best query I've comeup with is the following:-

SELECT venue.row,
venue.seat,
venue.x,
venue.y,
available.av_id
FROM venue LEFT JOIN available
ON venue.venue_id = available.venue_id
WHERE timing_id = 1
OR timing_id Is Null;

which would give the following results:-

example for date 25/12/2003,10pm
fields-av_id,row,seat,x,y
values-null,A,1,1,0
values-2,A,1,2,0
Click to expand...
I got:
values- 1 ,A,1,2,0

values-null,B,1,2,1

Note that the 3rd record from the 1st example doesn't show above which I
would like it to as it is an unsold ticket for the first date.

Any ideas on how to do it, is it possible with one query.

Thanks

Dave
Click to expand...
I think you want to take all posible links between the timing and vanue
tables (Q1 in the below SQL)
and then relate this with a left join to the available table.
Try this:


SELECT
available.[av-id],
Q1.*
FROM
(SELECT
venue.row,
venue.seat,
venue.x,
venue.y,
Timing.f_date,
Timing.timing_id,
venue.[venue-id]
FROM
venue, Timing
WHERE
Timing.timing_id=1) as Q1
LEFT JOIN available ON
(Q1.[venue-id] = available.venue_id) AND (Q1.timing_id =
available.timing_id);


Result:
av-id, row, seat, x, y, f_d, timing_id, venue-id

Null, a, 1, 1, 0, 25-Dec-03, 1, 1

1, a, 1, 2, 0, 25-Dec-03, 1, 2

Null, b, 1, 1, 1, 25-Dec-03, 1, 3

Null, b, 1, 2, 1, 25-Dec-03, 1, 4


I think it is better if you split this into two queries:
Just make a query Q1 with the SQL in the FROM Clause in the main query then
the second query would look like:
SELECT
available.[av-id],
Q1.*
FROM
Q1 LEFT JOIN available ON
(Q1.[venue-id] = available.venue_id) AND (Q1.timing_id =
available.timing_id);

I hope this will help you.
If this is not clear you must seply some more details.

Regards,

Peter van der Zwan MCSA
 
thanks you've put me on the right track - the following sql produces the
results I need:-

SELECT available.av_id, venue.row, venue.seat, venue.x, venue.y
FROM venue LEFT JOIN available ON venue.venue_id = available.venue_id
WHERE (((available.av_id)=1 Or (available.av_id) Is Null))
UNION ALL SELECT Null AS av_id, venue.row, venue.seat, venue.x, venue.y
FROM venue LEFT JOIN available ON venue.venue_id = available.venue_id
WHERE (((available.av_id)<>1));

Dave



PvdZ said:
Dave,

I am trying to follow your tables and I 'm not sure I can follow you.
But I will try anyway, comments in your text and below:



Lakeuk said:
I have the following tables setup in Access 97:-

timing table:-
fields-timing_id,f_date,f_time
values-1,25/12/2003,10pm
values-2,26/12/2003,10pm

venue table:-
fields-pos_id,row,seat,x,y
Click to expand...

I Changed pos-id into: venue-id
values-1,A,1,1,0
values-2,A,1,2,0
values-3,B,1,1,1
values-4,B,1,2,1

available table:-
fields-av_id,timing_id,venue_id
values-1,1,2
values-2,2,3

When a ticket is sold the details are placed in the available table, the
above shows two tickets sold one for each date, I want to run a query that
will give the following results so I can create a plan of whats sold and
still available:-

example for date 25/12/2003,10pm
fields-av_id,row,seat,x,y
values-null,A,1,1,0
values-1,A,1,2,0
values-null,B,1,1,1
values-null,B,1,2,1

The best query I've comeup with is the following:-

SELECT venue.row,
venue.seat,
venue.x,
venue.y,
available.av_id
FROM venue LEFT JOIN available
ON venue.venue_id = available.venue_id
WHERE timing_id = 1
OR timing_id Is Null;

which would give the following results:-

example for date 25/12/2003,10pm
fields-av_id,row,seat,x,y
values-null,A,1,1,0
values-2,A,1,2,0
Click to expand...
I got:
values- 1 ,A,1,2,0

values-null,B,1,2,1

Note that the 3rd record from the 1st example doesn't show above which I
would like it to as it is an unsold ticket for the first date.

Any ideas on how to do it, is it possible with one query.

Thanks

Dave
Click to expand...
I think you want to take all posible links between the timing and vanue
tables (Q1 in the below SQL)
and then relate this with a left join to the available table.
Try this:


SELECT
available.[av-id],
Q1.*
FROM
(SELECT
venue.row,
venue.seat,
venue.x,
venue.y,
Timing.f_date,
Timing.timing_id,
venue.[venue-id]
FROM
venue, Timing
WHERE
Timing.timing_id=1) as Q1
LEFT JOIN available ON
(Q1.[venue-id] = available.venue_id) AND (Q1.timing_id =
available.timing_id);


Result:
av-id, row, seat, x, y, f_d, timing_id, venue-id

Null, a, 1, 1, 0, 25-Dec-03, 1, 1

1, a, 1, 2, 0, 25-Dec-03, 1, 2

Null, b, 1, 1, 1, 25-Dec-03, 1, 3

Null, b, 1, 2, 1, 25-Dec-03, 1, 4


I think it is better if you split this into two queries:
Just make a query Q1 with the SQL in the FROM Clause in the main query then
the second query would look like:
SELECT
available.[av-id],
Q1.*
FROM
Q1 LEFT JOIN available ON
(Q1.[venue-id] = available.venue_id) AND (Q1.timing_id =
available.timing_id);

I hope this will help you.
If this is not clear you must seply some more details.

Regards,

Peter van der Zwan MCSA
Click to expand...
 
Back
Top