Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2007
    Posts
    34

    Unanswered: SQL Counter function based on Access DB

    Hi there,

    I'm not sure if I should put this in the Access topic or SQL topic, but since it involved more SQL coding I thought I'd put it in here.

    I tried searching through the forum for different SQL counters, and I tried the various suggestions but it's still not working properly ..

    I'm running something off an MS Access database (did the whole ODBC thing) and the program where I'm coding from has connected successfully with the Access database.

    Here's the scenario basically ..

    I have these data sets that come in periodically .. and every 4th data set that comes in is chosen for an audit .. the way how i have this set is this ..

    When the initial dataset comes in:
    select counter as counter_alias from Audit;

    Then I raise the counter by 1:
    update Audit
    set counter=counter_alias+1 (i tried counter=counter+1 before and it didn't work)

    Then there's a decision node that determines whether or not if it's +/- 4 and it sends it to its respective place.

    If it is sent to be audited, then have the counter set back to 0.
    update Audit
    set counter=0;

    I'm wondering if it's my coding that's messed up or if its because I'm using Access and there are some limitations when I'm using Access as the database.

    Any help would be appreciated.

    Thanks!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    where is the logic that determines whether it is sent to be audited?

    how many tables are there?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2007
    Posts
    34
    i'm running from 1 table.

    and sorry, i'm pretty new with sql .. my company kinda tossed me this project b/c i know how to use access .. so their reasoning was access = sql therefore i know.

    is there some sort of sql command where something gets inputted?

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    First of all, I'm really confused so you'll need to think these comments through, don't just accept them as gospel.

    SQL is the language that programs written in other languages use to communicate with the database engine. SQL is the "go between" that makes it easy for your VBA (the programming language used by Microsoft Access) to communicate with a database (such as the default Jet engine used by Access, or Microsoft SQL, or DB2, or MySQL, or Oracle). The database then does whatever the SQL specifies that it ought to do, in whatever way the database sees fit to do that, then returns the result to your program.

    Because SQL is the language used between your programming language of choice and the database engine, there isn't any "input" other than the SQL script itself. MS-Access blurs the line between the programming (actually scripting) language and the database manipulation language so that they appear to be one thing to the developer and the end user, but they are two very different things. MS-Access integrates them very well, but most languages don't even try to integrate them at all, which makes the shift from MS-Access to another database platform a lot harder than a shift like that normally is.

    Which database engine are you using? My first guess is Microsoft SQL, but before I start prattling about how to do things we ought to get at least that much settled!

    -PatP

  5. #5
    Join Date
    Sep 2007
    Posts
    34
    Sorry about the confusion ..

    I think the confusion lies in the problem that it's a company developed product that i'm using and i really can't give too much detail out except that i'm trying to get communication between MS Access and the company product ..

    Re-reading the scenario I wrote before, I did not do a very good job outlining what was desired .. So through this company product, we receive several datasets .. each dataset is more or less unique. But every 4th dataset that comes in needs to be audited.

    I'm using MS Access to control the 'random' audit test.

    It's almost like having somebody call a customer service place and every fourth caller is chosen to do a survey or something.

    So when the data set comes in, it is flagged 1, 2, 3, or 4 ..

    thus the "update audit set counter=counter+1" etc etc.

    does that make more sense?

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by juxtaposed
    more or less unique.
    That means they are not unique!

    How do you recieve this data? It may be simpler to simply give each record an auto incrementing integer value and then you audit every record where the modulus 4 of this integer = 0.
    George
    Home | Blog

Posting Permissions

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