How compute and link table 1 and table 2

[Geraldpauig]

Hi there,
Got prob on computing this fields... i want to sum up a purchased field then less consumed field in query2, to have total available... below are my sample data:

formula on the "available" : would be if total consumed will be higher than sep1 purchased, it will tag as "0" else output total purchased - total consumed

i have query 1 which contains:
date: purchased: & available
sep1 50
sep2 100
sep3 50
sep4 200

in query 2
date: consumed:
sep5 100

[nico5038]

Can you post the queries ?

In general using your query1 and query2 slightly changed in a UNION query would be the "blunt" way to solve this.

select date, purchased, 0 from query1
UNION
select date, 0, consumed from query1
 

By using this query in a GROUPBY query you can get the purchased and consumed in one row and thus your calculation can be performed.

I wouldn't use a field [available], as that's the result of the sum of purchases minus the [consumed], thus a deductible field and according the normalization rules these aren't stored...

Nic;o)

[Geraldpauig]

hi nico
tnx for the reply... heres my query1
SELECT Efa_pur.ExpiryDate, Sum(Efa_pur.NoMeds) AS SumOfNoMeds
FROM Efa_pur
GROUP BY Efa_pur.ExpiryDate

and query2
SELECT DISTINCTROW Sum(Efa.Pills) AS [Sum Of Pills]
FROM Efa
GROUP BY Efa.ExpDate;

sir, can i send you attachment here? i'll give you the details of my formula created in excel file that i wanted to replicate in my queries in ms-access..

maybe you have another option on how to handle my query

many thanks in advance