Append query doesn't work for certain ids 1

[melaniecarr23]

My append query is not working all the time:
INSERT INTO plan_calculations ( plan_wks, plan_id )
SELECT DateDiff("ww",[start_date],[end_date]) AS plan_wks, plans.plan_id
FROM today, plans INNER JOIN plan_calculations ON plans.plan_id = plan_calculations.plan_id
WHERE (((plans.plan_id)=[plan_to_calculate]) AND ((today.date_id)=1));

Details: I have 3 tables I'm working from right now.
table plans (data is entered into from a form)
table plan_calculations (queries append/update information to fill this table with values)
table today (includes only 1 record for the current date, month, and record to be updated (user input changes this)

When I try to run the queries for a record already entered in the plan_calculations table, it works fine. It has one row of information to append or update depending on the query.

However, when I try to run the queries for a record existing in the plans and today tables but not yet in the plan_calculations table, 0 rows are returned.

Can someone please tell me why this is happening?

Thanks!

 

[PHV]

Where are start_date and end_date coming from ?
Do you really need plan_calculations in the SELECT ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[melaniecarr23]

The start and end date are coming from the plans table, which are entered in a form.

I thought I had to have plan_calculations listed if I wanted to base some of the calculations on fields in that table. Is that not right?

Melanie

 

[PHV]

So, what about this ?
INSERT INTO plan_calculations ( plan_wks, plan_id )
SELECT DateDiff("ww",[start_date],[end_date]) AS plan_wks, plans.plan_id
FROM today INNER JOIN plans ON today.plan_to_calculate = plans.plan_id
WHERE today.date_id = 1;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lespaul]

yes, but why are you storing calculated fields (breaks normalization rules; see: The Fundamentals of Relational Database Design

Just use your calculations in a query when you need to see the information, no need to store it (in most cases - there are exceptions).

Leslie

 

[melaniecarr23]

I took your advice on removing the plan_calculations table and it worked for adding the record, but my update queries aren't working and I can't figure it out. This time I would need the plan_calculations table to make the query work. Here is an example:
UPDATE costs, today, plans INNER JOIN plan_calculations ON plans.plan_id = plan_calculations.plan_id SET plan_calculations.pgexf = IIf([planv]<30,12,IIf([planv]<90,15,18)), plan_calculations.pfilms = IIf([npr]=True,2,1)
WHERE (((plans.plan_id)=[plan_to_calculate]) AND ((costs.cost_id)=[costid]));

It works for a record which already has the calculations done (so they can change a plan and recalculate it) but doesn't work for my new record.

Is there something in here I can't see as unneccessary?

 

[melaniecarr23]

I am storing calculations because there are a ton of them needed to generate a report I can customize for each patient. The other reason is because I have a macro running all the queries, which one is based upon the next, in sequence so we can change and recalculate a plan with no problems. Basically, there were way too many calculations to be run at one time for access to handle (over 150 for a plan) not counting basic calculations in the report and subreports.

Thanks for asking.