Append query based on variable data

[ejhatch]

I am trying to write an append query which is based on a table (PA0041)having muliple columns. The columns are DAR01, DAT01, DAR02, DAT02, etc etc.

The data for DARnn will contain values such as S1, S2, S3 etc. For each of these values, there will be a corresponding date in the corresponding field DATnn.

The table PA0041 looks something like this:

Employe Number, DAR01, DAT01, DAR02, DAT02, DAR03, DAT03 ...
1, S1, 21/02/2006, S2, 13/01/2006, S3, 10/01/2006
2, S1, 15/02/2006, S2, 09/03/2006, S3, 18/02/2006
3, S1, 06/01/2006, S2, 16/02/2006, S3, 01/03/2006
4, S2, 03/03/2006, S3, 26/01/2006

There may be up to 10 columns for each - i.e. DAR01 up to DAR10.

I can append them into 1 append table if I write 10 queries. What I would like to know whether it is possible to do this using 1 query. I need to write both the date type and the date into the table.

In addition I need to know whether any employee is missing any date types. If I have a table called tblDateTypes which holds the date types that I would expect the employee to have as a minimum, how can I check for any employees who do not have a required date type.

If the data contained in tblDateTypes is as follows:

DateType
S1
S2
S3

In the above example I would expect the end result to flag the fact that employee 4 does not have date type S1.

Let us assume in the above example that the employees all exist in the table PA0041 - even if they don't have all the required date types.

Let me know if you need more information.

Thanks,

Evan

 

[neileg]

You have a design problem. Instead of up to 10 columns in your table you need up to ten rows in a linked table. This is known as normalisation (normalization). There's plenty of information in these forums on the subject. Once you have normalised your data you will find that the idea of writing 10 queries to return your data just never arises.

 

[ejhatch]

Hi Neil,

Thanks for that.

Evan

 

[Moxy1]

Hi Evan,

I agree, your database design is incorrect, but I do understand what you are trying to do so here is your answer:

Don't use a query, use VBA instead. What you need to do is move each set of fields for each record into a two dimensional array. You can then use the array to populate your table.

Cheers,
Pete