Averages Query

[AC5FF]

PS: Would like to add a Thank You here! While I seem to be spinning wheels on my end I am learning quite a bit!

 

[vbaInet]

Glad to know you're learning. You're welcome.

Did you see my last post? I don't think you would have because ours crossed. To deal with the issue defined issue, what version of access are you running?

 

[AC5FF]

I did see your post; then went back to the query and re-added the TIN Date and REC date fields to that query. My only issue there is you said each row needed to be distinct w/the serial # and unit.
But now the Horton1 query returns what you see in post#4. While each row is distinct, the serial # may be repeated (or not). that will depend on how many times it has been repaired. Yes, each row is distinct; but only due to the TIN Date and REC Date information.

I'm running Access2007

 

[vbaInet]

Ok, let me clear things up.

You need two queries:

1. The DISTINCT query that returns Serial and Unit. That will be where the avg will be calculated.
2. The Horton1 query would be for the OpenRecordset method. That query was the I was last speaking of.

See what I mean now?

 

[AC5FF]

Oh man am I lost... I have been suffering from a bad head cold so I would like to blame my lack of understanding on that... But, who am I trying to kid...

No, not really understanding. I've now got two queries that I have set up. First, I dont' know why I put in "Horton1" earlier... so I will try and clear that up now. It's correct in my function code.
[Horton Data] query returns data as posted in my post #4; multiple rows for each serial# dependant on REC DATE and TIN DATE fields.

[Horton Data1] query returns - well nothing right now. It is set up to pull data only from the [Horton Data] query. Here is the code for the [Horton Data1] query.

SELECT [Horton Data].abbr, [Horton Data].[s/n], CalcAvg("[abbr]",[s/n]) AS [Serviceability Avg]
FROM [Horton Data]
GROUP BY [Horton Data].abbr, [Horton Data].[s/n];

Not that I'm an expert writing queries in SQL (99% of the time I use design view), but CalcAvg(..... see the quotations around [abbr]? Should those be there? If so, why are they around that and not around [s/n]?

 

[AC5FF]

Just curious on the compile error. I scrolled back through other functions. The DIM statement for those appeared a little different. Written like this:

Sub ShowUserRosterMultipleUsers()
    Dim cn As New ADODB.Connection
    Dim cn2 As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim I, j As Long

Should I try replacing what you wrote as "dao.database" to ADODB.database or ADODB.recordset? but every function I looked at contained that same statement in the DIM.... none that I found used 'dao.'
Not that I know what I'm talking about here; just trying to look at what is different. Not really sure what dao. or ADODB. does..

 

[vbaInet]

Post #25 looks fine.

Last post: In the VBA editor, go to TOOLS > REFERENCES. Scroll through the list and select/tick "Microsoft DAO 3.6 Object Library" and click OK.

 

[AC5FF]

Okay.. went to the VBA editor but TOOLS/REFERENCES is dimmed out and not selectable.
Edit...
Strike that. Guess in debug mode it's not selectable. When I go back in on my own it is. However, it is asking for a password! This is the first time I have had anything asking for a password regarding this database!!! Arrgghh

 

[AC5FF]

WHEW.... I knew I had the password somewhere!! LOL Took me going back to 2007 emails... WoW..

Okay... enabled that library and re-ran. I now get a runtime error 3061: Too Few Parameters. Expected 2.

 

[vbaInet]

When you click on Debug, what line is it highlighting?

 

[AC5FF]

The whole line here is highlighted:

Set rst = db.OpenRecordset("SELECT [abbr], [S/N], [Rec Date], [TIN Date] FROM [horton data] " & _
                                "WHERE abbr = '" & unitVal & "' AND [S/N] = " & serialVal & " " & _
                                "ORDER BY [TIN Date]", dbOpenSnapshot)

 

[vbaInet]

How do you call the function again? I may have got it wrong.

 

[AC5FF]

Serviceability Avg: CalcAvg("[abbr]",[s/n])

I'm at home now so i'll have to update and try things in the morning....
But, I mentioned this earlier and want to see if there is a reason for the quotes around [abbr] above.. It's not around [s/n] ... I've removed the quotes and get the same thing, I didn't try putting quotes around [s/n] though....

 

[vbaInet]

Get rid of the quotes when you test it again.

Send some test data later of the sample data.

 

[AC5FF]

Tested w/out quotes. Didn't help the error.
What data do you want to see? Real data like in post 4 or what the 2nd query gives when runnng the function?

 

[vbaInet]

Raw data like in post #4. Use the right field names as you have it in your db as well.

An excel spreadsheet will do.

 

[Brianwarnock]

How about doing it with 4 queries. See attached.

Brian

 

[AC5FF]

vbaInet: Sorry... It's been an incredibally busy morning around here. Finally got around to posting this Excel Spreadsheet w/sample data.

I just took the first couple of rows of data, but this is just how it comes out in the query.

Couldn't upload a .xlsx file! So, I zipped it!

 

[AC5FF]

Brian; I will look over your sample here and see what I can come up with.

I'm learning a lot about VB and functions with vbaInet; so I am going to continue down that path but, knowing other ways will be helpful in the future! Thanks!

 

[Brianwarnock]

That's fine, after all you could have easily taken the soft option of exporting to EXcel and calculated the individual day diff with a formula like =IF(B2=B1,C2-D1,0) and then reimported for the final tweak.

Brian