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

 

[ashwinghanta]

What I mean to say is that if I have the following query :

ANTRAGSNUMMER	EINGANGDATSTROM	EINGANGESIGNIERTDOK	AUSGANGDATSTROM	POLICEVSL
111	1.1.2016	1.1.2016	1.1.2016	1.1.2016
222	2.1.2016
333	3.1.2016	4.1.2016
444	4.1.2016	5.1.2016	6.1.2016

<tbody>
</tbody>

where the first recordset with ANTRAGSNUMMER 111 is considered as the perfect case. Moving onto the second recordset for the second Antragsnummer 222 there is only 1 date 2.1.2016 and the remaining fields are empty. Now what I want is to wait for 2 days from 2.1.2016 and if the next field is still empty then move this recordset to a new table/query. The same for the 3rd recordset is to wait for 3 days from 4.1.2016 and if the next field is still empty then move the recordset to the new table/query. For the final case I want the same to wait for 5 days from 6.1.2016 and if the next field is empty them move the recordset to the same new table/query. Is there a possibility to code in Access VBA like Excel VBA on these lines?

If Date - sh.Cells(2, 2) > 2 Then
if sh.cells(2,3) = vbNullString then
copy Range

Looking forward for your reply

 

[xenou]

You don't really move recordsets to tables or queries. You would write another query. Either from the original source data for this query, or perhaps by putting this query into a table and using it as a new source.

What's determining the logic for waiting two days, vs. wait three days or wait four days or wait five days?

Excel vba will not be of any help to us, I'm afraid.

 

[ashwinghanta]

The logic behind waiting for 2 ,3, 4 or 5 days is that the query is updated every day as the table is updated every day through an ODBC connection. So even if the field is empty today it can be updated tomorrow or day after and incase if the field is not updated for 3 ,4 or 5 days then I want that recordset to be pointed out so that the required changes can be made for these particular recordsets.

 

[xenou]

My question was more along the lines of why wait two days for one record and three for another? Why not two for both? or three for both? What's the rule?

 

[ashwinghanta]

Ah! The rule depends on the department handling the Antragsnummer. If the EINGANGESIGNIERTDOK field is empty then we have to wait for 2 days and if its still empty to pass it to the required department. If the AUSGANGDATSTROM field is empty then we wait for 3 days and is still empty we pass it to another department and so on. The waiting time basically depends on different departments handling the fields.

 

[xenou]

Okay, next question. When you say wait three days, wait three days from when? How do you know when three days has passed?

Also, by the way, do not use the words "and so on". Be specific about all of the rules!

 

[ashwinghanta]

the waiting time of 3 days or 2 days or 5 days is always from the date specified in that particular record. For example in the query that I made couple of posts ago In the recordset of ANTRAGSNUMMER 222 we have to wait for 2 days from the date of 2.1.2016 which means we wait till 4.1.2016 and since we are now at 5.10.2016 and the next field is still empty it must be moved to a new table/query. In the recordset where ANTRAGSNUMMER is 333 we have to wait 3 days from the date which is specified in the EINGANGESIGNIERTDOK field 4.1.2016 which means till 7.1.2016 and since the date has passed and the next field is empty it must be moved to the new table/query. In the same way for the final ANTRAGSNUMMER of 444 we wait for 5 days from the date of 6.1.2016 which means till 11.1.2016 and since the date has passed and the next field is empty it must be moved to the new table/query.

For your second question. How do you know when three days has passed?
I hope the earlier explanation has provided some info regarding this question. Also suppose the Date is today 05.10.2016 then we wait for 2 ,3 or 5 days depending on which field it is present which means till 7.10.2016 or 8.10.2016 or 10.10.2016 and if the corresponding next field is still empty then we move it to the new Table/Query.

 

[xenou]

The fields in your last query are not in your original query (or in the lookup table I created for your first query). See posts 9 and 11 - the column headers have changed.

Please post up to date information. I'd probably avoid creating a query based on another crosstab query. Work from your original data. However, it is possible to push the crosstab query into a table using a MakeTable query, then query that table. However, you'd have to recreate that table every time you want the report because such a table will be static.

 

[ashwinghanta]

The original field names in the crosstab query are

Antragsnummer

Eingangdatenstrom

Esigniertdokument

Ausgangdatenstrom

PolicierungVSL

<tbody>
</tbody>

and I need to wait for 2 days from the date present in Eingangdatenstrom when the Esigniertdokument field is empty to move the record into a new table/Query. I need to wait for 3 days from the date present in Esigniertdokument when the Ausgangdatenstrom field is empty to move the record into a new table/Query. I need to wait for 5 days from the date present in Ausgangdatenstrom when the PolicierungVSL field is empty to move the record into a new table/Query. I hope I did not miss anything.

 

[xenou]

The original field names in the crosstab query are

Antragsnummer Eingangdatenstrom Esigniertdokument Ausgangdatenstrom PolicierungVSL

Hi, none of these fields are in the original query except antragsnummer. See any of posts 1-9. Not sure what data you are working with anymore. The fields in the original query are {Aus_Ed, Aus_Es, Ein_Ed, Ein_Es} and these fields were mapped to {AusgangDatenstromWebLife AusgangesigniertWebLife, EingangeDatenstromzworkflow, Ausgangesigniertzworkflow}