Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Jun 2003
    Location
    Annapolis, Maryland, USA
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Using SQL Count (Access 2000)

    Here's my first post.... Here goes everything...

    I Have an scenario I have been trying to build in SQL with the COUNT function. I am a ColdFusion newb as well,but I am working through the SQL
    and db problems. I am having problems evaluating two sets of data. Here is how I broke down the data.

    TABLE
    UserID (PK & AutoNumber)
    Max# (Number of Postings a User Can add to dbase) default value = 5
    COUNTR (Number of records User has already added to the dbase)
    DIFF (Difference between Max# and COUNTR)

    I can save myself alot of headaches working it through CF and SQL.

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Using SQL Count (Access 2000)

    I'm sorry, but I'm not sure what your question is. Are you asking for the SQL to return the count of records a user has added to the database? Are UserID, Max#, COUNTR and DIFF actual fields in the table or do COUNTR and DIFF represent the results you're looking for?
    Charlotte

  3. #3
    New Lounger
    Join Date
    Jun 2003
    Location
    Annapolis, Maryland, USA
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using SQL Count (Access 2000)

    That is correct I am interested in the SQL and the fields displayed in UserID, Max#, CountR, and Diff are all fields in MS Access db. I am not sure if it
    is easier to compile this in Access or not.

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Using SQL Count (Access 2000)

    You mean they're fields in the same table or query or fields in more than one table? The SQL won't be radically different wherever you do it, but I don't understand what you want to do with Count if you already have a value in a field called COUNTR. If you explain more about the data and what you hope to accomplish, it will be easier for us to help.
    Charlotte

  5. #5
    New Lounger
    Join Date
    Jun 2003
    Location
    Annapolis, Maryland, USA
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using SQL Count (Access 2000)

    Ok...
    Charlotte,
    Here is how it breaks down.

    Let's say that I have 2 tables, Products, Users

    Products
    =====================
    ProdID (PK) (AutoNumber)
    UID (Relationship to Users Table)
    Product Name

    Users
    ====================
    UID (PK) (AutoNumber) (Relationship to Product Table 1-to-Many)
    UserName (Validation Purposes)
    Password (Validation Purposes)
    Max# (Max # of Records this user is a allowed to add) (default value=5)(Can be changed to anything an admin can allow)
    CountR (Count All Records in Product Table)
    Diff (Evaluate or Compare the two, display difference)

    That is pretty much it. I have been gnawing through this with ColdFusion, but, I think there is a better way to
    let the dbase do the work and not so much letting the website have to churn it out. It hangs up the session variable and starts to loop.
    Thus the need to do this in my dbase.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Using SQL Count (Access 2000)

    Normally, you wouldn't have CountR and Diff in a table, because they contain derived information. Instead, you would create a query with calculated fields:

    SELECT Users.UID, Users.UserName, Count(*) As CountR, [Max#]-Count(*) As Diff
    FROM Users INNER JOIN Products ON Users.UID = Products.UID
    GROUP BY Users.UID, Users.UserName

    The query engine is pretty efficient at calculating this kind of thing.

    Note: using a non-standard character such as # in a field name is not a very good idea. Although it is allowed, it may cause confusion and problems, because # is used for other purposes too.

  7. #7
    New Lounger
    Join Date
    Jun 2003
    Location
    Annapolis, Maryland, USA
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using SQL Count (Access 2000)

    Thanks Hans.... I will give it a try and get back to you.

  8. #8
    New Lounger
    Join Date
    Jun 2003
    Location
    Annapolis, Maryland, USA
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using SQL Count (Access 2000)

    Thanks Hans...

    Worked Great!

Posting Permissions

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