Access Query 1

[joeyb629]

I have a query set up and would like to make another query from that query. here is a sample of the query i have created:

NUM NAME
1234 Adams,Joe
2743 Adams,Joe
2749 Griffin,Bob
4329 Smith,Bill
4876 Smith,Bill
4978 Smith,Bill

And this is how I want the query to come out:

NAME NUM NUM2 NUM3
Adams,Joe 1234 2743
Griffin,Bob 2749
Smith,Bill 4329 4876 4978


If anyone can help me out it would be greatly appreciated. Thank you.

 

[PGTurner]

Interesting project but I haven't been able to come up wiht with any bright ideas with straight queries. At least I don't see it yet.

I think my approach would be a temp table created & populated programatically.

But that could get ugly depending on how big your real table is and how many possibilities for numX columns.

 

[joeyb629]

i figured it was a basic query that i was just not noticing how to do it. THere is alot of records that would generate at the most 12 columns. maybe its more dificult then i originally thought.

 

[PGTurner]

Don't know if this will confuse more than it helps but barring a better inspiration, this is what I would do. Add the code to a module - watch line wrapping.

You will end up with a table named zTblTable1 that, I think, has what you asked for. The zTbl will be recreated each time the code runs so writing a report based on it would be a little weird unless it always had the same number ( you said 12 - I don't know the access limit on the top end ) of possibilities.


?TestThis("Table1", "FullName", "Number&quot

Function TestThis(TableName As String, GroupField As String, CountField As String)
'Call this from a macro or ?
  BuildTheTable TableName, GroupField, CountField
  TestThis = True
End Function


Sub BuildTheTable(TableName As String, GroupField As String, CountField As String)
    'Called for example:   BuildTheTable "Table1",  "FullName", "Number"
    ' Assumes Table1 has fields FullName & Number
    Dim tmpTbl As TableDef
    Dim db As Database
    Dim rst As Recordset
    Dim rst2 As Recordset
    Dim rstNewTable As Recordset
    Dim strTableName As String
    Dim strQuery As String
    Dim tmpMaxNumbersForAName As Long
    Dim i As Long
    Dim tmpErr As Long
    Dim tmpError As String
    Set db = CodeDb()
    ' Get the max count of values for a Groupfield
    tmpMaxNumbersForAName = MaxNumbersForAName(TableName, GroupField, CountField)
    
    strTableName = "zTbl" & TableName
    On Error Resume Next
    db.TableDefs.Delete strTableName
    tmpErr = Err.Number
    tmpError = Err.Description
    Err.Clear
    Select Case tmpErr
      Case 0     ' no error so table deleted OK
      Case 3265  ' table doesn't exist, we were going to whack it anyway, so keep going.
      Case Else
        GoTo Err_BuildTheTable
    End Select
    On Error GoTo Err_BuildTheTable
    Set tmpTbl = db.CreateTableDef(strTableName)
    
    With tmpTbl
        .Fields.Append .CreateField(GroupField, dbText)
        For i = 1 To tmpMaxNumbersForAName
        .Fields.Append .CreateField(CountField & Format(i, "000"), dbLong)
        Next i
    End With
    db.TableDefs.Append tmpTbl
    Set tmpTbl = Nothing
    Set rstNewTable = db.OpenRecordset("Select * from " & strTableName)
    Set rst = db.OpenRecordset("SELECT DISTINCT " & GroupField & " FROM " & TableName)
    While Not rst.EOF
      Set rst2 = db.OpenRecordset("SELECT " & CountField & " FROM " & TableName & _
                                  " where " & GroupField & " ='" & _
                                  rst.Fields(0).Value & "' Order by " & CountField)
      With rstNewTable
        .AddNew
        .Fields(0).Value = rst.Fields(0).Value
        i = 1
        While Not rst2.EOF
          .Fields(i).Value = rst2.Fields(0).Value
          rst2.MoveNext
          i = i + 1
        Wend
        .Update
      End With
      Set rst2 = Nothing
      rst.MoveNext
    Wend
    
Exit_BuildTheTable:
    Exit Sub

Err_BuildTheTable:
    If Err > 0 Then
      MsgBox Err & " - " & Error
    Else
      MsgBox tmpErr & " - " & tmpError
    End If
    Resume Exit_BuildTheTable
End Sub


Function MaxNumbersForAName(TableName As String, GroupField As String, CountField As String)
On Error GoTo Err_MaxNumbersForAName

    Dim tmpqry As QueryDef
    Dim db As Database
    Dim rst As Recordset
    Dim strQueryName As String
    Dim strQuery As String
    
    Set db = CodeDb()
    strQueryName = "zQry1" & TableName
               strQuery = "SELECT " & GroupField & ", Count("
    strQuery = strQuery & CountField & ") AS CountOf" & CountField
    strQuery = strQuery & " FROM " & TableName
    strQuery = strQuery & " GROUP BY " & GroupField

On Error Resume Next
   Set tmpqry = db.CreateQueryDef(strQueryName, strQuery)
   Select Case Err.Number
     Case 0
     Case 3012      ' The query already existed but this will overwrite.
       Set tmpqry = db.QueryDefs(strQueryName)
       tmpqry.SQL = strQuery
     Case Else
       GoTo Err_MaxNumbersForAName
   End Select
On Error GoTo Err_MaxNumbersForAName

   ' to pop the tmp query at this point
   ' DoCmd.OpenQuery strQueryName, acViewNormal, acEdit
   
'Now build a second query to find the highest count of numbers - should return just 1 record.
               strQuery = "SELECT Max(CountOf" & CountField & " ) AS MaxOfCountOf" & CountField
    strQuery = strQuery & " FROM " & strQueryName
   
    strQueryName = "zQry2" & TableName
   
On Error Resume Next
   Set tmpqry = db.CreateQueryDef(strQueryName, strQuery)
   Select Case Err.Number
     Case 0
     Case 3012  ' The query already existed but this will overwrite.
       Set tmpqry = db.QueryDefs(strQueryName)
       tmpqry.SQL = strQuery
     Case Else
       GoTo Err_MaxNumbersForAName
   End Select
On Error GoTo Err_MaxNumbersForAName
   
     Set rst = db.OpenRecordset(strQueryName)
   If Not rst.EOF Then
     MaxNumbersForAName = rst.Fields(0).Value
   Else
     MaxNumbersForAName = -1
   End If
   
   ' to pop the tmp query at this point
   ' DoCmd.OpenQuery strQueryName, acViewNormal, acEdit
   
   Set rst = Nothing
   Set tmpqry = Nothing
   Set db = Nothing
   
Exit_MaxNumbersForAName:
    Exit Function

Err_MaxNumbersForAName:
    MsgBox Err.Number & " - " & Err.Description
    MaxNumbersForAName = -2
    Resume Exit_MaxNumbersForAName
    
End Function

 

[scriverb]

In this example I used a table called tblNamesNum). Fields: Name Text(20), Num Num(lng).

Create a query with the following SQL and name it qryNamesNum:
SELECT tblNamesNum.Name, tblNamesNum.Num
FROM tblNamesNum
ORDER BY tblNamesNum.Name, tblNamesNum.Num;

Create a query with the following SQL and name it qryMTNamesNum:
SELECT tblNamesNum.Name, 0 AS Num1, 0 AS Num2, 0 AS Num3, 0 AS Num4, 0 AS Num5, 0 AS Num6, 0 AS Num7, 0 AS Num8, 0 AS Num9, 0 AS Num10, 0 AS Num11, 0 AS Num12 INTO tblNamesNumGrouped
FROM tblNamesNum
GROUP BY tblNamesNum.Name, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0
ORDER BY tblNamesNum.Name;

Use the following code to create a temp table and update the columns with the appropriate data as requested:
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rss As DAO.Recordset
Dim i As Integer
Dim vFieldName As String
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryMTNamesNum"
DoCmd.SetWarnings True
Set db = CurrentDb
Set rs = db.OpenRecordset("qryNamesNum", dbOpenDynaset)
Set rss = db.OpenRecordset("tblNamesNumGrouped", dbOpenDynaset)
rs.MoveFirst
Do
rss.FindFirst "[Name] = '" & rs("Name&quot & "'"
i = 1
Do
rss.Edit
vFieldName = "Num" & i
rss(vFieldName) = rs("Num&quot
rss.Update
rs.MoveNext
If Not rs.EOF Then
If rs(&quot;Name&quot <> rss(&quot;Name&quot Then
i = 13
End If
End If
i = i + 1
Loop Until rs.EOF Or i > 12
Loop Until rs.EOF

This should get your the table(tblNamesNumGrouped with the numbers laid out horizontally in one record as requested.


Bob Scriver

 

[joeyb629]

thanks guys... i will give both those options a shot on monday when i go into work

 

[joeyb629]

ok this might sound like a basic question:

I have all the queries made but i have no clue where to put the last bit of code you wrote...something about a temp table...where do i actually put that code?

 

[PGTurner]

If you are asking me, the code will create the table & queries.

On my version, &quot;temp&quot; is sort of a misnomer in that they do not go away when you leave ( unless you delete them ). The table & query names are re-used - the content could change on each run but they will stay there between runs.

 

[scriverb]

joeyb629: The VBA code to trigger the queries should be put behind a command button on a form. Copy and Paste the code into the OnClick event procedure of the command button.

Let me know if you need more assistance. Bob Scriver

 

[joeyb629]

im actually using bob's code first to see if i can get his to work. his uses more code that i know how to use. i dont even know where to begin on your code. I'm a very basic Access user and don't really understand where any of your code would be placed. I'm used to going to SQL View and typing Select statements to create queries. I don't really understand where the other information he gave me and all of your information would be inserted to get it to run.

 

[joeyb629]

ok i understand what you are saying bob but all i really want is to run the query and then use the information it creates in other queries or forms. i really don't need any command buttons or anything like that.

 

[PGTurner]

For all the code, it needs to go into either a module or behind an object ( eg a command button ) on a form.

Code can also go behind objects in reports as well.

Just to be more complete, I will describe how I would use what I posted.

Create a new module. One of the Access tabs. Paste all the code into it. Save the module - Module1 should be fine.

Read the code. You will very likely be able to decipher roughly what is happening. In general, you would not want to paste someone's code into your system and execute it without at least some study.

If it doesn't make sense, ask.

Now it is in place, you just need to run it. Depending on how you want to use it there are many different ways. Probably the simplest is to press CTRL-G and get to an Immediate pane in the Debug window - a command line where you can type.

(Apologies if you are familiar with this.)

Try typing: Print &quot;hello world&quot; then hit enter

Try ?now() <enter>

Then try
BuildTheTable &quot;Table1&quot;, &quot;FullName&quot;, &quot;Number&quot;

Replace Table1 with the name of your query or table (use [] if there are spaces or hyphens in the name. Replace FullName & Number with your fieldnames. If you are using spaces in your field names my code will probably fail - I didn't build that much flexibility in.

Then hit enter again.

It won't look like much of anything happened but if you look in queries you will see two new queries named zQry... & zQry2... and a new table name zTbl...

The zTbl... is my vision of what I think you are looking for.

Pete

 

[scriverb]

joeyb629: There are always more than one way to skin a cat. But, if you want to use what I gave you just follow these simple instructions.
1. Make a new form. Click the Forms Tab and Click the New button. Then click OK. You now have a blank form.
2. Click the Toolbox button from the Forms Design Toolbar. It has a Hammer/Wrench icon. Now select the Command Button tool from the Toolbox. Looks like a Rectangle. Just put your cursor over the buttons and your Tooltips will tell you what they are.
3. Now with your mouse Click on your form, hold down the left button, and drag diagonally to make a command button, release mouse button.
4. Right click the Command button and select from the dropdown Properties. Click the Events tab. Click into the OnClick event procedure box and select Event Procedure. Now click the small command button to the right with three periods in it.
5. You are now in the Onclick of the event procedure. Copy and Paste the code I posted inside of the two lines now showing. Close the window. Close and save the form. Naming it what ever you want.
6. Now select the form name from the list of forms in your Form Tab. Click the Open button. To run the code and create the file you requested click the Command Button.

You either have to learn this process to run code or create a macro to select and run the code from a macro. If you are going to use ACCESS you should learn how to create these little forms that are used to run code.

let me know if you get stuck. Bob Scriver

 

[joeyb629]

scriverb:

ok i did what you said and now im getting a run-time error on this line:

rss.FindFirst &quot;[Name] = '&quot; & rs(&quot;Name&quot & &quot;'&quot;

its saying its a syntax error (missing operator)

Any ideas??

 

[scriverb]

Send me an email(see my address in my profile) and I will return a small database that works and you can go from there. Bob Scriver

 

[joeyb629]

email sent

 

[joeyb629]

thanks bob for your help. the database works great now. I was having a little problem with it not producing enough columns but you worked that out, thanks again for all your help.