i have a query that gives me this info
DRUM CableTag01 CableTag02
Drum1 C-001 C-101
Drum2 P-544 P-454
Drum3 C-545
Drum4 C-444 C-777
but i want it diplayed like this
DRUM CABLETAG
DRUM1 C-001
DRUM1 C-101
DRUM2 P-544
DRUM2 P-454
DRUM3 C-545
DRUM4 C-444
DRUM4 C-777
The info is imported automatically from a program so i need to write a query or something to sort the data the way i need
any ideas
thanks
So, the problem is that your question doesn't accurately reflect what you need? We prefer members to consider their questions before posting to avoid such wastage of other members' time.
Regardless of how many columns you have, the approach shouldn't change fundamentally. I would suggest using a UNION ALL query, but I'm still not aware of the data very reliably. I assume that CableTag01 is a field which is reliably populated but all the other CableTag fields are optional.
Here is a basic format of the SQL, but you may need to do some work to handle your situation correctly : - SELECT [DRUM]
-
, [CableTag01] AS CABLETAG
-
FROM [tblCableTag]
-
UNION ALL
-
SELECT [DRUM]
-
, [CableTag02]
-
FROM [tblCableTag]
-
WHERE ([CableTag02] Is Not Null)
-
UNION ALL
-
SELECT [DRUM]
-
, [CableTag03]
-
FROM [tblCableTag]
-
WHERE ([CableTag03] Is Not Null)
-
UNION ALL
-
...
3 1948
It would be best to create another table (e.g. Drum2) with your desired formatting. Then use INSERT to move the data with 2 queries. - INSERT INTO Drum2(DRUM, CABLETAG) SELECT DRUM,CableTag01 FROM Drum1
- INSERT INTO Drum2(DRUM, CABLETAG) SELECT DRUM,CableTag02 FROM Drum1
thanks for your quick reply colintis
there is one problem though
cabletag01 , 02 etc can go to about 100 columns
and drum1, 2 etc can equally go for about 100 rows
NeoPa 32,556
Expert Mod 16PB
So, the problem is that your question doesn't accurately reflect what you need? We prefer members to consider their questions before posting to avoid such wastage of other members' time.
Regardless of how many columns you have, the approach shouldn't change fundamentally. I would suggest using a UNION ALL query, but I'm still not aware of the data very reliably. I assume that CableTag01 is a field which is reliably populated but all the other CableTag fields are optional.
Here is a basic format of the SQL, but you may need to do some work to handle your situation correctly : - SELECT [DRUM]
-
, [CableTag01] AS CABLETAG
-
FROM [tblCableTag]
-
UNION ALL
-
SELECT [DRUM]
-
, [CableTag02]
-
FROM [tblCableTag]
-
WHERE ([CableTag02] Is Not Null)
-
UNION ALL
-
SELECT [DRUM]
-
, [CableTag03]
-
FROM [tblCableTag]
-
WHERE ([CableTag03] Is Not Null)
-
UNION ALL
-
...
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Jennifer Smith |
last post by:
I want to be able to display my recordset as follows:
a e
b f
c g
d h
Instead of :
a b
c d
|
by: Vamsi Polavarapu |
last post by:
Hi,
I have two Data Tables,now i want a collection of only those rows which are
different in either one.Can any one suggest me a way to compare two Data Rows.
Both the Data Tables are identical...
|
by: authorking |
last post by:
How could I add data rows into a datagrid controll so that I can edit my
data in the data rows.
|
by: earsypaul |
last post by:
Hello,
With MS SQL server we can bcp hexadecimal data into varbinary columns
quite nicely. We're migrating some data to DB2 and trying to import to
a "varchar for bit data" data type column....
|
by: J055 |
last post by:
Hi
I've been looking into different ways of formatting columns/rows in the
GridView control. I realize now, I think, that once the datasource is bound
to the control the original column type...
|
by: frankkirchner |
last post by:
I'm new to both VS2005 and C# Web Forms (Though I have tons of experience
with VB.Net and VS2003).
Creating a Data Source visually in VS2005 is easy as pie - I love it, but
how do you or would...
|
by: yoav.sagi |
last post by:
hi,
i am Inheriting from TableLayoutPane with a designer that inherits
from ParentControlDesigner
..i would like to allow the user to resize the columns and rows
..All rows and columns SizeType =...
|
by: viki1967 |
last post by:
Hi all.
I need your help.
I realize this script and I do not know where to begin:
1) A simple grid rows / columns.
2) The first column contain an input type = "checkbox"
3) When select...
|
by: ronakinuk |
last post by:
how can i unhide rows/columns in excel 2007
|
by: nawedita |
last post by:
Hi everyone
how i add and delete rows,and columns(textbox,dropdown) dynamically onclick button and store data in database with insert,update,delete record.that support all browsers.Because i am...
|
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...
|
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...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
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)...
|
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...
|
by: Shællîpôpï 09 |
last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
|
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: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
| |