Sum two fields from two tables

J

John

Hi,

How can I "SUM" this?
I have 4 tables:
Table 1:
[ItemsClass][Name][OpenQuantity][CloseQuantity][OpenValues]
[CloseValues]

Table 2:
[ItemsClass][Name][OpenQuantity][CloseQuantity][OpenValues]
[CloseValues]

Table 3:
[TQuantity][TValues][ItemsClass]

Table 4:
[ItemsClass][TQuantity][TValues]

I just need to create in the query that I have already
done from these tables two extra's fields with:
created field
(Sum:([Table1.CloseQuantity]-[Table2.OpenQuantity])-
([Table3.TQuantity]-[Table4.TQuantity]))

created field:
(Sum:([Table1.CloseValues]-[Table2.OpenValues])-
([Table3.TValues]-[Table4.TValues]))

Is the above possible to do? If yes, why my "sum" is not
working?
I have heard that there could be that the tables could
contains "0" values which could bug the query.
If that is the answer please let me know than how to
exclude the "0".

I will appreciate if you could solve this matter.

Regards,

John
 
M

[MVP] S.Clark

Are all of the tables linked? If you just have the four tables in the
query, then it will create a cross join, which will skew results.

For fun, post your SQL.


--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
 
J

John

Hier is the SQL:

SELECT ILI1123.Field3, ILI1123.Field9, ILI1123.Field14,
ILI.Field3, ILI.Field5, ILI.Field10, [Quantity] AS Expr1
FROM ILI INNER JOIN ILI1123 ON ILI.Field3 = ILI1123.Field3
WHERE ((([Quantity])="SUM:[ILI.Field5]-[ILI1123.Field9]"));

Thanks in advance,

John
-----Original Message-----
Are all of the tables linked? If you just have the four tables in the
query, then it will create a cross join, which will skew results.

For fun, post your SQL.


--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

Hi,

How can I "SUM" this?
I have 4 tables:
Table 1:
[ItemsClass][Name][OpenQuantity][CloseQuantity] [OpenValues]
[CloseValues]

Table 2:
[ItemsClass][Name][OpenQuantity][CloseQuantity] [OpenValues]
[CloseValues]

Table 3:
[TQuantity][TValues][ItemsClass]

Table 4:
[ItemsClass][TQuantity][TValues]

I just need to create in the query that I have already
done from these tables two extra's fields with:
created field
(Sum:([Table1.CloseQuantity]-[Table2.OpenQuantity])-
([Table3.TQuantity]-[Table4.TQuantity]))

created field:
(Sum:([Table1.CloseValues]-[Table2.OpenValues])-
([Table3.TValues]-[Table4.TValues]))

Is the above possible to do? If yes, why my "sum" is not
working?
I have heard that there could be that the tables could
contains "0" values which could bug the query.
If that is the answer please let me know than how to
exclude the "0".

I will appreciate if you could solve this matter.

Regards,

John
Click to expand...


.
Click to expand...
 
M

[MVP] S.Clark

In your OP you showed four tables that were needed for the sums, but in your
SQL example, there are only two tables.
Where are the other two tables?
Are ILI and ILI1123 tables, queries, or one of each? Which is which?
Does ItemsClass link all four Tables together?

The following where clause:
WHERE ((([Quantity])="SUM:[ILI.Field5]-[ILI1123.Field9]"));
Doesn't make sense, as the Quantityfield will probably never have a text
string. See how it's surrounded by double quotes?

What are all of the field# fields refer to? Where are the fields you listed
in the OP?


John said:
Hier is the SQL:

SELECT ILI1123.Field3, ILI1123.Field9, ILI1123.Field14,
ILI.Field3, ILI.Field5, ILI.Field10, [Quantity] AS Expr1
FROM ILI INNER JOIN ILI1123 ON ILI.Field3 = ILI1123.Field3
WHERE ((([Quantity])="SUM:[ILI.Field5]-[ILI1123.Field9]"));

Thanks in advance,

John
-----Original Message-----
Are all of the tables linked? If you just have the four tables in the
query, then it will create a cross join, which will skew results.

For fun, post your SQL.


--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

Hi,

How can I "SUM" this?
I have 4 tables:
Table 1:
[ItemsClass][Name][OpenQuantity][CloseQuantity] [OpenValues]
[CloseValues]

Table 2:
[ItemsClass][Name][OpenQuantity][CloseQuantity] [OpenValues]
[CloseValues]

Table 3:
[TQuantity][TValues][ItemsClass]

Table 4:
[ItemsClass][TQuantity][TValues]

I just need to create in the query that I have already
done from these tables two extra's fields with:
created field
(Sum:([Table1.CloseQuantity]-[Table2.OpenQuantity])-
([Table3.TQuantity]-[Table4.TQuantity]))

created field:
(Sum:([Table1.CloseValues]-[Table2.OpenValues])-
([Table3.TValues]-[Table4.TValues]))

Is the above possible to do? If yes, why my "sum" is not
working?
I have heard that there could be that the tables could
contains "0" values which could bug the query.
If that is the answer please let me know than how to
exclude the "0".

I will appreciate if you could solve this matter.

Regards,

John
Click to expand...


.
Click to expand...
Click to expand...
 
J

John

-----Original Message-----
In your OP you showed four tables that were needed for
the sums, but in your
SQL example, there are only two tables.
Click to expand...

Yes! I am making from four tables two queries. I have send
you only the first query of two tables.
Where are the other two tables?
Are ILI and ILI1123 tables, queries, or one of each?
Click to expand...

ILI and ILI1123 are tables.
Which is which?
Does ItemsClass link all four Tables together?
Yes,


The following where clause:
WHERE ((([Quantity])="SUM:[ILI.Field5]- [ILI1123.Field9]"));
Doesn't make sense, as the Quantityfield will probably never have a text
string. See how it's surrounded by double quotes?

What are all of the field# fields refer to?
Click to expand...

What fields are you refering to?

Where are the fields you listed
in the OP?
Click to expand...

Forget my first OP!


Thanks,

Hier is the SQL:

SELECT ILI1123.Field3, ILI1123.Field9, ILI1123.Field14,
ILI.Field3, ILI.Field5, ILI.Field10, [Quantity] AS Expr1
FROM ILI INNER JOIN ILI1123 ON ILI.Field3 = ILI1123.Field3
WHERE ((([Quantity])="SUM:[ILI.Field5]- [ILI1123.Field9]"));

Thanks in advance,

John
-----Original Message-----
Are all of the tables linked? If you just have the
Click to expand...
four
tables in the
query, then it will create a cross join, which will
Click to expand...
skew
results.
For fun, post your SQL.


--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

Hi,

How can I "SUM" this?
I have 4 tables:
Table 1:
[ItemsClass][Name][OpenQuantity][CloseQuantity] [OpenValues]
[CloseValues]

Table 2:
[ItemsClass][Name][OpenQuantity][CloseQuantity] [OpenValues]
[CloseValues]

Table 3:
[TQuantity][TValues][ItemsClass]

Table 4:
[ItemsClass][TQuantity][TValues]

I just need to create in the query that I have already
done from these tables two extra's fields with:
created field
(Sum:([Table1.CloseQuantity]-[Table2.OpenQuantity])-
([Table3.TQuantity]-[Table4.TQuantity]))

created field:
(Sum:([Table1.CloseValues]-[Table2.OpenValues])-
([Table3.TValues]-[Table4.TValues]))

Is the above possible to do? If yes, why my "sum" is not
working?
I have heard that there could be that the tables could
contains "0" values which could bug the query.
If that is the answer please let me know than how to
exclude the "0".

I will appreciate if you could solve this matter.

Regards,

John



.
Click to expand...
Click to expand...


.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Sum two fields from two tables 2
delete records and modufy records 2
sql query help 1
SUBQUERIES in the FROM clause with Access query 7
Join Problem 4
Query on two tables with matching null fields 4
Joining two tables 2
Pull info from one table to make many 1

Top