Snitz Forums 2000 - Whats is better, multiple tables or 1 table?

Snitz Forums 2000

Username:	Password:
Save Password
Forgot your Password?

All Forums

Community Forums

Code Support: ASP (Non-Forum Related)

Whats is better, multiple tables or 1 table?

New Topic

Topic Locked

Printer Friendly

Author

Topic

Intrepidone
Average Member

Canada
515 Posts

Posted - 25 May 2002 : 08:19:59

I need some advice here. I am trying to get the sum from a column (NSN_Unit_Price) from 2 or more tables [Laptops_LT] and [Desktops_PC], but am not having any luck. I can add the sum from (NSN_Unit_Price) in one table, showing the total cost of multiple items, or each item individually, but I cannot pull the sums from 2 or more tables so it shows me the total cost of everything in the db.

Is it better to have multiple tables? I have 13 tables now, 1 for every category of product. If I just used 1 table for all 13 categories I could accomplish what I want, but this 1 table would contain approximately 7000 items. Is this feasible? or should I gut it out and stick to 13 tables and bang my head more?

Intrepidone

Edited by - Intrepidone on 25 May 2002 08:21:48

Intrepidone
Average Member

Canada
515 Posts

Posted - 25 May 2002 : 08:40:00

Here is the code I am using

<%
set rsNSN_Unit_Price=server.createobject("adodb.recordset") 

rsNSN_Unit_Price.open "select Sum(NSN_Unit_Price) As aTotal FROM [Desktops_PC], [Laptops_LT] where NSN_Unit_Price+'" & rs("NSN_Unit_Price") & "'",myDsn ,3,3
AllequipCost = rsNSN_Unit_Price("aTotal")

rsNSN_Unit_Price.Close
set rsNSN_Unit_Price = nothing

strAllTotalEquip = AllequipCost
%>

I thought I was on the right track because here is the error I get,

Microsoft OLE DB Provider for ODBC Drivers error '80004005' 
[Microsoft][ODBC Microsoft Access Driver] The specified field 'NSN_Unit_Price' could refer to more than one table listed in the FROM clause of your SQL statement.

Intrepidone

Edited by - Intrepidone on 25 May 2002 08:42:12

HuwR
Forum Admin

United Kingdom
20595 Posts

Posted - 25 May 2002 : 09:59:15

quote:

Is it better to have multiple tables? I have 13 tables now, 1 for every category of product. If I just used 1 table for all 13 categories I could accomplish what I want, but this 1 table would contain approximately 7000 items. Is this feasible? or should I gut it out and stick to 13 tables and bang my head more?

Yes, much better, 7000 records is a very small table in database terms
Have one table with your items, and another table which holds category info, and link them using the category id.

You can then do a SUM and group by the cat_id

Intrepidone
Average Member

Canada
515 Posts

Posted - 25 May 2002 : 13:04:41

Thanks HuwR,

As it stands now, every table (13tables) I have has 23 columns. Each column will hold the same information for every item in all 13 tables, so I guess combining all 13 tables into 1 table will be easier. Every column holds a very small amount of info, maybe a room #, name, building, things similiar to this.

I am still struggling along trying to figure out how to write the code properly, can you give me an example of how to do a SUM and group by the cat_id.

Thanks

Intrepidone

HuwR
Forum Admin

United Kingdom
20595 Posts

Posted - 25 May 2002 : 13:32:07

select cat_id, sum(afield) as fieldSum from sometable group by cat_id

Topic

New Topic

Topic Locked

Printer Friendly

Jump To:

Snitz Forums 2000

This page was generated in 0.24 seconds.