473,326 Members | 2,102 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Summing

I am trying to check a list (MyList) against another List
(SupplierList).
I want sum the Qty's of UniqueID on MyList and extract the sum of the
same UniqueId's on SupplierList.
BTW There are more than one instances of Unique Id on each list.

The Script below is providing me with the correct answer for some
products (UniqueId), but incorrect amounts for others.
The incorrect answer is always a multiple of the correct answer.
What am i doing wrong???

Regards,
Ciarán

SELECT MyList.[Unique ID], SupplierList.[Unique ID], Sum
(MyList.[SHP_QTY]), Sum (SupplierList.[Qty new])

FROM MyList LEFT OUTER JOIN SupplierList ON MyList.[Unique ID]
= SupplierList.[Unique ID]

GROUP BY MyList.[Unique ID], SupplierList.[Unique ID]
Jul 20 '05 #1
1 1353
>> I am trying to check a list (MyList) against another List
(SupplierList).<<

SQL uses tables as the only data structure, not lists. Furthermore, a
table should always have a key .. which I think you are missing in
both tables.
BTW There are more than one instances of Unique Id on each list. <<


Read what you wrote :) Do you think perhaps this data element name is
BOTH vague and inaccurate? It has to be an attribute of both tables,
so I am going to guess that it is the identifier for the suppliers. I
hope that you did not use some non-relational, proprietary
auto-numbering or worse, but since you also did not post DDL, who
knows??

Also, stop putting spaces in data element names and using those weird
proprietary square brackets; All you are doing is destroying
portability, readability and making yourself look like an ACCESS
programmer:) Look up ISO-11179 rules for this stuff.

SELECT M1.supplier_id, SUM(M1.shp_qty), SUM (S1.new_qty)
FROM (SELECT DISTINCT supplier_id FROM MyList AS M1)
LEFT OUTER JOIN
(SELECT DISTINCT supplier_id FROM Suppliers AS S1)
ON M1.supplier_id = S1.supplier_id
GROUP BY M1.supplier_id;
Jul 20 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

9
by: Yaroslav Bulatov | last post by:
I made an array of 10 million floats timed how long it takes to sum the elements, here's what I got (millis): gcc -O2: 21 Python with numarray: 104 Python with Numeric: 302...
2
by: SunMan | last post by:
Hello! I am trying to create a program that will ask for a user to enter a series of letters (codes) and then print out a table that shows the codes in decending frequency. Only letters will be...
2
by: Targa | last post by:
<input NAME="TAXRATE" onBlur="this.form.TAX.value = (this.form.TAXRATE.value - 0) * (this.form.ITEM1TOTAL.value - 0) + (this.form.ITEM2TOTAL.value - 0) " Size="4"> In my TAX field I get...
7
by: Hank | last post by:
I have a report-summing problem using Access 2000. When a section runs over the end of the page, sometimes a detail gets picked up twice. Example: Customer Header XYZ Company Detail Section...
2
by: MrL8Knight | last post by:
I am building a simple shopping cart and I am having problems trying to add the costs of the items to generate a total cost. I am new at this so forgive me if my technical verbiage isn’t the...
12
by: neeraj | last post by:
Hi Can any body give me the syntax for summing the elements of the an array , without looping thanks
4
by: dancole42 | last post by:
So I have an invoicing database based on two main forms: Orders and OrderLines. Orders has fields like: OrderID BillingMethod OrderDate CreditCard CCExp OrdSubTotal ShippingCharge
8
by: highroller152 | last post by:
Not to step on anyone, but in reference to this thread on summing odds and evens, why not just use the 'continue' statement? So adding up the odds would look something like this: -on some event-...
7
by: lethek39 | last post by:
Hey I have been trying to figure out how to sum rows and columns in a matrix square. I also have been trying to get the program to list the numbers of the diagonal in the matrix. So far this is the...
3
by: NewlytoSQL | last post by:
Hi all, im fairly new to SQL and im stuck half way through a query, im using DB2 here is what im tryng to do. i have a query that brings back an item number , shelf req, sum of all orders columns,...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.