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!

where is the logic that determines whether it is sent to be audited?

how many tables are there?

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?

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

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?

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.