Looking for help in starting fresh database

[CP]

I have created a DB that a user can enter worksheets - due to the skills and
nature of the users the front loading interface has to be fast and easy to
use. Therefore my form for loading has to leave out alot of things I would
like to add.

Three Main Tables now exist
Account - name, address, tel and so on
Service Data - account (linked to accounts), make, model, serial, datein,
dateout

The loading form uses service data - doing this leaves a massive hole in my
design later on. I need to find later on the last dateout for every service
data record. If there is only one record I need to see that or where there
are 20 records (where Acc, make, model and serial are equal) I still only
need that last service record by dateout not the previous 19.

I have lots of snippets of coding to help me use the forms/queries to get
around - but no matter how I get to it I always end up with this same problem
and I believe its my initial design, any ideas?

 

[Klatuu]

Here is an example that shows the last date a client signed a contract
(clients will have multiple properties which each may have multipe
contracts). Maybe it will give you some ideas on finding the most recent
date.

SELECT Client.ClientID, Max(Contract.ContractDate) AS MaxOfContractDate
FROM (Client INNER JOIN Property ON Client.ClientID =
Property.PropertyClientID) INNER JOIN Contract ON Property.PropertyID =
Contract.ContractPropID
GROUP BY Client.ClientID
HAVING (((Max(Contract.ContractDate)) Is Not Null));

This will not be an updatable recordset, but it will find the records you
want.

 

[Jamie Collins]

HAVING (((Max(Contract.ContractDate)) Is Not Null));

How is it possible for Max(Contract.ContractDate) to be the NULL
value?! Perhaps you meant

SELECT ... MAX(IIF(Contract.ContractDate IS NULL, NOW(),
Contract.ContractDate))
FROM ...

Jamie.

--

 

[Klatuu]

No, I didn't mean that at all.
In this case, The Contract record may be created before the contract is
signed. Once it is signed, the date is entered. What was posted is a subset
of the actual filtering, so in this case, I want to be sure I don't get a hit
if there are no contracts with a date.

 

[Jamie Collins]

In this case, The Contract record may be created before the contract is
signed. Once it is signed, the date is entered. What was posted is a subset
of the actual filtering, so in this case, I want to be sure I don't get a hit
if there are no contracts with a date.

Fair enough, I just wonder why you aren't doing so in the WHERE
clause, no biggie

Jamie.

--

 

[Klatuu]

GROUP BY
--
Dave Hargis, Microsoft Access MVP

Fair enough, I just wonder why you aren't doing so in the WHERE
clause, no biggie

Jamie.

 

[Jamie Collins]

GROUP BY

What's the difference?

SELECT ContractID, MAX(ContractDate)
FROM Contract
WHERE ContractDate IS NOT NULL
GROUP BY ContractID;

SELECT ContractID, MAX(ContractDate)
FROM Contract
GROUP BY ContractID
HAVING MAX(ContractDate) IS NOT NULL

Jamie.

--

 

[Klatuu]

Each client can have multiple properties
Each property can have multiple contracts
I only want the most recent contract for each client.

 

[Jamie Collins]

Each client can have multiple properties
Each property can have multiple contracts
I only want the most recent contract for each client.

MAX(column_name) can only return the NULL value where *all*
column_name values (for the GROUP) are the NULL value. One usually use
a WHERE clause to remove such rows. Sure, you can use the HAVING
clause if you really want to...

To see my point you have to answer my question: what's the difference
between the above two queries (simplified to use just table)?

As I say, no biggie.

Jamie.

--

 

[Klatuu]

They return the exact same results. The first one executes a little faster.
What is your point? Just curious.

 

[Jamie Collins]

They return the exact same results. The first one executes a little faster.
What is your point? Just curious.

*That* was my point i.e. your original

HAVING (((Max(Contract.ContractDate)) Is Not Null))

could be done in the WHERE clause (more intuitive for me) and stand a
good chance of executing faster.

Jamie.

--

 

[Klatuu]

Well, now we know the Acces Query builder is not all that smart, after all
Thanks, Jamie