Append query syntax requested

[Jack Sheet]

Hi all

I am a real newbie to Access (I don't even know how to insert a calculated
date into a record during an action query) and John Spencer gave me a lot of
help in the gettingstarted newsgroup on a similar problem. Perhaps this
question should be addressed there but I feel it is getting a bit more
complicated, and it is query-specific so I put it here. Apologies if in
error. (John, if you read this you will recognise the previous problem.)

I have a database with 2 tables: T_Clients and T_Tasks.

T_Clients has 3 fields:
ID_Clients = autonumber, primary key
YEM = number, integer, validation permits range >0 and <13 (representing a
month)
YED = number, integer, validation permits range >0 and <32 (representing a
day in a month)
(the user is currently trusted not to enter impossible values within those
ranges, ie 31 June or 30 February)

T_Tasks has 3 fields:
ID_Tasks = autonumber, primary key
ID_Clients = number, long integer
PeriodTo = date

There is a one (T_Clients.ID_Clients) to many (T_Tasks.ID_Clients)
relationship with enforced referencial integrity.

In my sample data, T_Clients has 3 records

ID_Clients YEM YED
20 4 30
21 5 31
22 6 30

T_Tasks has 3 records
ID_Tasks ID_Clients PeriodTo
11 20 2004-04-30
12 21 2005-05-31
13 22 2006-06-30

I am needing an Append query, that creates records in T_Tasks. I don't care
how many intermediate Select Queries I need to create to get to the final
destination.

User inputs a date (call it "RollDate") and for this example say the user
input date 2006-06-30 (I am flexible about where this needs to be stored, or
whether it is input on a prompt when the append query is run).

The query then adds records to T_Tasks, by considering each instance of
ID_Clients in T_Clients and, for each such entry it adds a new period in
T_Tasks if an appropriate period does not already exist in T_Tasks for that
ID_Clients value. An appropriate period is one that ends on the latest date
on or before the RollDate but bearing the month and day-of-the-month
particular to that value of ID_Clients as specified in T_Clients.YEM and
T_Clients.YED.

So, for the sample data given:

It should ignore ID_Clients = 22, because the year end for that client is
set to 30 June, the latest such date on or before rolldate (30 June 2006)
is, coincidentally, 30 June 2006 and there already exists a record with that
date in the PeriodTo field.

It should create one record for ID_Clients = 21, and populate the PeriodTo
field with 2006-05-31.

IDEALLY it should create two records for ID_Clients = 20, one for period
ended 30 April 2005 and one for period ended 30 April 2006. However, that
sounds very complicated, and I would be content (for now) with one that just
creates one field to 30 April 2006 (unless the more general solution is more
trivial than I expect).

Any help or pointers would be gratefully received.

 

[Jack Sheet]

Further to my earlier message I have made an attempt at this, which doesn't
yet work. At the moment I an concentrating on creating two Select queries.

One (the simpler problem) is to create a query that identifies all records
that require no updating.
I call this query Q_RecExists, and the query states

SELECT T_Tasks.ID_Tasks, T_Tasks.ID_Clients, T_Tasks.PeriodTo
FROM T_Clients INNER JOIN T_Tasks ON T_Clients.ID_Clients =
T_Tasks.ID_Clients
WHERE (((T_Tasks.PeriodTo)>#6/30/2005#));

For the time being I have hard-coded 30 June 2005, being one year prior to
the Rolldate, but that will eventually have to be replaced by a calculated
amount based on user input. Anyway, for the time being this query at least
works, on the existing data and for a proposed RollDate 30 June 2006.

The second query is intended to identify The records that require to be
appended, and this is where I run into problems. I call this query
Q_RecNotExists, and currently the query is saved as:

SELECT Q_RecExists.ID_Clients
FROM T_Clients AS Q_RecExists LEFT JOIN
[SELECT ID_Clients FROM T_Tasks WHERE [T_Tasks].[PeriodTo] =
#2005-06-30#].
AS Q_RecNotExists ON Q_RecExists.ID_Clients=Q_RecNotExists.ID_Clients
WHERE Q_RecNotExists.ID_Clients is NULL;

This does not work, and nor would I expect it to. It compiles, and runs,
but lists all records in T_Tasks.
The reason why I would not EXPECT it to work is that where I currently have
"= #2005-06-30#"
I thought that it should read "> #2005-06-30#"

But if I try to change the "=" to ">" then it will not save the query, and
tells me that there is a syntax error in the FROM clause.
So now I am stuck!

 

[Jack Sheet]

<Snip>

Now that I've proposed a solution, I feel I can point out a flaw in
your design: a period is modelled in SQL using start and end date pairs
and you are missing the PeriodFrom column.

Hi Jamie
I posted a thank you message a few days ago, but it did not propogate my
newsgroup reader, so I hope you got it.

As regards your comment about the absence of a PeriodFrom column, whilst I
have in fact inserted one I wonder whether its omission would really be a
flaw?

I am repeatedly advised not to hard-code data into fields where the data can
be calculated by reference to data already within the database. That is
what queries are for. The "PeriodFrom" date will be one day later than the
latest previous PeriodTo date for the same Client/Task. I can query for
that, so I don't need to hard code it. Or am I missing something?

 

[Jack Sheet]

Thank you very much, once again Jamie
I actually understood that!