SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Member
    Join Date
    Jan 2009
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Question on Simple Access Database

    I am new to databases and I need a little help please.

    I am working on a simple desktop database using Access.

    I have 99 different product categories that I need to keep track of inventory audits.

    I was planning on creating 99 different tables with these columns.
    coulmn 1: categoryID (autoNumber)
    coulmn 2: categoryNumber
    coulmn 3: auditDate
    coulmn 4: dollarAmount
    column 5: notes

    I can't figure out how to retrieve the correct information on my webpage.

    I want to be able to just create a simple form that the user will just input what category number they want to view and then it will show each audit record by date.

    I can do this when I create a query for a specific table (like query table category_49) but how can I query all 99 tables?

    Thanks

  2. #2
    SitePoint Member
    Join Date
    Jan 2009
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Some information I forgot to add to the post is that I am using ColdFusion and I know how to query 1 table using...

    <cfquery name="categoryQuery" datasource="inventory">
    SELECT categoryNumber, auditDate, dollarAmount, notes
    FROM category_2_table
    ORDER BY auditDate

    then output the data with...

    <cfoutput query="categoryQuery"></cfoutput>

    I can do this but it is only making a query for one table. How do you query all the category tables?

    I'm trying to figure out if my coldfusion code is incomplete or do I just have my database setup wrong.

    Thanks

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    the latter

    you need only 1 table, not 99
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Member
    Join Date
    Jan 2009
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I was thinking that I just needed everything in 1 table but I read somewhere that each thing you wanted to store should be in different tables.

    One table will make things much easier.

    Thanks for the help.

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by 10can View Post
    ... I read somewhere that each thing you wanted to store should be in different tables.
    that's true!!

    categories should be in the categories table, horses should be in the horses table, widgets should be in the widgets table, etc. -- and they should not be mixed!!

    but 99 category tables is not the same idea
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •