Duplicate Entires - Follow up

[Guest]

I am trying to modify a sql code based on John Spencer's thread on 2/5/07.
Ideally I want my query result to show the count of each unique value grouped
by Lane, by LastName.

Supporting data:
Lane LastName MA_ID INT_ID INR_ID PRN_ID
A Smith 1264 23057 13128 3715
A Smith 1264 23057 13341 3705
B Jones 1264 23000 13144 3705

SELECT Count(Tmp.MA_ID) as CT_MA_ID
FROM
(SELECT DISTINCT MA_ID
FROM qryBPAI) as Tmp

My current attempt shows a total of MA_ID but I would like to include the
other columns and a grouping in a format as below. Any help would be
appreciated.

Lane LastName MA_ID INT_ID INR_ID PRN_ID
A Smith 1 1 2 2
A Jones 1 1 1 1

 

[Gary Walter]

I am trying to modify a sql code based on John Spencer's thread on 2/5/07.
Ideally I want my query result to show the count of each unique value
grouped
by Lane, by LastName.

Supporting data:
Lane LastName MA_ID INT_ID INR_ID PRN_ID
A Smith 1264 23057 13128 3715
A Smith 1264 23057 13341 3705
B Jones 1264 23000 13144 3705

SELECT Count(Tmp.MA_ID) as CT_MA_ID
FROM
(SELECT DISTINCT MA_ID
FROM qryBPAI) as Tmp

My current attempt shows a total of MA_ID but I would like to include the
other columns and a grouping in a format as below. Any help would be
appreciated.

Lane LastName MA_ID INT_ID INR_ID PRN_ID
A Smith 1 1 2 2
A Jones 1 1 1 1

Hi Brian,

Is the "supporting data" a table,
or result of a query (qryBPAI?).

The reason I ask is because if the
data was "thin," I would think the task
would be easier and faster...

Lane LastName f f_val
A Smith MA_ID 1264
A Smith INT_ID 23057
A Smith INR_ID 13128
A Smith PRN_ID 3715
A Smith MA_ID 1264
A Smith INT_ID 23057
A Smith INR_ID 13341
A Smith PRN_ID 3705

Do you see what I mean?

A simple crosstab w/ Lane and
LastName as row header, f as PIVOT
on a simple DISTINCT Query of
all fields in table above would let you
count distinct values in the TRANSFORM.

for example, if thin table above were "tblThin"

qryDistinctThin:

SELECT DISTINCT
Lane, LastName, f, f_val
FROM
tblThin;

payoff:

TRANSFORM Count(q.f_val)
SELECT
q.Lane,
q.LastName
FROM
qryDistinctThin AS q
GROUP BY
q.Lane,
q.LastName
PIVOT
q.f IN ("MA_ID","INT_ID","INR_ID","PRN_ID");

So...the question is whether the qryBPAI
can be rethought to produce "thin."

Or...if this is not a process that is run frequently,
I have a routine that will convert your sample data
(query or table) to a thin table...

good luck,

gary

 

[Guest]

If the data is formatted as in the "Supporting Data" section below, you have
some work ahead of you.

1. First step is to create a union query that looks something like:

SELECT DISTINCT Lane, LastName, "MA_ID" as Field_Name, MA_ID as Value
FROM yourTable
UNION ALL
SELECT DISTINCT Lane, LastName, "INT_ID", INT_ID FROM yourTable
UNION ALL
SELECT DISTINCT Land, LastName, "INR_ID", INR_ID FROM yourTable
UNION ALL
SELECT DISTINCT Land, LastName, "PRN_ID", PRN_ID FROM yourTable

Save this query (qry_Union)

2. Now, create a crosstab query using the previous query (qry_Union)
Add all 4 fields to the query, make the Lane and LastName fields Row
Headings, Field_Name as a Column Heading, and the Values column as a Count

3. This might run quicker if the Union query used a GroupBy for each
section. I've recently seen some discussion on one of the Access newsgroups
that indicates that a GroupBy query runs quicker than an equivelant DISTINCT
query. It would look something like:

SELECT Lane, LastName, "MA_ID" as Field_Name, MA_ID as Value
FROM yourTable
GROUP BY Lane, LastName, Field_Name, "MA_ID", MA_ID

HTH
Dale

 

[Guest]

Dale,
Right out of the gate, my Union Query is giving me a problem. When I run an
error message says: The SELECT statement includes a reserved word or argument
name that is misspelled or missing or the punctuation is incorrect. Your
thoughts?

SELECT DISTINCT Lane, LastName, "MABR_ID" as Field_Name, MABR_ID as Value
FROM qryBPAI
UNION ALL
SELECT DISTINCT Lane, LastName, "INT_ID", INT_ID FROM qryBPAI
UNION ALL
SELECT DISTINCT Lane, LastName, "INR_ID", INR_ID FROM qryBPAI
UNION ALL
SELECT DISTINCT Lane, LastName, "PRN_ID", PRN_ID FROM qryBPAI

 

[Gary Walter]

just in case you run into trouble trying
to use a Union query for your xtab...

save following in a code module (say "modThin"),
then in Immediate Window type

fImportToThinTablePreserve2Fields "qryBPAI","tblThin","Lane","LastName"

then hit ENTER

{reference to DAO will need to be set of course}

'********* code start *************
Public Sub fImportToThinTablePreserve2Fields( _
pFromTable As Variant, _
pToTable As Variant, _
pPreserveField1 As Variant, _
pPreserveField2 As Variant)
On Error GoTo Err_fImportToThinTablePreserveField

'in Immediate Window
'fImportToThinTablePreserve2Fields "qryBPAI","tblThin","Lane","LastName"
'
Dim db As DAO.Database
Dim rsFrom As DAO.Recordset
Dim rsTo As DAO.Recordset
Dim Response, strMsg As String, varReturn
Dim strSQL As String
Dim strPreserve1 As String
Dim strPreserve2 As String
Dim lngPreserveFieldCnt As Long
Dim lngVal As Long
Dim lngRecNum As Long, i As Long


'check that pFromTable is not null nor ZLS
If Len(Trim(pFromTable & "")) > 0 Then
'check that pToTable is not null nor ZLS
If Len(Trim(pToTable & "")) > 0 Then
'continue processing
Else
MsgBox "Please provide name of thin table " _
& "you wish to fill with number data."
GoTo Exit_fImportToThinTablePreserveField
End If
Else
MsgBox "Please provide name of wide table " _
& "with many number fields."
GoTo Exit_fImportToThinTablePreserveField
End If


strMsg = "Will be importing number data from the following table:" _
& vbCrLf & vbCrLf & pFromTable & vbCrLf & vbCrLf _
& "into the following thin table:" _
& vbCrLf & vbCrLf & pToTable
Response = MsgBox(strMsg, vbOKCancel)
If Response = vbCancel Then ' User chose to Cancel
GoTo Exit_fImportToThinTablePreserveField
End If


DoCmd.Hourglass True

Set db = CurrentDb

'delete pToTable if it exists
If TableExists(CStr(pToTable)) Then
'if it exists, delete it
db.Execute "DROP TABLE " & pToTable, dbFailOnError
End If

'recreate pToTable
'do we have a pPreserveField1 and pPreserveField2?
If Len(Trim(pPreserveField1 & "")) > 0 _
And Len(Trim(pPreserveField2 & "")) > 0 Then
strSQL = "CREATE TABLE " & pToTable & " (ID AUTOINCREMENT, " _
& "FldPreserve1 TEXT, FldPreserve2 TEXT, FldName TEXT, " _
& "FldValue LONG, " _
& "CONSTRAINT PK_ID PRIMARY KEY (ID ));"
'Debug.Print strSQL
db.Execute strSQL, dbFailOnError
Else
'no Preserve field
MsgBox "Please provide names of both 'Preserve Fields.'"
GoTo Exit_fImportToThinTablePreserveField
End If


Set rsFrom = db.OpenRecordset(pFromTable, dbOpenDynaset)
'quit if empty table
If rsFrom.EOF = True Then
rsFrom.Close
MsgBox pFromTable & " does not contain any records.", vbCritical
GoTo Exit_fImportToThinTablePreserveField
Else
'continue
End If


Set rsTo = db.OpenRecordset(pToTable, dbOpenDynaset)


rsFrom.MoveFirst
lngRecNum = 0
Do While Not rsFrom.EOF
lngRecNum = lngRecNum + 1


'****** update progress display in status bar **********
varReturn = SysCmd(acSysCmdSetStatus, "Processing Rec # " _
& lngRecNum)

'get values of preserve fields
lngPreserveFieldCnt = 0
For i = 0 To rsFrom.Fields.Count - 1
If rsFrom.Fields(i).Name = pPreserveField1 Then
strPreserve1 = rsFrom.Fields(i) & ""
lngPreserveFieldCnt = lngPreserveFieldCnt + 1
Else
If rsFrom.Fields(i).Name = pPreserveField2 Then
strPreserve2 = rsFrom.Fields(i) & ""
lngPreserveFieldCnt = lngPreserveFieldCnt + 1
Else
If lngPreserveFieldCnt = 2 Then Exit For
End If
End If

Next i
'save record in thin table
For i = 0 To rsFrom.Fields.Count - 1
With rsTo
If rsFrom.Fields(i).Name <> pPreserveField1 _
And rsFrom.Fields(i).Name <> pPreserveField2 _
And rsFrom.Fields(i) <> 0 Then
.AddNew
!FldPreserve1 = strPreserve1
!FldPreserve2 = strPreserve2
!FldName = rsFrom.Fields(i).Name
!FldValue = rsFrom.Fields(i)
.Update
Else

End If
End With
Next i


rsFrom.MoveNext
Loop


'clear display in status bar
varReturn = SysCmd(acSysCmdClearStatus)

'close recordsets
rsFrom.Close
rsTo.Close

'in table pToTable,
'rename FldPreserve1 to pPreserveField1

strSQL = "ALTER TABLE " & pToTable & " ADD COLUMN " & pPreserveField1 &
" TEXT;"
db.Execute strSQL, dbFailOnError

strSQL = "UPDATE " & pToTable & " SET " & pPreserveField1 & "=
FldPreserve1;"
db.Execute strSQL, dbFailOnError

strSQL = "ALTER TABLE " & pToTable & " DROP COLUMN FldPreserve1;"
db.Execute strSQL, dbFailOnError

'rename FldPreserve2 to pPreserveField2

strSQL = "ALTER TABLE " & pToTable & " ADD COLUMN " & pPreserveField2 &
" TEXT;"
db.Execute strSQL, dbFailOnError

strSQL = "UPDATE " & pToTable & " SET " & pPreserveField2 & "=
FldPreserve2;"
db.Execute strSQL, dbFailOnError

strSQL = "ALTER TABLE " & pToTable & " DROP COLUMN FldPreserve2;"
db.Execute strSQL, dbFailOnError

db.Close

MsgBox "Have successfully imported number data from " & vbCrLf _
& pFromTable & vbCrLf & " into table " & vbCrLf & pToTable & "."


Exit_fImportToThinTablePreserveField:
DoCmd.Hourglass False
Set rsFrom = Nothing
Set rsTo = Nothing
Set db = Nothing
Exit Sub


Err_fImportToThinTablePreserveField:
MsgBox Err.Description
Resume Exit_fImportToThinTablePreserveField
End Sub


Public Function TableExists(strTableName As String) As Boolean
'from Joe Fallon
On Error Resume Next
TableExists = IsObject(CurrentDb.TableDefs(strTableName))
End Function
'***** code end ***********

good luck,

gary

 

[Gary Walter]

possibly that "Value" is a reserved word?

try naming it FieldVal

 

[Guest]

Dale, Gary,
This code works very well. TY for your help and advice.

 

[Guest]

Brian,

You are welcome.

BTW, If you have any say about the databases structure, I would recommend
that you normalize the table so that it is in the structure that my query
returns. The way it is setup now, it really looks and works more like a
spreadsheet than a query. With the data normallized, you can add new
categories (your column headers) by just adding a new data item rather than
having to add new columns to your data structure. It is also much simplier
and faster to get answers to questions like yours when the data is normalized.

Dale

 

[Guest]

Thanks Dale.
Here is a question that may be somewhat obvious to you but is escaping my
brain waves. When I inherited this db, I looked at this original query and
saw that it was based on four other queries (which were based on countless
other queries). I thought that I could do better and with your help have
this SQL. I know that I am doing something majorly wrong though as my end
result runs slower (20 seconds) than the earlier "poorly written" version
which zips along at about one second of processing time. Nothing in the
table structure has changed so I'm thinking of establishing parameters in the
SQL. Your thoughts?

 

[Dale Fye]

Brian,

Post your final SQL and maybe we can tweak it to make it run faster. Are
you creating the SQL dynamically (in code) and then executing it, or do you
have a saved query. Saved queries generally run quicker because they have
already been compiled, so if you can create and save the query with
parameters, and then just pass it the parameters it should run quicker.

Occassionally, there are advantages to writing queries and using them in
other queries. One of those times is when you can shrink the set of records
you are retrieving early on in the process. So if you have a query that
joins a couple of tables and ends up with 100,000 records, then try to
restrict that with a where clause, you will probably find that it takes
longer to run than if you create a couple of queries to restrict the number
of records retrieved from the original tables, then join them.

Dale

 

[Guest]

I have a similar situation, which I would like to extend the query below to
add a new field which will be populated with the count of duplicates in each
aggregate of records in a field (NewField1), displayed beside the original
(NewField1), which will then be displaying only the records as unique in the
result of (NewField1).

SELECT table22.*
FROM table22
WHERE NewField1 IN
(SELECT NewField1
FROM table22
GROUP BY NewField1
HAVING COUNT(NewField1) > 1)

 

[John Spencer]

I only see using either Dcount or a subquery to do this in your query.
Assuming NewField1 is a number field

SELECT table22.*
, DCount("*","Table22","NewField1 =" & Table22.NewField1) as TheCount
FROM table22
WHERE NewField1 IN
(SELECT NewField1
FROM table22
GROUP BY NewField1
HAVING COUNT(NewField1) > 1)

OR
SELECT table22.*
, (SELECT Count(NewField1)
FROM Table22 as T
WHERE T.NewField1 = Table22.NewField1 ) as TheCount
FROM table22
WHERE NewField1 IN
(SELECT NewField1
FROM table22
GROUP BY NewField1
HAVING COUNT(NewField1) > 1)

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..