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]

OK, the extra characters are in SCMLS !
What about this ?
SELECT C.OfcName, Sum(S.SalePrice) AS allDV, Count(*) AS allRecs
,Sum(IIf(Left(S.SellName,Len(C.OfcName))=Left(S.ListName,Len(C.OfcName)),S.SalePrice,0)) AS WSODV
,Sum(IIf(Left(S.SellName,Len(C.OfcName))=Left(S.ListName,Len(C.OfcName)),1,0)) AS WSORecs
,Sum(IIf(Left(S.SellName,Len(C.OfcName))=C.OfcName,S.SalePrice,0)) AS TSODV
,Sum(IIf(Left(S.SellName,Len(C.OfcName))=C.OfcName,1,0)) AS TSORecs
,Sum(IIf(C.OfcName=Left(S.ListName,Len(C.OfcName)),S.SalePrice,0)) AS WSTDV
,Sum(IIf(C.OfcName=Left(S.ListName,Len(C.OfcName)),1,0)) AS WSTRecs
FROM CBAll AS C, SCMLS AS S
WHERE C.OfcName IN (Left(S.SellName,Len(C.OfcName)), Left(S.ListName,Len(C.OfcName)))
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]

YES! YES! YES!

That does the trick. ALL 16 offices in the CBAll.Ofcname were in the results record set.

This is the main part of my program.

In Paradox I actually had this programed but that was the long hand method. I don't understand queries as well, but I'm beginning to know that queries are where the real power lies.

I am going to check the answers of this reuslts record set against my old programs for verification.

Once that's done I'll begin to port everything over to ACCESS 2003. YES!

Thanks so much. . . Rick

 

[Rick4077]

Hello PHV . . .

I’m sorry to keep bothering you with this but the figures are wrong in the results record set from your last posting to me. I’M SURE IT’S BECAUSE I CAN’T EXPLAIN THE EVENTS WELL AT ALL.

If C.Ofcname=S.ListName AND S.SellName =  then
WSODV=Sum(S.SalePrice)*2 (NOT shown in Results Record set)
WSO=Count(*)S.ListName*2  
(IS shown in the results record set AS [We Sold Ours])

If C.Ofcname=S.ListName AND NOT S.SellName =  then
TSODV=Sum(S.SalePrice)  (NOT shown in Results Record set)
TSO=Count(*)S.ListName 
(IS shown in Results Record set AS [They Sold Ours])

If C.Ofcname=NOT S.ListName AND IS S.SellName =  then
WSTDV=Sum(S.SalePrice) (NOT shown in Results Record set)
WST=Count(*)S.ListName 
(IS shown in Results Record set AS [They Sold Ours])

Then I need to do this.
TotalTransactions=WSO+TSO+WST (IS shown in Results Record set as [Total Transactions])
ListDV = C.OfcName=S.ListName Sum(L.SalePrice) (IS shown in Results Record set as [List DV])
TotalDV = WSODV+TSODV+WSTDV (IS shown in Results Record set as [Total DV])

Of course I still need the Len thing etc.

I hope I’ve explained this well enough.

Thanks so much. Rick

 

[Rick4077]

Okay . . . I have it!

SELECT C.OfcName, Sum(IIf(Left(S.SellName,Len(C.OfcName))=Left(S.ListName,Len(C.OfcName)),S.SalePrice*2,0)) AS WSODV, Sum(IIf(Left(S.SellName,Len(C.OfcName))=Left(S.ListName,Len(C.OfcName)),1,0))*2 AS WSO, Sum(IIf(Left(S.SellName,Len(C.OfcName))=C.OfcName,S.SalePrice,0)) AS TSODV, Sum(IIf(Left(S.SellName,Len(C.OfcName))=C.OfcName,1,0)) AS TSO, Sum(IIf(C.OfcName=Left(S.ListName,Len(C.OfcName)),S.SalePrice,0)) AS WSTDV, Sum(IIf(C.OfcName=Left(S.ListName,Len(C.OfcName)),1,0)) AS WST
FROM CBAll AS C, SCMLS AS S
WHERE (((C.OfcName) In (Left([S].[SellName],Len([C].[OfcName])),Left([S].[ListName],Len([C].[OfcName])))))
GROUP BY C.OfcName;

All I need to do now is Total WSODV+TSODV+WSTDV, and
WSO+TSO+WST and place the results results in two feilds at the far right of the results Records set.

How do I place the resuts record set table on the last page of a report once the query is completed?

Thanks so much . . . Rick