How to create duplicate records

[PeterWieland]

Hi,

Yes, you did read it correctly, I want to CREATE duplicate records!

I have a table with exam results in it with the following fields:

StudentNumber ExamCode Subject Grade Weighting
123456        AB123    Maths     A         1
123456        AB456    Science   B         2
567890        AB123    Maths     C         1
567890        AB999    Science   C         1

Some exams carry a 'double' or 'quadruple' weighting i.e if you get 'B' for Science, because it is a double award subject you are awarded 2 'B' grades, so I want to duplicate the appropriate records. I need it in this format to be able to run a crosstab to get the results with one row per student with one column per exam, and 2 or 4 columns for the weighted exams.
i.e

StudentNumber  Maths Science Science
123456         A        B      B
567890         C        C

I tried using a make table query with all data selected, then using append queries to add duplicate records for the weighted exams, running the crosstab on the new table, running the report and then deleting the table. This, however is very slow, and if the workstation crashes, (or gets switched off), before the temporary table is deleted, I am left with a large redundant table.

I thought a union query might help, but all I can get is the same records as the original table.

How can I acheive this, or, is there a better way of using the weighting field.

PS. I cannot change the way the data is in the original table, that is the way it is provided.

Thanks in advance

Pete

 

[llkhoutx]

Create select query Query_A isolating the record(s) you want to duplicate.
Create append query Query_B which appends Query_A to you underlying table.

 

[PeterWieland]

Thanks for the reply,

I have tried it like this, but I then end up with permanent duplicates. There are occasions when grades need to be edited, and I then get the problem of trying to find if there are any duplicates, and making sure that all copies get changed. I only need the duplicates to exist when I run a couple of reports, which is why I tried the temporary table route.

Pete

 

[llkhoutx]

If you don't want permanent duplicates, append to a temporary table, then use a union query with that table and your underlying table for the result you want. Delete all rows in the temporary table prior to its next use.