Complex SQL query help pls

[Jon-G]

I have a database where I have have (correctly I think) normalised out my data, however now Im trying to create some queries which pull data from four different related tables and Im really struggling.

My database is built around a Plots table (tblPlots) which has the following fields
PlotID - Autonumber
Sector
Block
Plot
Status - Related to tblLookupStatus
ClientID - Related to tblClients
PaymentOverdue - Yes/No field

I also have a Clients table (tblClients) which has the following fields
ClientID - Autonumber
Name
Surname
Telephone
Address
Email

A Payments table (tblPayments) which has:
PaymentID - Autonumber
PlotID - Related to tblPlots
PaymentAmount
PaymentDate

A Lookup table (tblLookupStatus) which holds the Status descriptions and their corresponding RGB values
StatusID - Autonumber
StatusDescription
R
G
B


What Im trying to do is create a query which shows the following information:
Sector
Block
Plot
Status (description looked up from tblStatus)
Name - Concatinated Name and Surname fields from matching row in tblClients
Telephone
Email
Last Payment Date (MAX value from tblPagos for this Plot)

WHERE
Overdue = True

Ive tried using the query builder to create the inner joins I think I need, but if I specify my Where clause I get no results (Even thought I have set one row in the Plots table to overdue. If I omit my where clause I always get three rows back (of the same data).

Can anyone with some expert SQL knowledge show me what my SQL should look like to pull this information out in this format?

TIA
Jon

 

[PNGBill]

It is good practice to use Joined Words for names in Access to help avoid Reserved Words being used.

Name is not allowed to be used.

ClientName, EmpName, Address1, EmailAdd or strEmpName, strEmailAdd etc.

Overdue should be a calculated value and shouldn't need to be a table field.

You can post what sql you have and that will assist advice to be given.

Try building the SQL / Query from basics first and add a field and check the result. This will identify which action caused the result to be

 

[Jon-G]

Hi,
Thanks for the prompt reply. As far as I am aware I havent used any reserved words, as my field names are all in Spanish! I translated them above as so you could easily see what I am trying to achieve. The actual field names can be seen in the SQL below what I have so far:

SELECT tblLotes.Sector, tblLotes.Manzana, tblLotes.Lote_No, tblLotes.En_mora, tblClientes.Nombre & " " & tblClientes.Apellido AS FullName, tblClientes.Telèfono, tblClientes.Email, tblClientes.Dirreción, tblPagos.Fecha_de_pago
FROM (tblClientes INNER JOIN tblLotes ON tblClientes.[ClienteID] = tblLotes.[ClienteID]) INNER JOIN tblPagos ON tblLotes.[LoteID] = tblPagos.[LoteID]
WHERE (((tblLotes.En_mora)=True));

 

[Jon-G]

With regards to the overdue status being set calculated programatically, it is. I wrote a function in VB to do this, so that it can be called automatically at midnight (if the DB has been left open overnight) or once when the DB first starts. I loop round all the plots in the table when I do this. This is also called for an individual plot when an payment is added/modified/removed to the payments table which pertains to that plot.

The code for the En Mora (overdue) calculation is below for your reference:

Function CalcIfEnMoraStatus(nLoteID As Integer)

Dim bEnMora As Boolean
Dim dtLastPaymentDate As Date
Dim Result

'Check there have been some payments, set to 0 if none found
Result = Nz(DCount("[Pago_USD]", "tblPagos", "[LoteID] = " & nLoteID), 0)

'If we have payments get the date of the most recent
If Result > 0 Then
dtLastPaymentDate = DMax("[Fecha_de_pago]", "tblPagos", "[LoteID] = " & nLoteID)

'We only check the En Mora status for the current month if were beyond the 5th day of the current month
If (DatePart("d", Now) > 5) Then

If (DatePart("yyyy", dtLastPaymentDate) = DatePart("yyyy", Now)) Then
If (DatePart("m", dtLastPaymentDate) = DatePart("m", Now)) Then

bEnMora = False
Else
bEnMora = True
End If
End If
End If
Else
bEnMora = True
End If

CalcIfEnMoraStatus = bEnMora
End Function

This all works correctly, and I then use the En_mora boolean field in the Plots table to show the label that represents that plot on the sector map as overdue (in red), and also show a red frame on the plot information form that shows its ovedue a payment.

This all works perfectly and I dont think that I need to change this structure, because If I build a very simply query to pull back just information that is in the plots table ONLY where En_mora is true, I get the correct results. All Im trying to add from that is rather than my simple query showing just the ClienteID that is linked to that plot, the information from the clients table (name etc) is looked up against the ID. I know I can combine fields in a qury using the 'AS' keyword as I have done it for my Combo boxes that reference the same Clients table. On top of that I just need to know how to add one field to my query which is a MAX calculation on the payments table, where the LoteID matches that of the plot in question

 

[Jon-G]

OK. So Ive figured out what I was doing wrong with the Client information lookup, and that was that I was adding the ClientID field from both tables and hiding them (I thought this would be required for the cross-refrencing of the data, but the relationship seems to take care of this for me). I have now re-factored the SQL and almost get the information I require. My SQL currently looks like this:


SELECT tblLotes.Sector, tblLotes.Manzana, tblLotes.Lote_No, tblClientes.Nombre & " " & tblClientes.Apellido AS Full_Name, tblClientes.Dirreción, tblClientes.Telèfono, tblClientes.Email
FROM tblClientes INNER JOIN tblLotes ON tblClientes.ClienteID = tblLotes.ClienteID
WHERE (((tblLotes.En_mora)=True));

Now what I also want to add is the last payment date for the plot in question to the query, so I add tblPagos (which has the LoteID field linked in the database relationships to tblLotes) and add the Max function around the Fecha_de_pago field


SELECT tblLotes.Sector, tblLotes.Manzana, tblLotes.Lote_No, tblClientes.Nombre & " " & tblClientes.Apellido AS Full_Name, tblClientes.Dirreción, tblClientes.Telèfono, tblClientes.Email, Max([Fecha_de_pago]) AS Expr1
FROM (tblClientes INNER JOIN tblLotes ON tblClientes.ClienteID = tblLotes.ClienteID) INNER JOIN tblPagos ON tblLotes.LoteID = tblPagos.LoteID
WHERE (((tblLotes.En_mora)=True));

But now I get the error 'You tried to execute a query that does not include the specified expression 'Sector' as part of an aggregate function.

If I take the max function out, I get two rows retured which are from the same Lote. I have set the En_mora bit to true for three records in tblLotes, an in tblPagos I only have two payments entered and these are both for the same plot.


Any help would be greatly appreciated!

 

[Jon-G]

Figured it out. I needed a sub-query which I didnt even know was possible.

Heres the SQL for anyone who want to achieve something like this in the future. +1 thanks to me....

SELECT tblLotes.Sector, tblLotes.Manzana, tblLotes.Lote_No, tblClientes.Nombre & " " & tblClientes.Apellido AS Full_Name, tblClientes.Dirreción, tblClientes.Telèfono, tblClientes.Email, 
(SELECT
        MAX(tblPagos.Fecha_de_pago)
      FROM
        tblPagos
      WHERE
    tblLotes.LoteID = tblPagos.LoteID) AS Ultimo_Pago
FROM (tblClientes INNER JOIN tblLotes ON tblClientes.ClienteID = tblLotes.ClienteID)
WHERE (((tblLotes.En_mora)=True));

 

[PNGBill]

Great work Talk about self help

Be careful of Max. Some of these (first, Last) can give incorrect results.
I am not skilled enough with sub queries to say your sql is bullet proof. Maybe others may comment.

Rigerous testing is advised.