[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

HELP! Create Table using SQL/Access/ ASP (Classic)

Posted on 2006-03-26
14
Medium Priority
?
758 Views
Last Modified: 2011-09-20
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.
0
Comment
Question by:the_sleeper
  • 6
  • 4
  • 4
14 Comments
 
LVL 10

Accepted Solution

by:
pvginkel earned 1000 total points
ID: 16294010
Simple answer: you shouldn't. You should create an extra table with busses and use the ID of those rows as your unique identifier. Any system that uses creation of new tables as a standard practice should be looked at critically. Of course, if you want it this way, you can.

You could always use the current date/time as a name or keep a counter somewhere (in a table or a text file) and increment that every time you create a new table.
0
 
LVL 33

Expert Comment

by:hongjun
ID: 16294026
create table bus
(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
0
 
LVL 10

Expert Comment

by:pvginkel
ID: 16294052
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?
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:the_sleeper
ID: 16294155


@ 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
0
 
LVL 33

Expert Comment

by:hongjun
ID: 16294165
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
0
 
LVL 10

Expert Comment

by:pvginkel
ID: 16294169
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.
0
 

Author Comment

by:the_sleeper
ID: 16294214
@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
0
 
LVL 33

Assisted Solution

by:hongjun
hongjun earned 1000 total points
ID: 16294223
>>You do not need the seat_count since you can get it by the number of seat_id in a bus_id

Suppose I have a bus_id value 1 and many seat_id from 1 to 50 for the same bus_id.
Using the below sql statement, I can get to know how many seats are there for bus_id = 1

SELECT COUNT(seat_id)
FROM tbl_seats
WHERE bus_id = 1
0
 

Author Comment

by:the_sleeper
ID: 16294239
@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?
0
 
LVL 33

Expert Comment

by:hongjun
ID: 16294244
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
0
 
LVL 10

Expert Comment

by:pvginkel
ID: 16294265
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.
0
 

Author Comment

by:the_sleeper
ID: 16294329
@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?
0
 

Author Comment

by:the_sleeper
ID: 16294359
I just built it. I get it! you both were great...points comin...
0
 

Author Comment

by:the_sleeper
ID: 16294385
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!
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question