can this be done in a query

[swarv]

Hi all,

I have the following code:

SELECT ([entitlement.entitlementfld] - [qry_countholidaysin2009]) as daysremaining
FROM entitlement
WHERE entitlement.id =  forms!holidaymainbasic!child3!id;

can this be done in a query and return a number?
qry_countholidaysin2009 returns "8".
So I would like the database to look in entitlement table and find the correct users id and take that figure and take "8" from it.

Is this possible?

Thanks

Martin

 

[MSAccessRookie]

I am a little confused as to the nature of the issue. The Query looks like it will do exactly what you want it to do unless either qry_countholidaysin2009 is a String, qry_countholidaysin2009 is a String, or both of them are. If that is the case, then use the built in cInt() Function in the calculation where it is needed, and you should get what you want.

 

[swarv]

Hi,

qry_countholidaysin2009 is a name of a query. when query is run on its own it returns the value 8.

qry_countholidaysin2009 looks like this in sql mode:

SELECT Count([holidaybookings].id) AS Countall
FROM holidaybookings
WHERE holidaybookings.id = forms!holidaymainbasic!child3!id and holidaybookings.yearfld = 2009;

now i want the new query (as posted above) to run this query, get the value it returns (in this case 8) and then do the miuns calculation and return a new number.

Can it do this?

Thanks

Martin

 

[MSAccessRookie]

Hi,

qry_countholidaysin2009 is a name of a query. when query is run on its own it returns the value 8.

qry_countholidaysin2009 looks like this in sql mode:

SELECT Count([holidaybookings].id) AS Countall
FROM holidaybookings
WHERE holidaybookings.id = forms!holidaymainbasic!child3!id and holidaybookings.yearfld = 2009;

now i want the new query (as posted above) to run this query, get the value it returns (in this case 8) and then do the miuns calculation and return a new number.

Can it do this?

Thanks

Martin

I think that this can be done by taking the original Query, and joining the Table entitlement the Query to qry_countholidaysin2009 using the id Columns. You will first need to modify qry_countholidaysin2009 to return the id Field. Something like:

SELECT holidaybookings.id, Count(holidaybookings.id) AS Countall
FROM holidaybookings
WHERE holidaybookings.id = forms!holidaymainbasic!child3!id 
    AND holidaybookings.yearfld = 2009
GROUP BY holidaybookings.id;

SELECT (entitlement.entitlementfld - qry_countholidaysin2009.Countall) as daysremaining
FROM entitlement INNER JOIN qry_countholidaysin2009 
ON entitlement.id = qry_countholidaysin2009.id
WHERE entitlement.id = forms!holidaymainbasic!child3!id;

 

[swarv]

thanks,

i'll try this tomorrow and let you know.

cheers