how create this query???

[daniel.preda]

Basicly I have this tabe:
____________________
|empl. | w1| w2| w3|
____________________
| 111 | 15 | 20 | 30 |
____________________
| 222 | 50 | 21 | 23 |
____________________
| 333 | 36 | 85 | 63 |
____________________



And I need a Query that will give me:

__________________
|empl. |data | value |
__________________
| 111 | w1 | 15 |
__________________
| 111 | w2 | 20 |
__________________
| 111 | w3 | 30 |
__________________
| 222 | w1 | 50 |
__________________
| 222 | w2 | 21 |
.....................

I need this format to export it into a CSV. I can easily do it in excel with a pivot talbe, also in an access report... but can't export it in CSV... please... anyone any idea?

[daniel.preda]

Just this layout...

[SteveF]

Basicly I have this tabe:
____________________
|empl. | w1| w2| w3|
____________________
| 111 | 15 | 20 | 30 |
____________________
| 222 | 50 | 21 | 23 |
____________________
| 333 | 36 | 85 | 63 |
____________________

And I need a Query that will give me:

__________________
|empl. |data | value |
__________________
| 111 | w1 | 15 |
__________________
| 111 | w2 | 20 |
__________________
| 111 | w3 | 30 |
__________________
| 222 | w1 | 50 |
__________________
| 222 | w2 | 21 |
.....................

I need this format to export it into a CSV. I can easily do it in excel with a pivot talbe, also in an access report... but can't export it in CSV... please... anyone any idea?

Instead of using a query to rectify a poor table design, you really need to redesign your table so that it has the same layout (and appearance when loaded with data) as your intended query. Then forms, reports, queries, and exports will be a lot easier to handle in the future.

Access (really, any RDBMS) likes tall/narrow tables instead of short/wide ones. Any time you think you need to have field names with a number suffix (e.g. "week1", "week2"; "address1", "address2"; and so on) should be a red-flag to redesign the table so that the suffix becomes a value in a column:

Employee | Week | Hours
1|1|40
1|2|39.6
1|3|40.4
2|1|40
2|2|40
2|3|40
3|1|45
3|2|40
3|3|42

Steve

[daniel.preda]

Thanks for the advice.
However, I was expecting a solution. The data base is quite huge right now with many tables, relationships, forms and queries... it is out of the question to change anything now. I have to work with what I've got... so... any other ideas.

[SteveF]

Thanks for the advice.
However, I was expecting a solution. The data base is quite huge right now with many tables, relationships, forms and queries... it is out of the question to change anything now. I have to work with what I've got... so... any other ideas.

It's unfortunate to hear that...unfortunate because now you're starting to run into the headaches of administering such a beast! Might actually be worth the time & effort to go back to square 1 and do it right...

Given that the table design can't be changed, the only option is a Union query. This is something that, to my knowledge, cannot be built with the visual query designer; it must be done in an SQL view.

Here's what it would look like for the first two sections:

SELECT [empl.], "W1" AS [data], [W1] AS value
FROM [table]
WHERE [W1] IS NOT NULL
ORDER BY [empl.]
UNION
SELECT [empl.], "W2" AS [data], [W2] AS value
FROM [table]
WHERE [W2] IS NOT NULL
ORDER BY [empl.]
...
...
...
...
...

You would need to replace the word "table" with the actual name of the table in your database, and replicate the word UNION and the section after it for each "W" column in the table that needs to be included in the query, changing the column names and the text string that populates the "data" column within each section so that the correct columns are retrieved. Add a semicolon to the end of the final section.
This won't sort exactly the way you've requested it. You'll need to either export it as-is and sort it elsewhere, or use this query as the "table" for a second query that will sort as desired.

Steve

[ssanfu]

Thanks for the advice.
However, I was expecting a solution. The data base is quite huge right now with many tables, relationships, forms and queries... it is out of the question to change anything now. I have to work with what I've got... so... any other ideas.

As a band aide, create a temporary table, run some code to convert the data from "short and wide" to "tall and narrow".

Below is an example of the code. You would have to change some of the variables/ field names (be aware of reserved words)...

Option Compare Database
Option Explicit


Public Sub TransposeIt()
'assumes that
'    "empl' is Text
'    "TheData" is Text
'   "TheValue" is a Long

' I used "TheData" and "TheValue" as field names because
' I didn't know what your actual field names were
' and to


   Dim d As DAO.Database
   Dim s As DAO.Recordset

   Dim sSQL As String

   Dim vEmp As String
   Dim vdata As String

   Dim vValue As Long
   
   Dim i As Integer   'index for looping


   Set d = CurrentDb

   'open the source table
   sSQL = "SELECT empl, w1, w2, w3 FROM OldTable;"
   Set s = d.OpenRecordset(sSQL)

   If Not s.BOF And Not s.EOF Then
      s.MoveFirst

      Do While Not s.EOF

         For i = 1 To 3
            vdata = "W" & i
            vValue = s("W" & i)

            sSQL = "INSERT INTO NewTable ( empl, TheData, TheValue )"
            sSQL = sSQL & " Values ('" & s("Empl") & "', '" & vdata & "', " & vValue & ");"

            d.Execute sSQL, dbFailOnError
         Next
         'next record
         s.MoveNext
      Loop

   End If

   s.Close
   Set s = Nothing
   Set d = Nothing

End Sub

This seems to work in my test dB.

[daniel.preda]

This is great... I cann't wait to try it... but first things first... Christmas and New Year...

I wish to you too all the best... I will come back with feedback after the holidays...

[daniel.preda]

I tried the code... worked like a charm... thanks...