Do you ever have one of those days when you get SQL block ?
Well, I do...
I built a cool Sales Funnel application for my manager, all works great.

We have a new Potential Sale form:
dennisvg.homeip.net/FIS-KPN-NL/medewerkers/salesfunnel_invoer.asp

These records are then displayed on the overview page:
dennisvg.homeip.net/FIS-KPN-NL/medewerkers/salesfunnelOverzicht.asp

All great you would say... But look at the overview... I only entered 2 potential sale records and look at the overview ?!
The total values are correct, but that's a simple add-up recordset like so:

SELECT Sum(Salesfunnel.[Capaciteit FC/SP]) AS [SomVanCapaciteit FC/SP], Sum(Salesfunnel.[Capaciteit EOS]) AS [SomVanCapaciteit EOS], Sum(Salesfunnel.[Capaciteit KPN ESN]) AS [SomVanCapaciteit KPN ESN], Sum(Salesfunnel.[Capaciteit Atos]) AS [SomVanCapaciteit Atos], Sum(Salesfunnel.WaardeFIS) AS SomVanWaardeFIS, Sum(Salesfunnel.Saleswaarde) AS SomVanSaleswaarde
FROM Salesfunnel

Looks more complicated than it is, trust me.

The SQL that gives me headaches is this:

SELECT Salesfunnel.SalesFunnelId, Salesfunnel.AccountManager, orgcodes.OrgcodeNaam, Salesfunnel.Kriteria, Salesfunnel.Opdrachtgever, divisies.divisieNaam, Klanten.klantNaam, Salesfunnel.ProjectNaam, Salesfunnel.Omschrijving, Salesfunnel.[Reg-Datum], Salesfunnel.[Capaciteit FC/SP], Salesfunnel.[Capaciteit EOS], Salesfunnel.[Capaciteit KPN ESN], Salesfunnel.[Capaciteit Atos], Salesfunnel.WaardeFIS, Fase.Fase, Salesfunnel.Kans, Salesfunnel.PO, Salesfunnel.Saleswaarde, Salesfunnel.LaatsteMutatie
FROM orgcodes INNER JOIN ((Fase INNER JOIN (divisies INNER JOIN Salesfunnel ON divisies.divisieID = Salesfunnel.Divisie) ON Fase.faseID = Salesfunnel.Fase) INNER JOIN Klanten ON divisies.divisieID = Klanten.Divisie) ON orgcodes.OrgcodeID = Klanten.OrgCode
ORDER BY Klanten.klantNaam ASC

I do not get why it returns 7 records, when there's only 2. I tried DISTINCT selecting, leaving out fields but always end up with 7 returns ?!

Could it have something to do with the customers belonging to certain divisions, as shown here:

dennisvg.homeip.net/FIS-KPN-NL/medewerkers/klantbaseRapportDiv.asp

But still it should only display the one I selected, not all that are related to the division i chose, right ?

Does anyone see what i'm missing ?

Oh before I forget, relationships are here...
dennisvg.homeip.net/FIS-KPN-NL/SF_relations.gif

My thank yous in advance for any help.

Regards,

Dennis
Webslave employed by KPN Telecom :-P

this sucks, this query in Access 2000 returns what i need, both records, not 7 not 9 not 1 but 2 records:

SELECT Salesfunnel.SalesFunnelId, Salesfunnel.AccountManager, orgcodes.Orgcode, Salesfunnel.Kriteria, Salesfunnel.Opdrachtgever, divisies.divisieNaam, Klanten.klantNaam, Salesfunnel.ProjectNaam, Salesfunnel.[Reg-Datum], Salesfunnel.[Capaciteit FC/SP], Salesfunnel.[Capaciteit EOS], Salesfunnel.[Capaciteit KPN ESN], Salesfunnel.[Capaciteit Atos], Salesfunnel.WaardeFIS, Fase.Fase, Salesfunnel.Saleswaarde
FROM Fase INNER JOIN (divisies INNER JOIN ((Salesfunnel INNER JOIN Klanten ON Salesfunnel.Klant = Klanten.klantID) INNER JOIN orgcodes ON Salesfunnel.[Organisatie Code] = orgcodes.OrgcodeID) ON divisies.divisieID = Salesfunnel.Divisie) ON Fase.faseID = Salesfunnel.Fase
ORDER BY Klanten.klantNaam

But when UDev accesses my Access 97 Dbase it returns 1 record and the sum query still sees both ?!

old report with much related records:
dennisvg.homeip.net/FIS-KPN-NL/medewerkers/salesfunnelOverzicht.asp

new report with 1 record shown:
dennisvg.homeip.net/FIS-KPN-NL/medewerkers/salesfunnelOverzicht1.asp

What am I doing wrong here ?

With kind regards,

Dennis van Galen
Webmaster KPN Services
Financial and Information Services