User Profile

postman · Feb 20 '15, 08:57 PM

Solved

That did it! And the performance is WAY better.

Thank you, Seth!

Here is the complete solution:

Step 1: Created a SourceCount query to provide numbered results for each person in each time period:

Code:

SELECT T1.qID
, T1.sMonth
, T1.rName
, T1.MgrName
, T1.Score
, Count(T2.qID) As ScoreNum
FROM tblScores As T1 LEFT JOIN
    (SELECT

...

postman · Feb 20 '15, 08:11 PM

I added GROUP BY to the end and that solved the error. But it's just showing a total count for each person for each month on each row--not the incremental count.

Here's the query now:

Code:

SELECT T1.qID
, T1.sMonth
, T1.rName
, T1.MgrName
, T1.Score
, Count(T2.qID) As ScoreNum
FROM tblScores As T1 LEFT JOIN
    (SELECT * FROM tblScores) As T2 
    ON T1.rName = T2.rName AND

...

postman · Feb 20 '15, 06:21 PM

I get this error when saving the query: "Syntax error in query expression 'Count(T2.*)'"

So I tried putting a field name: 'Count(T2.qID)' and that got past the error.

But when I ran the query, I got an error saying it did not include the specified expression 'qID' as part of an aggregate function--in reference to the qID in the SELECT statement at the beginning.

I really appreciate the help...

postman · Feb 20 '15, 04:03 PM

Yes fixed the Score field name.

Trying the crosstab again. I'll post results.

Do you know what NeoPa meant by "link in the data as a separate data source rather than use a Domain Aggregate function call" ?

Sounds like it might help the performance on the ScoreCount query that is getting the ScoreNum numbering.

postman · Feb 20 '15, 03:50 PM

Okay, it was my fault. Had the Score field name wrong. Fixed that and I don't get the prompt, but it's not giving the expected results.

It's just listing the first score and showing a different row for each record still.

And the final query runs extremely slow.

Any ideas? I really appreciate your help on this.

postman · Feb 20 '15, 03:42 PM

I tried that and I still get the prompt to enter a Parameter Value for Score.

I also removed lines 10-12 but still get the prompt.

Any other ideas?

postman · Feb 19 '15, 04:52 PM

Code:

SELECT MQ.qID
, MQ.[sMonth]
, MQ.[rName]
, MQ.MgrName
, S1.Score AS [Score 1]
, S2.Score AS [Score 2]
, S3.Score AS [Score 3]
, S4.Score AS [Score 4]
FROM (((([ScoreCount] AS MQ 
LEFT JOIN (SELECT qID, Score FROM [ScoreCount] WHERE ScoreNum = 1) As S1 ON MQ.qID = S1.qID) 
LEFT JOIN (SELECT qID, Score FROM [ScoreCount] WHERE ScoreNum = 2) As S2 ON MQ.qID = S2.qID) 
LEFT JOIN

...

postman · Feb 19 '15, 04:08 PM

Thanks.
That eliminated that error, but now it's prompting for a Score parameter when I run the query.

postman · Feb 19 '15, 01:57 PM

Seth, the performance is much better on the DCount function using the method in your example, thank you.

But I keep getting a "syntax error (missing operator)" on the second query example you gave with the subqueries. The error is in the section with the LEFT JOINS.

Any ideas?

postman · Feb 19 '15, 12:01 AM

NeoPa, could you please clarify or give an example of linking in the data as a separate data source?

Thanks.

postman · Feb 18 '15, 09:38 PM

Thanks, Seth.

I know about reserved names and ID fields. It was just a simple example to illustrate what I was after.

I actually tried this method with the DCount function before, but it ran extremely slow due to the aggregate function. But I see a few differences in your function's criteria that I'll try out.

I'll test and post results.

Thanks!

postman · Feb 18 '15, 07:17 PM

It would be a maximum of 4 scores.

postman · Dec 20 '11, 09:15 PM

Thank you, Rabbit! I will try that.

postman · Dec 15 '11, 09:16 PM

Rabbit, you have been immensely helpful and I don't want to take much more of your time, but could you point me in the right direction of how to open the database to execute the compiled code using javascript?

This is an example of the Javascript code in the front-end I'm using to insert the data into the database now:

Code:

function AddToDB() {
objConn = new ActiveXObject("ADODB.Connection");
conn_String

...

postman · Dec 15 '11, 07:06 PM

I see.
The function for encrypting the data would be in the javascript code in the web application, right? There's no way to have VBA Functions in the database file run unless the file is open, is there?

In your comment below you said to use an MDE (or ACCDE for 2007+) version of the database so the user couldn't get the key. But if the encryption function is in the front-end application, wouldn't a user be able to view...

postman · Dec 15 '11, 06:24 PM

Rabbit, could you expand on that, please? Do you mean simply encrypting the Access database file with a password?

If so, how do you allow the ActiveX ADO object in the local web application to access the database without supplying the password in the connection string (which is easily viewable)?

postman · Nov 17 '11, 01:43 AM

That's basically what I ended up doing. I did the calculations in hidden columns and just referred to those cells in the displayed columns using the TRUNC function.

I was just hoping there was a simpler, more elegant solution.

Thanks for your reply. You've saved me from researching further for no reason!

User Profile

Profile Sidebar

Leave a comment:

Leave a comment:

Leave a comment:

Leave a comment:

Leave a comment:

Leave a comment:

Leave a comment:

Leave a comment:

Leave a comment:

Leave a comment:

Leave a comment:

Leave a comment:

Combine multiple records into one row with multiple columns [SOLVED]

Leave a comment:

Leave a comment:

Leave a comment:

Leave a comment:

Write-only Access to Database on Network Drive?

Leave a comment:

Excel: Format Decimal Places Without Rounding or Changing Cell Value