Consolidate like information

[gal4y]

I have two tables

1. tblConference_data (info about conferences)
Name, Start Date, Location etc.

2. tblConference_coverage (what faculty is signed up for what conferences)
Name, FacultyID

The SQL:
SELECT [Conferences-Tbl].Name, [Conferences-Tbl].[Start Date], [Conferences-Tbl].[End Date], [Conferences-Tbl].Location, [Conferences-Tbl].[URL to website], [Conference_Coverage-Tbl].Facutly
FROM [Conferences-Tbl] INNER JOIN [Conference_Coverage-Tbl] ON [Conferences-Tbl].ID = [Conference_Coverage-Tbl].ConfID
ORDER BY [Conferences-Tbl].Name, [Conferences-Tbl].[Start Date];

THIS IS A SUBSET OF THE QUERY

Name Start Date FacultyID
2003 IERC Sunday, May 18, 2003 Jeff
2003 IERC Sunday, May 18, 2003 Tina
2003 IERC Sunday, May 18, 2003 Harry
AORS Frank
AORS Mike

I would like the query and eventually the report to look like this:
1. Consolidate Information
2. If no date exists: input NEED DATE

Name Start Date FacultyID
2003 IERC Sunday, May 18, 2003 Jeff, Tina, Harry
AORS Frank, Mike


Thank you for any guidance or assistance.
Greg

 

[PaulBricker]

Greg, the first part is easy. In the Name and StartDate field textboxes, set the Hide Duplicates Property to Yes. That will give you

Name           Start Date        FacultyID
2003 IERC   Sunday, May 18, 2003   Jeff
                                   Tina
                                   Harry
AORS                               Frank
                                   Mike

But to get the names on the same line would require coding. First create a Totals query that has just the info in it to uniquely identify your conferences. So if you need 2003 IERC and AORS then just add the Conference name and Group By that value. If you need the conference name and the date, add them both and Group By both. I'll call this query qryConfer. I'll call the table that holds your conference info tblConfer and the temporary table to hold the Report info tblTemp.

Dim rst as DAO.Recordset
Dim rst2 as DAO.Recordset
Dim rst3 as DAO.REcordset
Dim strHolder as String
Dim strSQL as String
Set rst3 = CurrentDb.OpenRecordset("tblTemp",dbOpenDyanset)
Set rst = CurrentDb.OpenRecordset("qryConfer",dbOpenDynaset)
rst.MoveFirst
Do Until rst.EOF
strSQL = "Select * From tblConferWhere Name = '" & rst!Name
set rst2 = CurrentDb.OpenRecordset(strSQL,dbOpenDynaset)
rst2.MoveFirst
Do Until rst2.EOF
strHolder = strHolder & ", " & rst2!Name
rst.MoveNext
Loop
rst3.AddNew
rst3!Name = rst2!Name
rst3!StartDate = rst2!StartDate
rst3!FacultyID = Mid(1,strHolder,Len(strHolder)-2)
rst3.Update

rst.MoveNext
Loop
Set rst = Nothing
Set rst2 = Nothing
Set rst3 = Nothing

This should put the info into tblTemp in the manner you want and then run your report from tblTemp.

Paul

 

[gal4y]

Paul,

I will try it.

Thanks

Greg

 

[gal4y]

Paul,

I put this code behind a command box
I am getting a run time error:

Run time error 3001
Invalid arguement.

The line for the error is the one with the $$:

Set rst3 = CurrentDb.OpenRecordset("tblTemp", dbOpenDyanset)

Private Sub Command0_Click()
Dim rst As DAO.Recordset
Dim rst2 As DAO.Recordset
Dim rst3 As DAO.Recordset
Dim strHolder As String
Dim strSQL As String
$$ Set rst3 = CurrentDb.OpenRecordset("tblTemp", dbOpenDyanset)
Set rst = CurrentDb.OpenRecordset("Query1", dbOpenDynaset)
rst.MoveFirst
Do Until rst.EOF
strSQL = "Select * From Conferences-Tbl Where Name = '" & rst!Name
Set rst2 = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
rst2.MoveFirst
Do Until rst2.EOF
strHolder = strHolder & ", " & rst2!Name
rst.MoveNext
Loop
rst3.AddNew
rst3!Name = rst2!Name
rst3!StartDate = rst2!StartDate
rst3!EndDate = rst2!EndDate
rst3!Location = rst2!Location
rst3!URL_to_website = rst2!URL_to_website

rst3!FacultyID = Mid(1, strHolder, Len(strHolder) - 2)
rst3.Update

rst.MoveNext
Loop
Set rst = Nothing
Set rst2 = Nothing
Set rst3 = Nothing
End Sub


Did I put it in the wrong place? Do I have to create the temporary table or does the code make that.
The first part worked with the hidden property.

Thanks
Greg

 

[DanJR]

Hi there,

There's a very small spelling mistake (dbOpenDynaset not dbOpenDyanset)....

replace the line:

Set rst3 = CurrentDb.OpenRecordset("tblTemp", dbOpenDyanset)

with this line:

Set rst3 = CurrentDb.OpenRecordset("tblTemp", dbOpenDynaset)


Will you also need to create a table called "tblTemp".

Before running the code, you may wish to complie it (under Debug in the toolbar) to check for syntax errors


Cheers,
DanJR

 

[PaulBricker]

Greg, sorry about the typo, my second one on that word today.
Dan is correct, you will have to create the temporary table to hold the values. If you need to run this report over and over again, we may want to create a Delete query that empties tblTemp before adding new values to it. That way the information is always correct. If you need help with that let us know.

Paul

 

[gal4y]

Thanks, Dan and Paul.

I am getting an error

I added a tblTemp and changed the one line. I added three watches and compiled it. OK until

Run Time error 3075
Syntax error in string in query expression 'Name = '2003 IERC'.
2003 IERC is the first conference in the query.

The error line occus at:
Set rst2 = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

The watches for rst and rst3 track all of the fields but I am getting nothing for rst2 because of the error.

Thanks again for any help.
Greg

 

[DanJR]

strSQL is missing a single quote

replace:

strSQL = "Select * From Conferences-Tbl Where Name = '" & rst!Name

with:

strSQL = "Select * From [Conferences-Tbl] Where [Name] = '" & rst!Name & "'"

Cheers,
Dan

 

[DanJR]

Just for interest sake, another way to achieve what you are doing is to base the report on a query which calls a custom function to create the 'faculty list'.

The method of creating the list is exactly the same, except the query calls a function (vba code) to generate the list and thus, no temporary table is needed.

However, this method can be a real pain to debug and may be slow your report down. I would definately keep to Paul's method (its a good way to do it), but I thought I would include an 'alternative' method for interest sake only (i.e., I'm just mucking around).

-------

1. Create the following function in a module:

Public Function fGetFacultyList(ConferenceID As Long) As String
Dim rs As DAO.Recordset
Dim strSQL As String
Dim strNames As String

On Error GoTo errGetList

'get list of faculty names
strSQL = "SELECT [FacultyID] FROM [Conference_Coverage-Tbl] WHERE [ConfID] = " & ConferenceID
Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenForwardOnly)


'turn list into one string
strNames = ""

Do Until rs.EOF
If strNames = "" Then
strNames = rs.Fields("FacultyID&quot.Value
Else
strNames = strNames & ", " & rs.Fields("FacultyID&quot.Value
End If

rs.MoveNext

Loop

fGetFacultyList = strNames

CleanUp:
Set rs = Nothing
Exit Function

errGetList:
Debug.Print "Error in fGetFacultyList (" & Err.Number & &quot " & Err.Description
Resume CleanUp

End Function

In the above function, I've assumed the field 'FacultyID' contains the name of the people.

2. Create a query that lists the data the way you want it (without the faculty name included). That is, the query will look like....

Name Start Date
2003 IERC Sunday, May 18, 2003


3. In design view of your query, add another field with the following expression:

FacultyID:fGetFacultyList([ConfID])

Now you should have a query that looks like this:

Name Start Date FacultyID
2003 IERC Sunday, May 18, 2003 Jeff, Tina, Harry

4. Now, create the report from this query.

Anyway....

 

[PaulBricker]

Good point about the query. It would always be current and it would eliminate the need for a third table. To get the query the way you need it to be, you would want a Totals query. Group By the Conference and Date and then just run the Function against that. Much neater all around.

Paul

 

[gal4y]

Paul,

I am getting a Run Time error 3021, No current Record in the line rst.MoveNext.

The watches on rst2!Name the other rst2 items show items for the first record.
rst3!StartDate = rst2!StartDate
rst3!EndDate = rst2!EndDate
rst3!Location = rst2!Location
rst3!URL_to_website = rst2!URL_to_website

but on strHolder shows and excessive amount of 2003 EIRC (First Conference) items.

There are no items in the tbl_temp.

I also tried the other way, I understand that way but I am getting blanks in the faculty field of the query. My original query that yield the conferences with their associated data without duplicates. I am not sure if I input that on the strSQL line. I am also not sure what the ConferenceID at the end does or if I should have that as a field somewhere.

strSQL = "SELECT [Faculty] FROM [Conference_Coverage_Tbl] WHERE [ConfID] = " & ConferenceID

SELECT Conferences_Tbl.Name, Conferences_Tbl.StartDate, Conferences_Tbl.EndDate, Conferences_Tbl.Location, Conferences_Tbl.URL_to_website
FROM Conferences_Tbl INNER JOIN [Conference_Coverage-Tbl] ON Conferences_Tbl.ID = [Conference_Coverage-Tbl].ConfID
GROUP BY Conferences_Tbl.Name, Conferences_Tbl.StartDate, Conferences_Tbl.EndDate, Conferences_Tbl.Location, Conferences_Tbl.URL_to_website
ORDER BY Conferences_Tbl.Name;

Thank for your help, I am learning a lot.
Greg

 

[PaulBricker]

Greg, I've lost track of exactly which method you are using right now and I can't tell by your last post which it is. It will make a difference because of how they will get the information. We need to either use a function we call from the query, or a Subroutine that does all the work and puts the data into a temptable. Let me know. Post the code you are using as well. It shouldn'ttake much to set this straight.

Paul

 

[gal4y]

Paul,

I was using both of the methods. Here is the code for the function method.

Public Function fGetFacultyList(ConferenceID As Long) As String
Dim rs As DAO.Recordset
Dim strSQL As String
Dim strNames As String

On Error GoTo errGetList

'get list of faculty names
strSQL = "SELECT [Faculty] FROM [Conference_Coverage_Tbl] WHERE [ConfID] = " & ConferenceID
Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenForwardOnly)


'turn list into one string
strNames = ""

Do Until rs.EOF
If strNames = "" Then
strNames = rs.Fields("Faculty&quot.Value
Else
strNames = strNames & ", " & rs.Fields("Faculty&quot.Value
End If

rs.MoveNext

Loop

fGetFacultyList = strNames

CleanUp:
Set rs = Nothing
Exit Function

errGetList:
Debug.Print "Error in fGetFacultyList (" & Err.Number & &quot " & Err.Description
Resume CleanUp

End Function

My SQL Query without the Faculty:
SELECT Conferences_Tbl.Name, Conferences_Tbl.StartDate, Conferences_Tbl.EndDate, Conferences_Tbl.Location, Conferences_Tbl.URL_to_website
FROM Conferences_Tbl INNER JOIN [Conference_Coverage-Tbl] ON Conferences_Tbl.ID = [Conference_Coverage-Tbl].ConfID
GROUP BY Conferences_Tbl.Name, Conferences_Tbl.StartDate, Conferences_Tbl.EndDate, Conferences_Tbl.Location, Conferences_Tbl.URL_to_website
ORDER BY Conferences_Tbl.Name;

It gives me the following:
Name StartDate EndDate Location URL FacultyID

2003IERC May 18, 2003 May 20, 2003 Portland, OR

http://fie.engrng.pitt.edu/iie/ierc/

2003IERC May 18, 2003 May 20, 2003 Portland, OR

http://fie.engrng.pitt.edu/iie/ierc/

2003IERC May 18, 2003 May 20, 2003 Portland, OR

http://fie.engrng.pitt.edu/iie/ierc/

AORS Ft. Lee, VA Host: Concepts Analysis Agency
AORS Ft. Lee, VA Host: Concepts Analysis Agency

When I add the Faculty: fGetFacultyList([ConfID]) it is blank.
The query is made with two tables:
Conferences_Tbl: Contains the information about the conferences (name, data, web, location, etc.)
Conference_Coverage-Tbl: Contains the conference name (ConfID) and the faculty that signed up for it.
The query joins those two tables.


The make a Temp_tbl way (the first solution you gave me)gives me a Run Time error 3021, No current Record in the line rst.MoveNext.

The watches on rst2!Name the other rst2 items show items for the first record.
rst3!StartDate = rst2!StartDate
rst3!EndDate = rst2!EndDate
rst3!Location = rst2!Location
rst3!URL_to_website = rst2!URL_to_website

but on strHolder shows and excessive amount of 2003 EIRC (First Conference) items.

There are no items in the tbl_temp.
Here is the entire code for the first way:

Private Sub Command0_Click()
Dim rst As DAO.Recordset
Dim rst2 As DAO.Recordset
Dim rst3 As DAO.Recordset
Dim strHolder As String
Dim strSQL As String
Set rst3 = CurrentDb.OpenRecordset("tblTemp", dbOpenDynaset)
Set rst = CurrentDb.OpenRecordset("Query1", dbOpenDynaset)
rst.MoveFirst
Do Until rst.EOF
strSQL = "Select * From [Conferences_Tbl] Where [Name] = '" & rst!Name & "'"
Set rst2 = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
rst2.MoveFirst
Do Until rst2.EOF
strHolder = strHolder & ", " & rst2!Name
rst.MoveNext
Loop
rst3.AddNew
rst3!Name = rst2!Name
rst3!StartDate = rst2!StartDate
rst3!EndDate = rst2!EndDate
rst3!Location = rst2!Location
rst3!URL_to_website = rst2!URL_to_website
rst3!Faculty = Mid(1, strHolder, Len(strHolder) - 2)
rst3.Update

rst.MoveNext
Loop
Set rst = Nothing
Set rst2 = Nothing
Set rst3 = Nothing
End Sub

I like the first way because it is easier to understand but I just need help to make either work. Lets focus on the first way.

Thanks
Greg

 

[PaulBricker]

Greg, both codes look OK so let's take one at a time and see if we can get the temp_tbl way first. If we get that, then we'll get the Function and you can see which works better for you. The first thing I notice is Query1. The SQL for Query1 should read
SELECT Conferences_Tbl.ID, Conferences_Tbl.Name, Conferences_Tbl.StartDate, Conferences_Tbl.EndDate, Conferences_Tbl.Location
FROM Conferences_Tbl
GROUP BY Conferences_Tbl.ID, Conferences_Tbl.Name, Conferences_Tbl.StartDate, Conferences_Tbl.EndDate, Conferences_Tbl.Location, Conferences_Tbl.URL_to_website
ORDER BY Conferences_Tbl.Name;

This query should return one (1) record for each Conference.

The second thing is your strSQL. It should read
strSQL = "Select * From [Conference_Coverage-Tbl] Where [Name] = '" & rst!Name & "'"

Now from what you said in your last post, [Conference_Coverage-Tbl] has the information about your Faculty attending the conference. So this is the table we need to fill strHolder.

Now the last thing is knowing what to look for. Query1 should have only the information in it that uniquely identifies each conference. So for example if
2003IERC has date in April, May and June then you will need to add the dates to Query1 to uniquely indentify each 2003IERC conference. If there is only one 2003IERC conference then all we really need in Query1 is the name or ID. The reason I mention this is that you say strHolder is filling with a lot of names. That may be happening because when we filter for the conference in our strSQL statement we only use the Name field of the conference. If the Name appears more than once in Query1, then we are not getting a unique value for our strSQL statment just using the name.
We may have to more uniquely identify the conference by add additional qualifiers to the strSQL statement. Something like this
strSQL = "Select * From [Conference_Coverage-Tbl] Where [Name] = '" & rst!Name & "' And [Location] = '" & rst!Location & "'"

or even this

strSQL = "Select * From [Conference_Coverage-Tbl] Where [Name] = '" & rst!Name & "' And [Location] = '" & rst!Location & "' And [StartDate] = #" & rst!StartDate & "# And [EndDate] = #" & rst!EndDate & "#"

Give it a go. If you have problems, you can email me directly if you want at
pbricker@attbi.com

Paul