query that sum two tables

[marnelle]

below is my example tables:

issuance_table: transaction_table:
item condition qty. item condition
qty.
item1 good 1 item1 good
1
item2 good 1 item2 good
1
item1 damage 1 item1 damage 1

my query below will summed up the quantity from transaction_table and
issuance_table which is group by item and condition. It's execute fine for me

SELECT transaction_table.item, transaction_table.condition, Sum(
[transaction_table].[qty]+[issuance_table].[]qty)
AS Total_issued
FROM transaction_table
INNER JOIN issuance_table
ON (transaction_table.condition = issuance_table.condition)
AND (transaction_table.item = issuance_table.item)
GROUP BY transaction_table.item, transaction_table.condition;

query_output:
item condition Total_issued.
item1 good 2
item2 good 2
item1 damage 2

------but when there's an item initially set from issuance_table which is not
yet in transaction table, it will not appear when i run the query above.

example i have this table

issuance_table: transaction_table:
item condition qty. item condition
qty.
item1 good 1 item1 good
1
item2 good 1 item2 good
1
item1 damage 1 item1 damage 1
item3 good 1

when i ran the query, it looks like this

query_output:
item condition Total_issued.
item1 good 2
item2 good 2
item1 damage 2

as I notice, the item3 did not appear to the output query
Please help me with this problem, any suggestion will be appreciated

i want to output the query like shown below. Can you pls give me some hint or
query code
t oget rid on this. If there's no transaction yet, i want to display the
initial quantity.

query_output:
item condition Total_issued.
item1 good 2
item2 good 2
item1 damage 2
item3 good 1



thanks a lot, im looking forward for your answer

more power

 

[NuBie via AccessMonster.com]

use RIGHT JOIN or LEFT JOIN instead of INNER JOIN. in your case since RIGHT
JOIN will do.

below is my example tables:

issuance_table: transaction_table:
item condition qty. item condition
qty.
item1 good 1 item1 good
1
item2 good 1 item2 good
1
item1 damage 1 item1 damage 1

my query below will summed up the quantity from transaction_table and
issuance_table which is group by item and condition. It's execute fine for me

SELECT transaction_table.item, transaction_table.condition, Sum(
[transaction_table].[qty]+[issuance_table].[]qty)
AS Total_issued
FROM transaction_table
INNER JOIN issuance_table
ON (transaction_table.condition = issuance_table.condition)
AND (transaction_table.item = issuance_table.item)
GROUP BY transaction_table.item, transaction_table.condition;

query_output:
item condition Total_issued.
item1 good 2
item2 good 2
item1 damage 2

------but when there's an item initially set from issuance_table which is not
yet in transaction table, it will not appear when i run the query above.

example i have this table

issuance_table: transaction_table:
item condition qty. item condition
qty.
item1 good 1 item1 good
1
item2 good 1 item2 good
1
item1 damage 1 item1 damage 1
item3 good 1

when i ran the query, it looks like this

query_output:
item condition Total_issued.
item1 good 2
item2 good 2
item1 damage 2

as I notice, the item3 did not appear to the output query
Please help me with this problem, any suggestion will be appreciated

i want to output the query like shown below. Can you pls give me some hint or
query code
t oget rid on this. If there's no transaction yet, i want to display the
initial quantity.

query_output:
item condition Total_issued.
item1 good 2
item2 good 2
item1 damage 2
item3 good 1

thanks a lot, im looking forward for your answer

more power

 

[marnelle via AccessMonster.com]

thanks for your reply Nubie, I really appreciate your quick response

I tried the RIGHT JOIN and LEFT JOIN but still have no changes.

SELECT transaction_table.item, transaction_table.condition, Sum(
[transaction_table].[qty]+[issuance_table].[]qty)
AS Total_issued
FROM transaction_table
INNER JOIN issuance_table
ON (transaction_table.condition = issuance_table.condition)
AND (transaction_table.item = issuance_table.item)
GROUP BY transaction_table.item, transaction_table.condition;

As far as i know, the query above will display only the data in which the
transaction_table.condition = issuance_table.condition and
transaction_table.item = issuance_table.item . . ...right?

Here, my problem is, when I manually input new item, condition & qty. in the
issuance_table
and this new data have no transaction yet in the transaction_table. When i
run the query code above,
the new data i manually stored earlier were not included in the query result.

That's what I wanted to do, Is there any possible query for this?.
I want this new data to be included in the display even if it has no
transaction yet.

thanks a lot

 

[NuBie via AccessMonster.com]

see if this helps:

SELECT issuance_table.item, issuance_table.condition, Sum(
[transaction_table].[qty]+[issuance_table].[]qty)
AS Total_issued
FROM transaction_table
RIGHT JOIN issuance_table
ON (transaction_table.condition = issuance_table.condition)
AND (transaction_table.item = issuance_table.item)
GROUP BY transaction_table.item, transaction_table.condition;

thanks for your reply Nubie, I really appreciate your quick response

I tried the RIGHT JOIN and LEFT JOIN but still have no changes.

SELECT transaction_table.item, transaction_table.condition, Sum(
[transaction_table].[qty]+[issuance_table].[]qty)
AS Total_issued
FROM transaction_table
INNER JOIN issuance_table
ON (transaction_table.condition = issuance_table.condition)
AND (transaction_table.item = issuance_table.item)
GROUP BY transaction_table.item, transaction_table.condition;

As far as i know, the query above will display only the data in which the
transaction_table.condition = issuance_table.condition and
transaction_table.item = issuance_table.item . . ...right?

Here, my problem is, when I manually input new item, condition & qty. in the
issuance_table
and this new data have no transaction yet in the transaction_table. When i
run the query code above,
the new data i manually stored earlier were not included in the query result.

That's what I wanted to do, Is there any possible query for this?.
I want this new data to be included in the display even if it has no
transaction yet.

thanks a lot

 

[NuBie via AccessMonster.com]

EDIT: Should be:

GROUP BY issuance_table.item, issuance_table.condition;

see if this helps:

SELECT issuance_table.item, issuance_table.condition, Sum(
[transaction_table].[qty]+[issuance_table].[]qty)
AS Total_issued
FROM transaction_table
RIGHT JOIN issuance_table
ON (transaction_table.condition = issuance_table.condition)
AND (transaction_table.item = issuance_table.item)
GROUP BY transaction_table.item, transaction_table.condition;

thanks for your reply Nubie, I really appreciate your quick response

[quoted text clipped - 24 lines]

thanks a lot

 

[marnelle via AccessMonster.com]

thanks Nubie for the help, i really appreciate it

but I took another way that can provide same result, for now, it's very
complicated, i know there's an easy way for this but due to the lack of time
and deadline, user might already need this. Temporarily, as the program
officially running, I still work in it to fix some bugs in there. Still, I'm
hoping for your quick response for further development w/ this program.

Thanks a lot and more power