Ok, the mooshing solution is how I'm going about this, to simplify the SQL part. Table now looks like this:

(Table name is ProductionData)

KeyField pdDate pdTime StopCode StopTime OperatorName currentshift MachineName
2007/07/17 14:19:41 fred 2007/07/17 14:19:41 0 0 admin 2 fred

Now I'm betting the "select distinct" will become much simpler/clearer (I hope)

Thanks again for your help!
Joe



JP

By the way... was the code you posted intended to be VBScript? If so, maybe I'm doing something wrong here.. wouldn't surprise me any... I get nasty-grams from the interpreter about the very first line...."Expected end of statement...." If it's not VBScript, is there a way to make this happen with VBScript?

Thanks again,
Joe

JP

If you are using this in VBScript (which has typeless variables) remove the "As ..." part of the variable declarations, so:

Dim rs1, rs2, rs3
Dim sSQL1, sSQL2, sSQL3
Dim sT1, sT2, sT3
Dim i

No need to use seperate rows, but I do anyway.

Also, the ADO syntax is different and you will need to declare and make connection objects. Is this going to Access or SQL?





mmcdonal

It's an Access database... Thanks for your patience with someone new to the whole VB* business...

Joe

JP

If you are writing this in VBScript, and you are moving data around in Access, this is the Connection and Recordset syntax using a DSN:

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

cn.Open "DSN=YourDSNNameHERE;"
rs.CursorLocation = 3
rs.Open "SELECT * FROM YOURTABLE", cn, 3, 3

Let me know if you are not using a DSN.



mmcdonal

This gets deeper as we go... "Your DSNname here" - would I replace that with the .mdb file's name? I'm not sure exactly what a DSN is, in this context... All I remember is from mainframe JCL days when it meant DataSet Name, and I bet that's NOT what you're talking about.... Is NOT using a DSN easier?

Thanks again... I won't give up easily!

Joe (maybe not so bright, but at least persistent)

JP

Now we are into DSN issues:

A system DSN is easy enough to install (Control Panel> Administrative Tools > ODBC Data Sources) using Access as the source.

The issue is more difficult if this application has to move around to more than one desktop. Then you need to push the DSN, or install it with code, which I do, and then use it. The Access database needs to be in the same location on each PC, or on the same mapped network drive, even better.

It could be easier to use a DSN. They are not the fastest, but they are very solid.

You can use Provider statements instead of a DSN, but the issue is the same. The file must be in the same location on everyone's PC (local or network). SQL Server is much easier in this regard since it makes itself known on the network by server and database name. I don't suppose you could do this on SQL Server? =)

I would say use a DSN, and then I can give you code to install it on the PC at the beggining of the code (check for existence and install if no).

Say, why are you doing this in VBScript anyway, instead on some event in Access, like a Main Form On Open event, or a button click?




mmcdonal

Hi

I dont want to teach my granny to suck eggs but can I suggest a possible solution.

Some time ago I designed a database with a similar problem
My client had a list of customers that were sent to him using Excell spreadsheets

I set up Access so that he could import the spreadsheet into a table.
without going into details this meant that duplicates would be created.

I solved the problem by causing the datetime to be stamped on the latest xl spreadsheet input.

I then used the find duplicate query wizard to identify all the duplicates

I then ran a query which set the records in time date order then simply highlighted the oldest records and deleted them


I dont know if this is perhaps an over simplification. I thought it couldn,t hurt to mention it

Apologies if I am out of line on this

Man Friday

Ok... one step further... found the DSN stuff in control Panel > administrative tools > Data Sources...

    I guess that's as easy as any way to access this data...

Additionally, at this point, I've got 2 tables, one with and one without the PK... the "anything goes - no keys" table is called ProductionData, and the other one, WITH Keyfield defined as primary key, is PDDataClean.

  Progress!
               Joe

JP

It sounds like the one without the PK is the old one, and the one with is the intermediate one for cleaning data.

Don't try to transfer a PK. Put a PK column in the old table, use autonumber as the format, and then don't reference in your code. As you hit the AddNew code, the table will automatically insert new PK's for you.


mmcdonal