Transform table by using query is it possible

[emma pierreux]

Hello
I am from Belgium . I am trying to adjust a table to work with it in access. The table called grootboekrekeni ngopbrengsten and results from an excel file I imported in access (see the attached file) unfortunately I cannot use it until I make some rearrangements. The fields of the tabele are
Periode
Datum
Dagboek
Docnr
Bedrijf / Omschrijving
Debet
Credit
Saldo

The last fields (aanwezig , select) of the query alternatief_tra ns_stap1 after running produce the following values

aanwezig select
1 70010000-projecten
0
0
0
0
1 70020000 -sport
0
0
1 70050000-drankjes
1 70042100 boter
0
0
1 70012364 eten

I would like the query to produce the following
aanwezig Rubriek select
1 70010000-projecten 70010000-projecten
0 70010000-projecten
0 70010000-projecten
0 70010000-projecten
0 70010000-projecten
1 70020000 -sport 70020000 -sport
0 70020000 -sport
0 70020000 -sport
1 70050000-drankjes 70050000-drankjes
1 70042100 boter 70042100 boter
0 70042100 boter
0 70042100 boter
1 70012364 eten 70012364 eten

So everytime there is a 1 in field aanwezig the field rubriek should contain the value of field select and keeps on copiing that value until the next value in the field aanwezig contains another 1 .

Can anyone help me ?

Thanks in advance
Emma Pierreux

[NeoPa]

This is done more easily in Excel. Would you prefer to do it there or must it be done in Access?

[emma pierreux]

In Access it's possible to edit an enormous amount of records at a time. If this is also the case in Excel, I am curious about your Excel solution (both solutions are welcome). Kind regards.

[NeoPa]

Let's start with an Excel suggestion then :-)

I don't know exactly what is in which columns, so I'll assume for now that we start with an X column of 'aanwezig', a Y column of 'Select' and an extra column required before that called 'Rubriek'.

The first step then, would be to select column Y (at the top to select the whole column) and insert a whole new column. This new column (which will be called 'Rubriek') becomes our new column Y and the old column Y ('Select') becomes column Z.

Now, we enter a formula in the cell which is the top data cell of column Y (Probably Y1 but may be Y2 or even below that if any rows at the top are used for titles). For illustration I'll assume we are entering the formula into Y2. The formula will say, basically, get the value from Z2 if it exists, otherwise get Y1 ==> Get matching Z if exists or previous Y otherwise :

=IF(Z2>"",Z2,Y1)

Our next step is to copy this formula from this single cell into all the cells in column Y that have data in the matching rows. EG. If there are 9,999 records and the top record is in row 2 then the last cell we need to fill is Y10000. The first part is to copy Y2 using Ctrl-C. Next get to the end of the column. To do this use Ctrl-End to get to the bottom-right of the whole worksheet then move back left until you reach column Y. From here use Ctrl-Shift-UpArrow to select all those cells in column Y that reflect rows with data in, then use Ctrl-V to paste the modified formula into each cell.

At this point you will see what you want, but all these cells are showing the results of the formulas. It is probably safer to ensure that these are replaced by their values. To do this simply Ctrl-C again to paste all the cells then using the key sequence Alt-E; S; V; Enter (Edit menu | Paste-Special | Values) you replace each formula with the current values.

This should leave you with what you need in your spreadsheet :-)