Sum two fields from two tables

  • Thread starter Thread starter John
  • Start date Start date
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...
 
I need to show two or three value in Cross Tab Query. But
it only allows one value. (with command Transform.... Then
pivot).


Can anybody help to advise?
 
Dear CC:

You cannot show more than one value at each "intersection" when you
use a Cross Tab. But there is a standard way of coding a query that
gives you crosstab like results that does not have this limitation.

This is done by having a subquery for each cross-product column.

If you do not know how to do this, I suggest that a complete course of
how to do it is not within the scope of newsgroup assistance.
However, if you can completely describe the source tables or queries
and give details of the data that will be encountered and the kind of
results you need, I can try to craft the query you need.

Do the number of columns vary with the data? Do the column headings
need to vary with the data? If so, how do you want the columns
labelled?

The process of learning what you want may take a few posts before I
can start.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Back
Top