use cursor or subquery?

[dollygg]

Table A:



Site columnB

1087242 abc
1091220 asd
1094730 qwe

Table B:

site phone phonetype phonestring
1087242 754369 Company (888) 123-0354
1087242 754370 CompanyFax (800) 123-9506
1091220 759259 Company 1-888-123-0354
1091220 759260 CompanyFax 1-800-123-9506
1094730 763115 Company (1 888) 123-0354
1094730 763116 CompanyFax (1 800) 123-9506
1098525 767232 Company 1-888-123-0354
1098525 767233 CompanyFax 1-800-123-9506


result wanted:

site columnB phonenumber faxnumber
1087242 abc (888) 123-0354 (800) 123-9506

[ranman256]

Try a CROSSTAB query:

1. build a query A with the data you want to use
2. Then use the QUERY WIZARD, and select CROSSTAB query
3. use query A from step 1, tell it how you want to view the data (it will ask)

[pbaldy]

Neither; join table b to table a twice. See if this gives you an idea, from something I'm working on where I needed to denormalize data in a locations table (note this is from SQL Server, Access syntax may differ slightly):

SELECT PU.FieldName As PickupLocation, DO.FieldName as DropoffLocation
FROM ResSegments Seg

  --SC splits the pickup and dropoff addresses into a related table, these allow denormalization
  --type 0 is pickup, type 1 is dropoff, type 2 is for stops, not used here
  LEFT JOIN ResSegmentLocations PU 
    ON Seg.SegmentRowId = PU.SegmentRowId And PU.LocationPhaseType = 0 
  LEFT JOIN ResSegmentLocations DO 
    ON Seg.SegmentRowId = DO.SegmentRowId And DO.LocationPhaseType = 1

[pbaldy]

Geez, am I a slow typist or what?