nasty query

[Corticus]

Hi all,

I've got a nasty query to do.

In short, I need to make this:

Book1
	A	B	C	D
1	Name	ServiceID	ServiceName	Rate
2	Alachua	1	HDM	5.5
3	Alachua	2	CM	5.1
4	Alachua	3	EAR	4.8
5	Citrus	1	HDM	6.7
6	Citrus	2	EAR	3.9
7	Columbia	1	HDM	2.4
8	Columbia	2	EAR	7.6
9	Columbia	3	CM	3.4
10	Columbia	4	PECA	4.5
Sheet1

become this:

Book1
	F	G	H	I	J	K	L	M	N
1	Name	Serv1	Rate1	Serv2	Rate2	Serv3	Rate3	Serv4	Rate4
2	Alachua	HDM	5.5	CM	5.1	EAR	4.8
3	Citrus	HDM	6.7	EAR	3.9
4	Columbia	HDM	2.4	EAR	7.6	CM	3.4	PECA	4.5
Sheet1

Which I have sort of figured out how to do. I can make the query work as long as I know the max number of services, with SQL like:
SELECT QryRates.Name, QryRates.County, QryRates.ServiceName AS Serv1, QryRates.BillableRate AS Rate1, QryRates_1.ServiceName AS Serv2, QryRates_1.BillableRate AS Rate2, QryRates_2.ServiceName AS Serv3, QryRates_2.BillableRate AS Rate3
FROM (QryRates INNER JOIN QryRates AS QryRates_1 ON QryRates.Name = QryRates_1.Name) INNER JOIN QryRates AS QryRates_2 ON QryRates_1.Name = QryRates_2.Name
WHERE (((QryRates.ServiceID)=1) AND ((QryRates_1.ServiceID)=2) AND ((QryRates_2.ServiceID)=3));

But if there is no service 3, then the query return no records, since the "3" criteria for serv3 is not met,since there are no service 3.

Oh, what to do.

Thanks!

I apologize for the hideous SQL, but I wanted to show that I had done something to try to do this.

 

[Russell Hauf]

Hi Corticus,

That is a tough one. I think you're going to have to use code.

If this is a one-time thing, then I would look for the maximum number in the ServiceID field, and then make your table (create a new table) with that many ServX and RateX fields. Then loop through the recordset of the original table - put the "Name" field into a temporary variable, then check it to the current record to see if you should still be filling in field values for the same "Name" as the last record. Does that make sense? So you'll be looping through 2 recordsets at once.

So it might look somthing like (aw heck, I wrote the code):

Sub Transpose2()
    ' Russell Hauf, June 11, 2003 for MrExcel Message Boards
    
    Dim strLastName As String
    
    Dim rstNew As New ADODB.Recordset
    Dim rstOld As New ADODB.Recordset
    
    
    rstNew.Open "tbl061103b", CurrentProject.Connection, adOpenDynamic, adLockOptimistic
    rstOld.Open "Select * from tbl061103 order by Name", CurrentProject.Connection, adOpenForwardOnly, adLockOptimistic

    Do While Not rstOld.EOF
        If strLastName <> rstOld.Fields("Name") Then
            rstNew.AddNew
            rstNew.Fields("Name") = rstOld.Fields("Name")
            strLastName = rstOld.Fields("Name")
        End If
        rstNew.Fields("Serv" & rstOld.Fields("ServiceID")) = rstOld.Fields("ServiceName")
        rstNew.Fields("Rate" & rstOld.Fields("ServiceID")) = rstOld.Fields("Rate")
        rstNew.Update
        rstOld.MoveNext
    Loop

End Sub

Make sure you order by the name when you open the recordset, because even if it looks like the table is sorted by name, the recordset based on the table may not be. My "old" table is named tbl061103, and the new one is tbl061103b.

HTH,

Russell

p.s. If you're going to be doing this on a regular basis, you could create the table dynamically in code - just use DMAX to find the highest ServiceID, and make that many Serv and Rate fields in the new table, then run the code above.

 

[Corticus]

That's nice Russel, thanks.

This is probably going to be long...sorry...

I was hoping I wouldn't have to get into it to much just because its boring, but I guess details are necessary. The query has to get run every time a certain database is opened. The database is serving as storage for the data for a mail merge in Word. Word will only break your sections up by records of one table from an mdb, so I have to transpose the fields like you see to accomadate a table in the Word doc.
The real trick is that this has to be used by people after I leave, so it can't be real complicated. I have the data entry for the mdb going into normalized tables, and then this query needs to pull that data into the transposed table above. That way, I have all the associated data for one "Name" in one record. I tried to use crosstabs first (I don't suppose you tried that did you? ), and then ended up with the query above.
I think I can get the code you gave me to work, the trick is the max services as you indicated, I need to pull that value into the code, and I can't how to do that right off the top of my head, but I might get it, or not.
One nice thing about this is that I'm building it from scratch, so I can set it up however I want; there's no exisiting data I have to work into it.

Sheesh.

Thanks for reading this far!

-Corticus

 

[Philem]

Hullo. I'm like as not WAY off base with my thoughts here, but, would a Cross-Tab Query work for you? Set the Name, ServiceName and Rate as Rows, and ServiceID as Columns?

Shot in the dark, but, HTH

P

 

[Corticus]

Thanks Philem,

Tried the crosstab thing, problem is, you need multiple column headers, and there not supposed to sum or perform any group calculations.

I came up with something that should work.

What I did was, create a Cartesian query between the Services table and the Entities table, thus creating a list of every possible entity and service combination, then I link a bunch of instances of the Rate table (this is where the rate for a service is kept, joined to the Service table on service ID), each joined on entity, with the first instance of the Rate table having the service criteria set to one, the next instance has the service criteria set to two, and so on. You just have to make sure there is an serviceID associated with each instance of the rate table, even is the services name is blank.

Here's my elegant SQL,
SELECT TblEntities.EntityID, TblEntities.County, QryRates.ServiceName AS Serv1, QryRates.BillableRate AS Rate1, QryRates.Units AS Units1, QryRates_1.ServiceName AS Serv2, QryRates_1.BillableRate AS Rate2, QryRates_1.Units AS Units2, QryRates_2.ServiceName AS Serv3, QryRates_2.BillableRate AS Rate3, QryRates_2.Units AS Units3, QryRates_3.ServiceName AS Serv4, QryRates_3.BillableRate AS Rate4, QryRates_3.Units AS Units4, QryRates_4.ServiceName AS Serv5, QryRates_4.BillableRate AS Rate5, QryRates_4.Units AS Units5
FROM QryRates AS QryRates_4 RIGHT JOIN (QryRates AS QryRates_3 RIGHT JOIN (((TblEntities LEFT JOIN QryRates ON TblEntities.EntityID = QryRates.EntityID) LEFT JOIN QryRates AS QryRates_1 ON TblEntities.EntityID = QryRates_1.EntityID) LEFT JOIN QryRates AS QryRates_2 ON TblEntities.EntityID = QryRates_2.EntityID) ON QryRates_3.EntityID = TblEntities.EntityID) ON QryRates_4.EntityID = TblEntities.EntityID
WHERE (((QryRates.ServiceID)=1) AND ((QryRates_1.ServiceID)=2) AND ((QryRates_2.ServiceID)=3) AND ((QryRates_3.ServiceID)=4) AND ((QryRates_4.ServiceID)=5));

Nice eh?

Thanks for the help, I'd still love to hear a suggestion for something that will work better, but I can't imagine anyone would want to read all this!

-Corticus

 

[Philem]

Glad to hear you got something working. Another option would be to do a Pivot Table in Excel. That's normally what I do when things start to get too weird for Access.

P

 

[Corticus]

Heehee,

That's funny you should mention that. This project was in Excel, but we had a host of Excel related problems as well. But as far as organizing the data and presenting in the manner I wanted, Excel did great, it just doesn't offer me the relational tools Access does, which I need.

Thanks for the help,
-Corticus