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]

One try (probably some problems here).

This uses your table in Post11 with the name Table4.
Given your table in Post 11, we first transform the text values into true dates and call that Query8. Then we create our second query, which uses the previous query Query8 and includes the logic for which rows to select.

Note that the intermediate query also filters out any records that are complete full. Otherwise there is a problem because a completed record becomes "old" after a few days pass and then it doesn't fullfill the criteria about being less than 2,3, or 5 days from the current date. I think you really should create test data that can check that the query is correct on different kinds of scenarios - data that is 2 days old, 3 days old, 4 days old, etc. Your current test data is all several months old so not much of a test.

Note also that it is not clear whether your dates are m/d/yyyy or d/m/yyyy and that could be a problem.

Out of old habit I use 1/1/1970 as a marker for invalid dates/null dates/zero dates etc. This prevents an error in the function call on the date fields in the original data. Afterwards we also use that in our comparison logic. Then we can convert them back to Null (or empty strings if you prefer) in the final results (which, by the way, retain true dates rather than text dates - another conversion could be introduced for that too).

First query to transform dates to true dates and filter out complete records.
Query 8

SELECT 
	ANTRAGSNUMMER AS ANTRAGSNUMMER2, 
	CDate(Replace(Nz([EINGANGDATSTROM],"1/1/1970"),".","/")) AS EINGANGDATSTROM2, 
	CDate(Replace(Nz([EINGANGESIGNIERTDOK],"1/1/1970"),".","/")) AS EINGANGESIGNIERTDOK2, 
	CDate(Replace(Nz([AUSGANGDATSTROM],"1/1/1970"),".","/")) AS AUSGANGDATSTROM2, 
	CDate(Replace(Nz([POLICEVSL],"1/1/1970"),".","/")) AS POLICEVSL2
FROM 
	Table4
WHERE 
	Nz([EINGANGDATSTROM])="" 
	OR Nz([EINGANGESIGNIERTDOK])=""
	OR Nz([AUSGANGDATSTROM])="" 
	OR Nz([POLICEVSL])=""
;

Second query to get results:

SELECT 
	q.ANTRAGSNUMMER2 AS ANTRAGSNUMMER, 
	IIF(q.EINGANGDATSTROM2 = DATEVALUE("1/1/1970"), NULL, EINGANGDATSTROM2) AS EINGANGDATSTROM, 
	IIF(q.EINGANGESIGNIERTDOK2 = DATEVALUE("1/1/1970"), NULL, EINGANGESIGNIERTDOK2) AS EINGANGESIGNIERTDOK, 
	IIF(q.AUSGANGDATSTROM2 = DATEVALUE("1/1/1970"), NULL, AUSGANGDATSTROM2) AS AUSGANGDATSTROM, 
	IIF(q.POLICEVSL2 = DATEVALUE("1/1/1970"), NULL, POLICEVSL2) AS POLICEVSL
FROM 
	Query8 q
WHERE
	(
	(Date() - q.EINGANGDATSTROM2 > 2 AND Date() - q.EINGANGESIGNIERTDOK2 > 2)
	OR
	(Date() - q.EINGANGESIGNIERTDOK2 > 3 AND Date() - q.AUSGANGDATSTROM2 > 3)
	OR
	(Date() - q.AUSGANGDATSTROM2 > 5 AND Date() - q.POLICEVSL2 > 5)
	)
;

Result:
---------------------------------------------------------------------------------------
| ANTRAGSNUMMER | EINGANGDATSTROM | EINGANGESIGNIERTDOK | AUSGANGDATSTROM | POLICEVSL |
---------------------------------------------------------------------------------------
|           222 |       01-Feb-16 |                     |                 |           |
|           333 |       01-Mar-16 | 01-Apr-16           |                 |           |
|           444 |       01-Apr-16 | 01-May-16           | 01-Jun-16       |           |
---------------------------------------------------------------------------------------

 

[ashwinghanta]

Thank you for the reply. I have achieved to do the same using

[COLOR=#242729][FONT=Arial]  WHERE (date-3) > datevalue(Eingangdata) and (esigniertdok is null)[/FONT][/COLOR]

for 3 different queries and joing them at the end using UNION and that seems to work as well. Now I am trying to retrieve records from the query which you have helped me produce in your post #3. I have created a form and inserted two text boxes as Start Date and End date and I would like to enter dates into these boxes and retrieve the records from these dates using that Query. How can I achieve this?

 

[xenou]

See:
Access 2010: Queries: How to Create a Parameter Query - Page 1
Access Tips: Parameter Queries

I strongly recommend using the query parameters window as described in the second link (at the bottom, 'asking the questions in the right order'). Although not mentioned there, the good reason for this is that you get type safety for your parameters - dates will be treated as dates, number as numbers, and so on, and bad data will be rejected (text when numbers are expected, bad date formats when dates are expected, etc.)

 

[ashwinghanta]

Hi, Thank you for the sources. Although I have created a form named overview and I have two textboxes named StartDate and EndDate in the form. I want to sort out records based on the date entered in these fields (which in turn hang on EingangDatenstrom Field dates). And I have created a button with the following code to list out the records. Where Laufzettel is the name of my CrossTab query

Private Sub findrecord_Click()
Dim SQL As Variant


SQL = "SELECT Laufzettel.ANTRAGSNUMMER,   Laufzettel.[COLOR=#333333][FONT=&quot]EINGANGDATSTROM[/FONT][/COLOR], Laufzettel.[COLOR=#333333][FONT=&quot]EINGANGESIGNIERTDOK[/FONT][/COLOR], Laufzettel.[COLOR=#333333][FONT=&quot]AUSGANGDATSTROM[/FONT][/COLOR], Laufzettel.[COLOR=#333333][FONT=&quot]POLICEVSL[/FONT][/COLOR] " & _
"FROM Laufzettel " & _
"WHERE DateValue(Laufzettel.[COLOR=#333333][FONT=&quot]EINGANGDATSTROM[/FONT][/COLOR]) BETWEEN #" & Format([Forms]![overview]![StartDate], "yyyy\/mm\/dd") & "# AND #" & Format([Forms]![overview]![EndDate], "yyyy\/mm\/dd") & "#"
End Sub

But this gives me a Run-time error '438' Object doesn't support this property or method. Can you tell me where am I going wrong?

 

[xenou]

Not sure. It works fine for me. Doesn't do anything, though - just builds a string variable then exits.

 

[ashwinghanta]

Hi,

It works now with the SQL code but with a small problem

PARAMETERS [Forms]![overview]![start] DateTime, [Forms]![overview]![end] DateTime;
SELECT Laufzettel.ANTRAGSNUMMER,   Laufzettel.[COLOR=#333333]EINGANGDATSTROM[/COLOR], Laufzettel.[COLOR=#333333]EINGANGESIGNIERTDOK[/COLOR], Laufzettel.[COLOR=#333333]AUSGANGDATSTROM[/COLOR], Laufzettel.[COLOR=#333333]POLICEVSL[/COLOR] 
"FROM Laufzettel
WHERE ((([FONT=Verdana]Laufzettel.[/FONT][COLOR=#333333][FONT=Verdana]EINGANGDATSTROM[/FONT][/COLOR][FONT=Verdana]) Between [Forms]![overview]![start] And [Forms]![overview]![end]));[/FONT]

This gives me the records between the start and end dates but It doesn't include the border dates. It just gives me dates in between. How can I also include the dates which I give in?

Also from Another query I have a problem with DateDiff

SELECT Laufzettel.ANTRAGSNUMMER,  DATEDIFF( day, [COLOR=#574123]Laufzettel.[/COLOR][COLOR=#333333]POLICEVSL[/COLOR][COLOR=#574123] [/COLOR], [COLOR=#574123]Laufzettel.[/COLOR][COLOR=#333333]EINGANGDATSTROM[/COLOR]) AS bear
FROM Laufzettel

The above code gives me an error saying Access database engine does not recognize 'day' as a valid field name or expression. Where am I going wrong?

 

[xenou]

I never use either of these constructions. Days between dates can use simple math: last date - first date. For between, I always use GTE and LTE (or GT and LT). So, full control and no confusions.

I.e, to get dates in January 2016:
Select * from MyTable where MyDate >= '2016-01-01' and MyDate < '2016-02-01'

Be very careful with dates. If the dates contain times the results are usually not what is expected. For instance, Between 2016-01-01 and 2016-01-31 will NOT return 2016-01-31 10:57 AM since that is out of range.

 

[ashwinghanta]

Hello, I have the following problem with the CrossTab Query which you helped me to create http://www.mrexcel.com/forum/microsoft-access/972443-adding-clause-crosstab-query.html. I hope you can help me again. Thanks

 

[ashwinghanta]

What if there is another field named DOK_ART in the VWDRSSTA Table and I want the values of that field when SYSTEM CODE = ZW And EREIGNIS = EIN-ES, I tried using WHERE and IIf conditions but it gives me an error is not valid for the aggregate function. How can I implement this in the above code?

TRANSFORM Max(VWDRSSTA.DATUM_ZEIT) AS MaxOfDATUM_ZEIT
SELECT VWDRSSTA.ANTRAGSNUMMER, VWDRSSTA.DOK_ART AS DOK
FROM VWDRSSTA INNER JOIN V_NAMES 
	ON (VWDRSSTA.SYSTEM = V_NAMES.SYSTEM_CODE) 
	AND (VWDRSSTA.EREIGNIS = V_NAMES.EREIGNIS)
WHERE VWDRSSTA.EREIGNIS = "EIN-ES"
GROUP BY VWDRSSTA.ANTRAGSNUMMER
ORDER BY VWDRSSTA.ANTRAGSNUMMER
PIVOT V_NAMES.MAPPED_NAME;

The above code does not work. How can I show the values of the field DOK_ART?

 

[ashwinghanta]

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 |
--------------------------------------------------------

What if there is another field named DOK_ART in the VWDRSSTA Table and I want the values of that field when SYSTEM CODE = ZW And EREIGNIS = EIN-ES, I tried using WHERE and IIf conditions but it gives me an error is not valid for the aggregate function. How can I implement this in the above code?

Code:
TRANSFORM Max(VWDRSSTA.DATUM_ZEIT) AS MaxOfDATUM_ZEIT
SELECT VWDRSSTA.ANTRAGSNUMMER, VWDRSSTA.DOK_ART AS DOK
FROM VWDRSSTA INNER JOIN V_NAMES
ON (VWDRSSTA.SYSTEM = V_NAMES.SYSTEM_CODE)
AND (VWDRSSTA.EREIGNIS = V_NAMES.EREIGNIS)
WHERE VWDRSSTA.EREIGNIS = "EIN-ES"
GROUP BY VWDRSSTA.ANTRAGSNUMMER
ORDER BY VWDRSSTA.ANTRAGSNUMMER
PIVOT V_NAMES.MAPPED_NAME;
The above code does not work. How can I show the values of the field DOK_ART?