Greetings,
This seems a very complicated task, so I welcome any
input. My boss wants a data grid or matrix of the top 6
orders with ordered items, and products, for a particular
customer and he wants to see it like this:
products down the left side
orders across the top
number of items under the respective order number column
across from the corresponding product.
I hope that makes sense.
I have three tables. Products, Orders, Orderitems. I
don't even know where to start. Many thanks in advance
for any input offered. 4 3494
DebbieD wrote: Greetings,
This seems a very complicated task, so I welcome any input. My boss wants a data grid or matrix of the top 6 orders with ordered items, and products, for a particular customer and he wants to see it like this: products down the left side orders across the top number of items under the respective order number column across from the corresponding product. I hope that makes sense. I have three tables. Products, Orders, Orderitems. I don't even know where to start. Many thanks in advance for any input offered.
So you need a crosstab (also known as a pivot table). The first step is to
let us know what database you are using (version, as well). Also, if you
could give us some details about your tables, column names, datatypes,
relationships, it would get us that much further along in the process. Note,
if your tables have dozens of columns that aren't related to this question,
then we don't need to hear about them, just tell us about the relevant
columns and their datatypes.
Providing some sample data for us to work with will also help. Just a few
rows for each table - no need to use attachments.
Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
Many thanks Bob. I am using a SQL2000 database.
I have three tables and have listed fields I need to
display next to the table name:
PRODUCTS - catalogid (int),name (text)
orders - orderid (int), lastname (text), firstname (text)
orderitems - numitems (int), unitprice (money)
orderitems is linked to orders with orderid
products is linked to orderitems with catalogid
I hope this helps. In the meantime, I'll look up things
on pivot tables.
Thanks again. -----Original Message----- DebbieD wrote: Greetings,
This seems a very complicated task, so I welcome any input. My boss wants a data grid or matrix of the top
6 orders with ordered items, and products, for a
particular customer and he wants to see it like this: products down the left side orders across the top number of items under the respective order number
column across from the corresponding product. I hope that makes sense. I have three tables. Products, Orders, Orderitems. I don't even know where to start. Many thanks in advance for any input offered. So you need a crosstab (also known as a pivot table).
The first step is tolet us know what database you are using (version, as
well). Also, if youcould give us some details about your tables, column
names, datatypes,relationships, it would get us that much further along
in the process. Note,if your tables have dozens of columns that aren't
related to this question,then we don't need to hear about them, just tell us
about the relevantcolumns and their datatypes.
Providing some sample data for us to work with will also
help. Just a fewrows for each table - no need to use attachments.
Bob Barrows
-- Microsoft MVP - ASP/ASP.NET Please reply to the newsgroup. This email account is my
spam trap so Idon't check it very often. If you must reply off-line,
then remove the"NO SPAM"
.
Hi Bob,
I did exactly what I need in Access but it won't work in
my Active Server Page because my back end is SQL. How
can I translate this into a SQL query? Thanks!
Hello,
I've created this crosstab query in Access and works
great but my back end is SQL2000. How can this be
rewritten to pull from a SQL2000 database in an ASP?
Thank you very much in advance.
TRANSFORM Sum(qyMatrixTest.numitems) AS SumOfnumitems
SELECT qyMatrixTest.cname
FROM qyMatrixTest
WHERE (((qyMatrixTest.ocustomerid)=271))
GROUP BY qyMatrixTest.cname, qyMatrixTest.ocustomerid
PIVOT qyMatrixTest.orderid; -----Original Message----- DebbieD wrote: Greetings,
This seems a very complicated task, so I welcome any input. My boss wants a data grid or matrix of the top
6 orders with ordered items, and products, for a
particular customer and he wants to see it like this: products down the left side orders across the top number of items under the respective order number
column across from the corresponding product. I hope that makes sense. I have three tables. Products, Orders, Orderitems. I don't even know where to start. Many thanks in advance for any input offered. So you need a crosstab (also known as a pivot table).
The first step is tolet us know what database you are using (version, as
well). Also, if youcould give us some details about your tables, column
names, datatypes,relationships, it would get us that much further along
in the process. Note,if your tables have dozens of columns that aren't
related to this question,then we don't need to hear about them, just tell us
about the relevantcolumns and their datatypes.
Providing some sample data for us to work with will also
help. Just a fewrows for each table - no need to use attachments.
Bob Barrows
-- Microsoft MVP - ASP/ASP.NET Please reply to the newsgroup. This email account is my
spam trap so Idon't check it very often. If you must reply off-line,
then remove the"NO SPAM"
.
DebbieD wrote: Hi Bob,
I did exactly what I need in Access but it won't work in my Active Server Page because my back end is SQL. How can I translate this into a SQL query? Thanks!
Hello,
I've created this crosstab query in Access and works great but my back end is SQL2000. How can this be rewritten to pull from a SQL2000 database in an ASP? Thank you very much in advance.
TRANSFORM Sum(qyMatrixTest.numitems) AS SumOfnumitems SELECT qyMatrixTest.cname FROM qyMatrixTest WHERE (((qyMatrixTest.ocustomerid)=271)) GROUP BY qyMatrixTest.cname, qyMatrixTest.ocustomerid PIVOT qyMatrixTest.orderid;
Yes, it's easy in Access, but not so easy in SQL Server. Have you done a
Google search for crosstab and T-SQL? If so, you will find a lot of
solutions. http://www.google.com/search?sourcei...2DSQL+crosstab
The easiest solution may be to use an Access database with your SQL tables
linked in to run this Transform query. However, it may not perform very well
so YMMV.
If your budget can afford it, there's a tool called RAC
( http://www.rac4sql.com/) that makes crosstabs in SQL Server easy.
HTH,
Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM" This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Maria |
last post by:
One of my duties is to record student grades each report period(6). The
tables in the database basically look like:
TblStudent
StudentID
Name
etc
TblStudentGrade
StudentGradeID
GradePeriod
|
by: Jim Corey |
last post by:
Hello,
I've been using the datagrid, but now I need to sort
on the columns, and I really want to do this on the client
to save performance time.
I had a third-party grid I used in ASP that...
|
by: Nevets Steprock |
last post by:
I'm writing a web program where one of the sections is supposed to output a
correlation matrix. The typical correlation matrix looks like this:
..23
..34 .54
..76 .44 .28
..02 .77 ...
|
by: Rob |
last post by:
I have a form where the user may see a matrix of data in a grid (populated
via a dataset). If the user wishes to add a new row of data, they click a
button and a new form opens where the user may...
|
by: Ben R. |
last post by:
Hi guys,
I've got a DB table of timecards with these fields in the table:
ID (Int)
UserID (Int)
DateWorked (DateTime)
HoursWorkedOnThatDate (Double)
I'd like to display a grid, with...
| |
by: Bob |
last post by:
Hi Everyone:
Does anyone know where I can find a free grid control that is used to
display information and is not tied to any databases? I basically read a
file, and want to display a matrix. ...
|
by: okcomputer24 |
last post by:
I am wanting to print a matrix grid which is derived from a list of lists. The grid should look as follows:
Col 0 Col 1 Col 2
Row 0 1 2 3
Row 1 4 ...
|
by: 15153681 |
last post by:
Can a matrix be used as a grid in python... if not, can anyone let me know the procedure for getting a grid in python. Reason for asking is that i must create a sandpile simulation. thanx
|
by: dev101 |
last post by:
hi fellow coders, what is the best data structure to store a grid / matrix, with 2 Keys (row, col), and the Values (cell entries) can be any object? Similar to a 2-dimensional array, but i am trying...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence...
| |