HELP! Create Table using SQL/Access/ ASP (Classic)
Greetings
Suppose I am selling tickets to an event.
The ticket includes BUS transporation to the event
Each bus has 56 seats
when one bus is full I need to create a new table for another bus
so..how do I "name" the table such that each name is unique?
For example (and point out SQL ERRORS please, im a newbie)
create table bus '<--------------- this must be in some sorta sequence to create different names
(busID autonum,
bus_cost number(20), '<----------- Question: should'nt this be a floating point number?
num_seats number(2),
bus_license varchar(10),
city varchar(20),
state varchar(20));
I hope I have made my question clear.
Suppose I am selling tickets to an event.
The ticket includes BUS transporation to the event
Each bus has 56 seats
when one bus is full I need to create a new table for another bus
so..how do I "name" the table such that each name is unique?
For example (and point out SQL ERRORS please, im a newbie)
create table bus '<--------------- this must be in some sorta sequence to create different names
(busID autonum,
bus_cost number(20), '<----------- Question: should'nt this be a floating point number?
num_seats number(2),
bus_license varchar(10),
city varchar(20),
state varchar(20));
I hope I have made my question clear.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Yes, that's true. But in that case, how do you keep track of the seats. I take it you have a secondary table for that?
ASKER
@ pvginkel >> You should create an extra table with busses and use the ID of those rows as your unique identifier
wow. that is a better implementation. Like this?
tbl_busses
------------
bus_id
bus_company
bus_type
bus_num_seats
bus_cost
bus_miles_per_gallon
which would mean I would need a table for seats (to keep count on max seats) so that I know when to enter a new record in [tbl_busses], right? wrong?
tbl_seats
----------
seat_id
bus_id
seat_count (guess this would be of type num)
Please advise
Thanks in advance
tbl_seats
----------
seat_id
bus_id <<< foreign key to tbl_buses
seat_id and bus_id together form composite key to tbl_seats
You do not need the seat_count since you can get it by the number of seat_id in a bus_id
----------
seat_id
bus_id <<< foreign key to tbl_buses
seat_id and bus_id together form composite key to tbl_seats
You do not need the seat_count since you can get it by the number of seat_id in a bus_id
No, not the seat count in tbl_seats. You want to store the seat number in there and a flag (an int which can get 0 or 1) whether the seat is sold. This would create 56 records in tbl_seats for every tbl_busses.
ASKER
@pvginkel >> You want to store the seat number in there and a flag (an int which can get 0 or 1) whether the seat is sold. This would create 56 records in tbl_seats for every tbl_busses.
You just lost me...whatyou have written is EXACTLY what im trying to do, but it's not ...."clear" yet...
what would the table structure look like? I dont "see" it.
@hongjun >> You do not need the seat_count since you can get it by the number of seat_id in a bus_id
I think you guys are saying the same thing, but I need an example to be clear
You just lost me...whatyou have written is EXACTLY what im trying to do, but it's not ...."clear" yet...
what would the table structure look like? I dont "see" it.
@hongjun >> You do not need the seat_count since you can get it by the number of seat_id in a bus_id
I think you guys are saying the same thing, but I need an example to be clear
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
@hongjun ...
ooohhhh! this is staring to make sense!
Now, to be even more clear :->....
I can use the SAME table for seats [tbl_seats] regardless of how many busses i need to create, right?
ooohhhh! this is staring to make sense!
Now, to be even more clear :->....
I can use the SAME table for seats [tbl_seats] regardless of how many busses i need to create, right?
yes..
bus_id of tbl_seats is related to bus_id of tbl_buses..
So this means you can have a bus_id in tbl_buses and that same bus_id may not even be in tbl_seats. This just mean there are no seats for that bus.
hongjun
bus_id of tbl_seats is related to bus_id of tbl_buses..
So this means you can have a bus_id in tbl_buses and that same bus_id may not even be in tbl_seats. This just mean there are no seats for that bus.
hongjun
You make the primary key a combination of bus_id and seat_id. Something like "primary key (bus_id, seat_id)". Then you can simply use the seat_id as the seat number.
ASKER
@pvginkel >> "You make the primary key a combination of bus_id and seat_id. Something like "primary key (bus_id, seat_id)".
In Access? (2003) i suppose... and then in SQL view only (i suppose).
@pvginkel >> Then you can simply use the seat_id as the seat number.
How can the seat_id be the same as the seat "number" if a bus only holds 56 seats. What happens when the seat_id reaches 57?
In Access? (2003) i suppose... and then in SQL view only (i suppose).
@pvginkel >> Then you can simply use the seat_id as the seat number.
How can the seat_id be the same as the seat "number" if a bus only holds 56 seats. What happens when the seat_id reaches 57?
ASKER
I just built it. I get it! you both were great...points comin...
ASKER
Now let me explain...
Thanks to pvginkel for correcting my wayward thinking in the early stages and pointing me toward the light. The concepts were clearly heard and 9finally) understood (mostly) ;->
- "Simple answer: you shouldn't."
But also, shouts out to hongjun for the patience and extra "newbie tenderness" showed...
"Suppose I have a bus_id ".....
aaawwww. wasnt that sweet? Made all the theory make sense.
Peace and blessings to you both! and thanks again.
I'd also like to thank my producers, directors and most of all you the fans!
Thanks to pvginkel for correcting my wayward thinking in the early stages and pointing me toward the light. The concepts were clearly heard and 9finally) understood (mostly) ;->
- "Simple answer: you shouldn't."
But also, shouts out to hongjun for the patience and extra "newbie tenderness" showed...
"Suppose I have a bus_id ".....
aaawwww. wasnt that sweet? Made all the theory make sense.
Peace and blessings to you both! and thanks again.
I'd also like to thank my producers, directors and most of all you the fans!
(busID autonum,
bus_cost number(20, 2),
num_seats number(2),
bus_license varchar(10),
city varchar(20),
state varchar(20));
I don't see the need to create multiple tables for different buses. You can use the same table.
busID = 1 represents first bus
busID = 2 represents second bus
...
hongjun