Make Table query

[ashwinghanta]

Hello Everyone,

I have the following table and I want to create a table out of this

SYSTEM	EREIGNIS	DATUM_ZEIT	ANTRAGSNUMMER
WL	AUS_ED	1.1.2016	123
WL	AUS_ES	1.2.2016	123
ZW	EIN_ED	1.3.2016	123
ZW	EIN_ES	1.4.2016	123
WL	AUS_ED	1.5.2016	222
WL	AUS_ES	1.6.2016	222

<tbody>
</tbody>

I want to use the maketable query which gives me a table like this

ANTRAGSNUMMER	AusgangDatenstromWebLife	AusgangesigniertWebLife	EingangeDatenstromzworkflow	Ausgangesigniertzworkflow
123	1.1.2016	1.2.2016	1.3.2016	1.4.2016
222	1.5.2016	1.6.2016

<tbody>
</tbody>

I have tried with the following make table SQL Query

SELECT  VWDRSSTA.ANTRAGSNUMMER AS Antragsnummer, VWDRSSTA.DATUM_ZEIT AS AusgangDatenstromWebLife, VWDRSSTA.DATUM_ZEIT AS AusgangesigniertWebLife, VWDRSSTA.DATUM_ZEIT AS EingangeDatenstromzworkflow, VWDRSSTA.DATUM_ZEIT AS Ausgangesigniertzworkflow FROM VWDRSSTA WHERE (VWDRSSTA.SYSTEM ='WL' AND VWDRSSTA.EREIGNIS='AUS_ED') OR (VWDRSSTA.SYSTEM ='WL' AND VWDRSSTA.EREIGNIS='AUS_ES')  OR (VWDRSSTA.SYSTEM ='ZW' AND VWDRSSTA.EREIGNIS='EIN_ED')  OR (VWDRSSTA.SYSTEM ='ZW' AND VWDRSSTA.EREIGNIS='EIN_ES')

which gives me something like this

ANTRAGSNUMMER	AusgangDatenstromWebLife	AusgangesigniertWebLife	EingangeDatenstromzworkflow	Ausgangesigniertzworkflow
123	1.1.2016	1.1.2016	1.1.2016	1.1.2016
222	1.2.2016	1.2.2016	1.2.2016

<tbody>
</tbody>

Can someone tell me where am I going wrong in the code? Looking forward to hear from you

 

[xenou]

which gives me something like this

ANTRAGSNUMMER AusgangDatenstromWebLife AusgangesigniertWebLife EingangeDatenstromzworkflow Ausgangesigniertzworkflow
123 1.1.2016 1.1.2016 1.1.2016 1.1.2016
222 1.2.2016 1.2.2016 1.2.2016

This is not actually the results of your query so it's hard to know what you want.

In general, you will need to pivot your data somehow.



EDIT:
Nevermind, what you want is clear enough, although I have no idea why you posted a query and then gave results that are "something like" instead of just showing the actual results.

Still, probably need a pivot (crosstab) involved if you want to create new columns ... or some other procedure to transform the data into a new table structure.

 

[xenou]

Well, a simple crosstab query nearly does the job.

QUERY:

TRANSFORM Max(VWDRSSTA.DATUM_ZEIT) AS MaxOfDATUM_ZEIT
SELECT VWDRSSTA.ANTRAGSNUMMER
FROM VWDRSSTA INNER JOIN V_NAMES ON VWDRSSTA.EREIGNIS = V_NAMES.EREIGNIS
GROUP BY VWDRSSTA.ANTRAGSNUMMER
ORDER BY VWDRSSTA.ANTRAGSNUMMER
PIVOT V_NAMES.MAPPED_NAME;

This is simply the three fields needed, with Antragsnummer as the row field, Ereignis as the column field, and Datum_Zeit as the value field (max).

You must be careful - we assume there is only one field for each value. Otherwise, you will get a real max and lose data. If there is only one date, then the one and only date is also the max date and you get the right value.

In order to adjust the column names, I mapped your values AUS_ED, AUS_ES, etc. in a lookup table to facilitate giving them the names you want in the output.

I did not use the System field at all because it was redundant (all fields starting with Aus are WL and all fields starting with Ein are ZW). If there are counterexamples you may need to provide more data and the results you want.

My results for the above query are:
--------------------------------------------------------------------------------------------------------------------------------
| ANTRAGSNUMMER | AusgangDatenstromWebLife | AusgangesigniertWebLife | Ausgangesigniertzworkflow | EingangeDatenstromzworkflow |
--------------------------------------------------------------------------------------------------------------------------------
|           123 | 1.1.2016                 | 1.2.2016                | 1.4.2016                  | 1.3.2016                    |
|           222 | 1.5.2016                 | 1.6.2016                |                           |                             |
--------------------------------------------------------------------------------------------------------------------------------

My lookup table:
------------------------------------------
| EREIGNIS | MAPPED_NAME                 |
------------------------------------------
| AUS_ED   | AusgangDatenstromWebLife    |
| AUS_ES   | AusgangesigniertWebLife     |
| EIN_ED   | EingangeDatenstromzworkflow |
| EIN_ES   | Ausgangesigniertzworkflow   |
------------------------------------------

 

[ashwinghanta]

First of all, Thank you for the reply. I am new with Microsoft Access and SQL. I could not understand your code with the crosstab query. Any how I have more values in SYSTEM & EREIGNIS Fields. The total number of values in SYSTEM & EREIGNIS Fields are :

SYSTEM EREIGNIS

WL AUS_ED

WL AUS_ES

ZW EIN_ED

ZW EIN_ES

ZW AUS_ED

VL ANL_SW

VL POL_AN

and I would like to make a query which produces a table with these 7 columns with DATUM_ZEIT for every ANTRAGSNUMMER

so the new table has the following fields

ANTRAGSNUMMER [FONT=&quot]AusgangDatenstromWebLife [/FONT][FONT=&quot]AusgangesigniertWebLife [/FONT][FONT=&quot]EingangeDatenstromzworkflow[/FONT][FONT=&quot] [/FONT][FONT=&quot]Ausgangesigniertzworkflow AusgangDatenstromzworkflow((AUS_ED)

Schwebeanlage(ANL_SW) policierung(POL_AN)

[/FONT]Could to tell me how can I achieve this? and how can I create a lookup table? instead of lookup fields?

 

[xenou]

There are seven fields that are distinct:
AUS_ED
AUS_ES
EIN_ED
EIN_ES
AUS_ED
ANL_SW
POL_AN

So you don't need the ZW or VL, again unless there is more that isn't showing in your sample data that makes this otherwise. Either way, you can probably just use a lookup table to map the fields to the column headers you want in your report.

You create a lookup table like any other table. It's the same as any basic example you'd find. For example, Canadian provinces:
---------------------------------
| PostalCode | TerritoryName    |
---------------------------------
| AB         | Alberta          |
| BC         | British Columbia |
| MB         | Manitoba         |
| ON         | Ontario          |
| QC         | Quebec           |
---------------------------------

You are just using this table to use the first column to find the value in the second column. You create a lookup table the same way as any other table.

To understand the crosstab query code, please google MSACCESS Crosstab Query. You have to understand this as it is the key to what you are trying to do.

 

[ashwinghanta]

There is one field which isn't distinct AUS_ED​. It comes in with WL and ZW. How can I deal with this?

 

[xenou]

Hi,
You will add the other field to your lookup table and include it in your joins, then.

 

[xenou]

A revised cross tab query:

TRANSFORM Max(VWDRSSTA.DATUM_ZEIT) AS MaxOfDATUM_ZEIT
SELECT VWDRSSTA.ANTRAGSNUMMER
FROM VWDRSSTA INNER JOIN V_NAMES 
	ON (VWDRSSTA.SYSTEM = V_NAMES.SYSTEM_CODE) 
	AND (VWDRSSTA.EREIGNIS = V_NAMES.EREIGNIS)
GROUP BY VWDRSSTA.ANTRAGSNUMMER
ORDER BY VWDRSSTA.ANTRAGSNUMMER
PIVOT V_NAMES.MAPPED_NAME;

Using new lookup table:
--------------------------------------------------------
| SYSTEM_CODE | EREIGNIS | MAPPED_NAME                 |
--------------------------------------------------------
| WL          | AUS_ED   | AusgangDatenstromWebLife    |
| WL          | AUS_ES   | AusgangesigniertWebLife     |
| ZW          | EIN_ED   | EingangeDatenstromzworkflow |
| ZW          | EIN_ES   | Ausgangesigniertzworkflow   |
--------------------------------------------------------

 

[ashwinghanta]

I have tried out your code and it works. I have a better understanding now of Crosstab query and pivot Table. Although I have one other question. After creating this cross Tab query we have the resultset as

[FONT=&quot]--------------------------------------------------------------------------------------------------------------------------------[/FONT]
[FONT=&quot]| ANTRAGSNUMMER | AusgangDatenstromWebLife | AusgangesigniertWebLife | Ausgangesigniertzworkflow | EingangeDatenstromzwo rkflow |[/FONT]
[FONT=&quot]--------------------------------------------------------------------------------------------------------------------------------[/FONT]
[FONT=&quot]| 123 | 1.1.2016 | 1.2.2016 | 1.4.2016 | 1.3.2016 |[/FONT]
[FONT=&quot]| 222 | 1.5.2016 | 1.6.2016 | | |[/FONT]
[FONT=&quot]--------------------------------------------------------------------------------------------------------------------------------

Now I have to create another query from the above query where in I have to create a code which runs through the above query for every ANTRAGSNUMMER and copies the record set to another query if the fields are empty for 3 days from the date of EingangsDatenstrom? How can I achieve this?[/FONT]

 

[xenou]

Can you be more precise about what this means:

if the fields are empty for 3 days from the date of EingangsDatenstrom

In general, you want to turn that into a WHERE clause and you have your query.