By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,460 Members | 2,215 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,460 IT Pros & Developers. It's quick & easy.

Is this SQL query possible?

P: n/a
Jon
Hi all

I'am not that good when it comes to SQL querys, just knowing the basics
and a little bit more.

Is this SQL query possible?
Summarizing all CustAmount, CustValue from the articles.
That belongs to a customer and then grouping them by week
3 tables

tblCustomer
tblLinkedCustomer
tblData

'tblCustomer
ID Customer
1 K1
2 K2
3 K77

'tblLinkedCustomer
ID Article ArticleID Customer CustomerID
1 112233 3 K1 1
2 112233 3 K2 2
3 223311 4 K77 3
4 112233 3 K2 2
5 554466 1 K2 2
6 554466 1 K77 3
'tblData
ID Article ArticleID Customer CustomerID CustAmount
CustValue InsertedWeek
1 112233 3 K1 1 12
120 0333
2 112233 3 K2 2 9
90 0333
3 223311 4 K77 3 4
32 0334
4 112233 3 K2 2 15
150 0334
5 554466 1 K2 2 15
225 0333
6 554466 1 K77 3 25
375 0334
I've filled a treeview with Customer and Article
K1
-112233
K2
-112233
-554466
K77
-223311
-554466

Now to the problem, I need to fill a listview by week
Summarizing all article that belong to that customer

When I click on the first customer, into listview
Customer CustAmount CustValue InsertedWeek
K1 12 120 0333

When I click on the second customer, into listview
Customer CustAmount CustValue InsertedWeek
K2 24 315 0333
K2 15 150 0334

and so on customer by customer..

Any pointers would mostly appreciated

Jon
jo*************@comhem.se
Nov 12 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
Suspect that you have too many tables for a start. There appears to be
nothing in tblinkedData that is not in tblData, so why not get rid of it
The normal way of doing this is to have a table of customers
CusID Customer
1 K1
2 K2
3 K3

a table of articles
ArtID Article
1 112233
2 223311
3 554466

and a join table of customers to articles
CusID ArtID CustAmount CustValue InsertedWeek
1 3 12 120 0333
2 3 9 90 0333
3 4 4 40 0334
2 3 15 150 0334
2 1 15 225 0333
3 1 25 375 0334

Although the table is not clear, when displayed as a query with the other
tables it the is very simple to sort it by Customer Ascending, InsertedWeek
Ascending.

Phil
"Jon" <jr*@telia.com> wrote in message
news:QK*******************@newsc.telia.net...
Hi all

I'am not that good when it comes to SQL querys, just knowing the basics
and a little bit more.

Is this SQL query possible?
Summarizing all CustAmount, CustValue from the articles.
That belongs to a customer and then grouping them by week
3 tables

tblCustomer
tblLinkedCustomer
tblData

'tblCustomer
ID Customer
1 K1
2 K2
3 K77

'tblLinkedCustomer
ID Article ArticleID Customer CustomerID
1 112233 3 K1 1
2 112233 3 K2 2
3 223311 4 K77 3
4 112233 3 K2 2
5 554466 1 K2 2
6 554466 1 K77 3
'tblData
ID Article ArticleID Customer CustomerID CustAmount
CustValue InsertedWeek
1 112233 3 K1 1 12
120 0333
2 112233 3 K2 2 9
90 0333
3 223311 4 K77 3 4
32 0334
4 112233 3 K2 2 15
150 0334
5 554466 1 K2 2 15
225 0333
6 554466 1 K77 3 25
375 0334
I've filled a treeview with Customer and Article
K1
-112233
K2
-112233
-554466
K77
-223311
-554466

Now to the problem, I need to fill a listview by week
Summarizing all article that belong to that customer

When I click on the first customer, into listview
Customer CustAmount CustValue InsertedWeek
K1 12 120 0333

When I click on the second customer, into listview
Customer CustAmount CustValue InsertedWeek
K2 24 315 0333
K2 15 150 0334

and so on customer by customer..

Any pointers would mostly appreciated

Jon
jo*************@comhem.se

Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.