Complex Query & Variables

[Rick4077]

Okay -
I think I have this now. Please bear with me. I’m very new with Access. I do have this programmed in Paradox but I want to migrate over to Access 2003 which I have on my computer.

Unique Office Table Names – Usually 5 to 14 different office names in each unique table ie.,
CBAll.tbl [OfcName].

SCMLS.tbl contains 4,000 to 50,000 records, some matching the office names in CBAll.tbl [OfcName]
This is the secondary table that the unique tables will query against

I need to run these three queries, all at once preferably, and STORE these values to be placed in a small table on the last page of a report.

WSODV, WSORecs
TSODV, TSORecs
WSTDV, WSTRecs

MATCH ListName AND SellName

SELECT DISTINCTROW CBAll.OfcName, Sum(SCMLS.SalePrice) AS WSODV, Count(*) AS WSORecs
FROM CBAll INNER JOIN SCMLS ON (CBAll.OfcName = SCMLS.ListName) AND (CBAll.OfcName = SCMLS.SellName);

MATCH SellName NOT ListName

\SELECT DISTINCTROW CBAll.OfcName, 
Sum(SCMLS.SalePrice) AS TSODV, 
Count(*) AS TSORecs
FROM CBAll INNER JOIN SCMLS ON CBAll.OfcName = SCMLS.SellName;

MATCH ListName NOT SellName

SELECT DISTINCTROW CBAll.OfcName, 
Sum(SCMLS.SalePrice) AS WSTDV,
Count(*) AS WSTRecs
FROM CBAll INNER JOIN SCMLS ON CBAll.OfcName = SCMLS.ListName;

I will need to add them together as shown below for each CBAll.tbl [OfcName]

WSODV + TSODV + WSTDV
WSORecs + TSORecs + WSTRecs

My plan is to place control buttons (is this correct) on a form to run the queries, view and print the reports.
I will need some programming to ask the user to enter a unique table name. Something like a dropdown list I suppose. I don’t know. What about paths, etc?

Thanks Much. Rick

 

[PHV]

I need to run these three queries, all at once
Something like this ?
SELECT C.OfcName, Sum(S.SalePrice) AS allDV, Count(*) AS allRecs
,Sum(IIf(S.SellName=S.ListName,S.SalePrice,0)) AS WSODV
,Sum(IIf(S.SellName=S.ListName,1,0)) AS WSORecs
,Sum(IIf(S.SellName=C.OfcName,S.SalePrice,0)) AS TSODV
,Sum(IIf(S.SellName=C.OfcName,1,0)) AS TSORecs
,Sum(IIf(C.OfcName=S.ListName,S.SalePrice,0)) AS WSTDV
,Sum(IIf(C.OfcName=S.ListName,1,0)) AS WSTRecs
FROM CBAll AS C, SCMLS AS S
WHERE C.OfcName IN (S.SellName, S.ListName)
GROUP BY C.OfcName

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Rick4077]

PHV . . .my goodness . . . I just pasted this into an SQL and ran it. Walla it worked perfectly.

I need to rethink things over now as it looks like there won't be a necessity for a lot of programming.

How can the user select an individual tables one at a time and run this query? The tables would replace CBAll?

Thanks Much . . . Rick

 

[Rick4077]

One more thing . . .

Regarding the office names in the Unique tables like CBAll, is there a way to place a wildcard to include everything after a certain point in the [OfcName] field?

ie.,

COLDWELL BANKER(Wildcard) to include

COLDWELL BANKER THOMAS
COLDWELL BANKER GEORGE
COLDWELL BANKER LIPTON etc . . .

In other words I wish to run the calculations for all of the COLDWELL BANKER offices.

Thanks much. Your pure genious!

Rick

 

[PHV]

run the calculations for all of the COLDWELL BANKER offices
...
WHERE C.OfcName IN (S.SellName, S.ListName) AND C.OfcName LIKE 'COLDWELL BANKER*'
...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Rick4077]

PHV . . . with this I have to have the user typin in the office name each time for the Like statement.

Is there a way to set a wildcard for each office name in the C.tbl as the program is running?

Some tables will have as many as 20 or more office names.

I tried *, then [*] but neither worked.

Thanks much . . . Rick

 

[PHV]

I wish to run the calculations for all of the COLDWELL BANKER offices
I thought I answered this question.
Sorry I don't understand your last post.

 

[Rick4077]

Hi Again PHV . . . I understand that I have a sorry ability to exactly describe my difficulties. I apologize.

In the C.tbl (I didn’t know you could rename like that) there are various office names. Sometimes 5, sometimes 20.

ie.,

Coldwell Banker NRT CP

(There are 17 Coldwell Banker NRT offices that end with different two digit designations. I need to calculate on Coldwell Banker NRT* to include all of the Coldwell Banker NRT offices as one office, or entity if you will.

Prudential Calif Pick SD
Same here. I need to make the calculations on all of the Prudential Calif Pick* offices as one entity.

So I need to include a wildcard in the query for the C.table office names.

I tried *, and [*] in the query but this failed.

I sure hope I explained this correctly.

Thanks so much. Rick

 

[PHV]

Like this ?
SELECT IIf(C.OfcName Like '*? ?*', Left(C.OfcName,InStrRev(RTrim(C.OfcName),' ')-1), C.OfcName) AS Office
,Sum(S.SalePrice) AS allDV, Count(*) AS allRecs
,Sum(IIf(S.SellName=S.ListName,S.SalePrice,0)) AS WSODV
,Sum(IIf(S.SellName=S.ListName,1,0)) AS WSORecs
,Sum(IIf(S.SellName=C.OfcName,S.SalePrice,0)) AS TSODV
,Sum(IIf(S.SellName=C.OfcName,1,0)) AS TSORecs
,Sum(IIf(C.OfcName=S.ListName,S.SalePrice,0)) AS WSTDV
,Sum(IIf(C.OfcName=S.ListName,1,0)) AS WSTRecs
FROM CBAll AS C, SCMLS AS S
WHERE C.OfcName IN (S.SellName, S.ListName)
GROUP BY IIf(C.OfcName Like '*? ?*', Left(C.OfcName,InStrRev(RTrim(C.OfcName),' ')-1), C.OfcName)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[PHV]

end with different two digit designations
Simpler way:
SELECT IIf(C.OfcName Like '* ??', Left(C.OfcName,Len(C.OfcName)-3), C.OfcName) AS Office
,Sum(S.SalePrice) AS allDV, Count(*) AS allRecs
,Sum(IIf(S.SellName=S.ListName,S.SalePrice,0)) AS WSODV
,Sum(IIf(S.SellName=S.ListName,1,0)) AS WSORecs
,Sum(IIf(S.SellName=C.OfcName,S.SalePrice,0)) AS TSODV
,Sum(IIf(S.SellName=C.OfcName,1,0)) AS TSORecs
,Sum(IIf(C.OfcName=S.ListName,S.SalePrice,0)) AS WSTDV
,Sum(IIf(C.OfcName=S.ListName,1,0)) AS WSTRecs
FROM CBAll AS C, SCMLS AS S
WHERE C.OfcName IN (S.SellName, S.ListName)
GROUP BY IIf(C.OfcName Like '* ??', Left(C.OfcName,Len(C.OfcName)-3), C.OfcName)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Rick4077]

PHV . . . I tried the "Simpler Way" as you described. The query works very well and is very fast.

Only problem is that it's only returning four out of 16 offices in the C.Table.

Any ideas on why this is?

Thanks much! Rick

 

[lespaul]

do the offices that are not showing up have records in SCMLS?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[Rick4077]

Yes.

COLDWELL BANKER NRT has approximately over 100.

If I manually filter S.ListName as COLDWELL BANKER NRT there are no records in the filter.

If I manually filter S.ListName as COLDWELL BANKER NRT* all of the COLDWELL BANKER NRT records show in the filter including the aformentioned two digit designators after the NRT part.

In Paradox ObjectPal language I use this to accomplish it;
s = CBall."OfcName" ;gets office name from field OfcName
s=s+".." (This, ".." is Paradoxs' "include everything after" OfcName.

I think it should be a simple task but I haven't been able to figure out how to ad the * in the query for C.Table and S.Table.

Thank much LesPaul . . . (where is that guitar?)

Rick

 

[PHV]

I still don't understand your REAL issue :-(
Are you saying that SCMLS.SellName or SCMLS.ListName may not be equal to CBAll.OfcName ?
In which table are the 2 extra characters ? SCMLS, CBALL, both ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Rick4077]

I just realized something - I think!

Shouldn't the wildcard(*) be on the S.[fields]?

This way it will select the records corectly???

Rick

 

[Rick4077]

Another question . . .

,Sum(IIf(S.SellName=S.ListName,S.SalePrice,0))
,Sum(IIf(S.SellName=S.ListName,1,0))

What does the 0 and 1,0 represent in the above code?

Boolean True/False???

If so, which is which?

Thanks . . . Rick

 

[lespaul]

it means
if s.Sellname = s.listname, then add saleprice to the total if they don't match add 0 to the total
If s.sellname = s.listname then add 1 to the total if they don't match add 0 to the total

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[Rick4077]

Okay . . .
"Are you saying that SCMLS.SellName or SCMLS.ListName may not be equal to CBAll.OfcName ?
In which table are the 2 extra characters ? SCMLS, CBALL, both?"

CBAll.Ofcname
All office names in this table should be in both SCMLS.ListName and SCMLS.SellName in various ways.

CBAll.OfcName is queried against SCMLS.Listname AND SCMLS.SellName as thus . . .

CBAll.OfcName = SCMLS.ListName AND SCMLS.SellName
(These values need to be multiplied as *2)

CBAll.OfcName=SCMLS.ListName NOT=SCMLS.SellName 'Calculations
CBAll.OfcName=SCMLS.SellName NOT=SCMLS.ListName 'Calculations

Whew . . . I get so confused about this.

Thanks . . . Rick

 

[PHV]

So, the 2 extra characters are in BOTH tables ?
only returning four out of 16 offices
Could you please post some values of missing CBAll.Ofcname and corresponding SCMLS.Listname or SCMLS.SellName ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Rick4077]

Look at CBAll.Ofcname
One of the office names is PRUDENTIAL CALIF PICK

If you look at PRUDENTIAL CALIF PICK in the Scmls.ListName field you will see that there are 6 PRUDENTIAL CALIF PICK with different trailing designations.

If you look at PRUDENTIAL CALIF PICK in the Scmls.SellName field you will see that there are 3 PRUDENTIAL CALIF PICK with different trailing designations.

I need to calculate ALL PRUDENTIAL CALIF PICK offices as one office - regardless of the trailing designations.
So . . . PRUDENTIAL CALIF PICK* (I think?)

I’m not sure if the wildcard should be appended to the office name in the CBAll.Ofcname or the SCMLS.ListName and the SCMLS.SellName while running the query?


Scmls.ListName Scmls.SellName
PRUDENTIAL CALIF PICK PRUDENTIAL CALIF PICK
(CBAll.OfcName = SCMLS.ListName AND SCMLS.SellName
(Calculations need to be multiplied as *2) They are in both columns for the same record. SCMLS.SalePrice is there also.

Scmls.ListName Scmls.SellName
PRUDENTIAL CALIF PICK COLDWELL BANKER NRT CDM
CBAll.OfcName=SCMLS.ListName NOT=SCMLS.SellName 'Calculations

Scmls.ListName Scmls.SellName
RE/MAX PREMIER REALTY PRUDENTIAL CALIF PICK
CBAll.OfcName=NOT SCMLS.ListName and =SCMLS.SellName 'Calculations

Remember – thousands of the office names are SIDE by SIDE in the Scmls.tbl

The query looks at the Scmls.ListName column and the Scmls.SellName column and makes calculations as shown – ONLY for offices listed in the CBAll.OfcName field.

I gotta call a doctor. Ha!

CBAll.OfcName
CATALIST HOMES
C-21 BEACHSIDE
C-21 SUPERSTARS
C-21 PROFESSIONALS
COLDWELL BANKER NRT
ERA
EVERGREEN REALTY
FIRST TEAM
KELLER WILLIAMS
PRUDENTIAL CALIF PICK
PRUDENTIAL CALIF REALTY
RE/MAX REAL EST SVCS
RE/MAX PREMIER REALTY IR
SEVEN GABLES
STAR REAL ESTATE
TARBELL REALTORS

SCMLS.Listname
COLDWELL BANKER NRT CAPO BCH
COLDWELL BANKER NRT CAPO BCH
COLDWELL BANKER NRT CAPO BCH
COLDWELL BANKER NRT FUL
COLDWELL BANKER NRT FUL
COLDWELL BANKER NRT FUL
COLDWELL BANKER NRT FUL
COLDWELL BANKER NRT FUL
COLDWELL BANKER NRT IR
COLDWELL BANKER NRT IR
COLDWELL BANKER NRT LB LN
COLDWELL BANKER NRT LB LN
COLDWELL BANKER NRT LB NO
COLDWELL BANKER NRT LB NO
PRUDENTIAL CALIF PICK DP MB
PRUDENTIAL CALIF PICK DP MB
PRUDENTIAL CALIF PICK DP RITZ COVE
PRUDENTIAL CALIF PICK IR
PRUDENTIAL CALIF PICK IR
PRUDENTIAL CALIF PICK IR
PRUDENTIAL CALIF REALTY
TARBELL REALTORS BREA
TARBELL REALTORS BREA
TARBELL REALTORS FV
TARBELL REALTORS FV
ERA DREAM TEAM REALTY
ERA DREAM TEAM REALTY
ERA FINE HOMES
ERA FINE HOMES
FIRST TEAM HB BRKHRST
FIRST TEAM HB BRKHRST
FIRST TEAM HB GLDN WEST
FIRST TEAM HB GLDN WEST
FIRST TEAM HB GLDN WEST

SCMLS.SellName
HANOLD PROPERTIES
HANU REDDY REALTY
HANU REDDY REALTY
HELP-U-SELL ANAHEIM HILLS RLTY
LAGUNA WOODS PROPERTIES
LAGUNA WOODS PROPERTIES
LAGUNA WOODS PROPERTIES
LANDMARK REAL ESTATE
LANDMARK REALTORS
LANDMARK REALTORS
COLDWELL BANKER NRT NB CDM
COLDWELL BANKER NRT NB CDM
COLDWELL BANKER NRT NB CST NPT
COLDWELL BANKER NRT NB CST NPT
PRUDENTIAL CALIF REALTY YL
PRUDENTIAL CALIF REALTY YL
PRUDENTIAL CALIF REALTY YL
QUANTUM REALTY
QUANTUM REALTY
QUEST REAL ESTATE SERVICES
R D FINANCIAL
R K PROPERTIES, INC.
PRUDENTIAL CALIF PICK DP MB
PRUDENTIAL CALIF PICK DP RITZ COVE
PRUDENTIAL CALIF PICK IR
RE/MAX PREMIER REALTY IR
RE/MAX PREMIER REALTY IR
RE/MAX PREMIER REALTY IR
RE/MAX REAL EST SVCS AV
RE/MAX REAL EST SVCS DP MB
RE/MAX REAL EST SVCS DP MB
RE/MAX REAL EST SVCS HB
RE/MAX REAL EST SVCS HB
RE/MAX REAL EST SVCS LB
RE/MAX REAL EST SVCS MB