hi all, access issue here

[dtdukes86]

Hi all im new here so please be kind! ><.. I've got a little project im working on, im not too bad at access but totally self taught and i lack support from any body better than me in my area, so ill try my question here and try my upmost to explain fully.

I've made a database and a small part of it is to collect financial data from tables and dump the data into one place ready for reporting. I use a QRY to show only 6 records which has a number of fields. see files named "original data" which is the layout of the table, albeit only a tester ATM and not the finished table.

These records are Monday - Saturdays date in any week sorted on the Field [Actual Date] . Mondays date is also in another field Called [date] this is simply the Week Commencing or WC for short. so Tuesdays date is WC+1 etc. I know thisis a long way around but other parts of the database forced my hand here, dont ask lol!

So any way, I have my nice normalized data, However my customer wants the data Flipped around so the headings at the top are the Dates of the Week I.e WC, WC+1, WC+2, WC+3 ...WC+5. see photo data in excel

So my question is how to I rotate my table, i know I can do it in Excel in about 4 clicks using copy and paste transpose, however i didn't want my end user to have to use 2 applications and start copy and pasting. Not only does it look crap, its bound to cause errors if they don't copy the right thing or click the right box , I've looked at examples of VBA code but cant seem to amend it to fit my needs, i will upload photos if i can.

I am being paid for the project as im self employed, so im happy to make a donation for somebody's time, id like this to work fully automatically but more so, id love to understand how its done not just be provided a solution. Although i do need one :-D thanks so much in advance i hope somebody can understand and help .

 

[Jon]

Welcome to the forums! We are the most active Microsoft Access community on the internet by far, with posts going back over 20 years!

To get started, I highly recommend you read the post below. It contains important information for all new users to this forum.

https://www.access-programmers.co.uk/forums/threads/new-member-read-me-first.223250/

We look forward to having you around here, learning stuff and having fun!

 

[The_Doc_Man]

I have not done this myself, but here is a possible solution from FMS, Inc.

Transposing Data in Microsoft Access Tables

Transposing data in Microsoft Access tables to normalize data

www.fmsinc.com

Look it over and see if it makes sense.

ADDENDUM: Normally, we put problems in one of the problem-topic sections rather than the intro. Seeing as you are new, you wouldn't know that and we don't whip up on you for this error. I will perhaps later move this to the Tables section but if I do, I will leave a link behind.

 

[dtdukes86]

thank you the doc man this is actually the site i was referring to , ive downloaded there sample transposing tool, which is kinda what im after but i cant seem to get the code to work with my tables , im not bad with VBA but again self taught so anything new is like reading another language i am a tinkering and have tried for a good while a few times and still cant seem to make it go.

 

[theDBguy]

Hi. Welcome to AWF!

I wonder if a Crosstab query would work. Have you tried it? Otherwise, you could also try to use Excel's Power Query.

 

[dtdukes86]

Hi. Welcome to AWF!

I wonder if a Crosstab query would work. Have you tried it? Otherwise, you could also try to use Excel's Power Query.

hiya i have used cross tabs in other parts of the database and it worked well, after a lot of tinkering i figured it out however i even had to export to excel and import automatically to get it to work in a sub form but it did work... :-D.. However when i tried in this instance the results where not what i expected as i have several fields i don't know how to show them all rather than just the sum or count one of them

 

[dtdukes86]

Hi. Welcome to AWF!

I wonder if a Crosstab query would work. Have you tried it? Otherwise, you could also try to use Excel's Power Query.

i dont mind using excell as long as the process in automatic and the end user never sees it. thats what ive dont with mycross tab previously but i cant seem to find a auto method to transpose

 

[isladogs]

I don't think you can do this from a single crosstab query.
What you will need is a series of crosstab queries which each form one part of a union query .
Does it matter to you that the output will be read only?

 

[dtdukes86]

I don't think you can do this from a single crosstab query.
What you will need is a series of crosstab queries which each form one part of a union query .
Does it matter to you that the output will be read only?

hi isladogs , not really i dont think it would matter being read only as weekly data can be inserted before this happens so in theroy this is for viewing only, the user can edit each record individually before the qry runs to create the weeks summary.

i read somewhere a union query might be the answer but i have no clue how they work. and i wasn't sure i was even looking in the right place at the time.

 

[dtdukes86]

I don't think you can do this from a single crosstab query.
What you will need is a series of crosstab queries which each form one part of a union query .
Does it matter to you that the output will be read only?

cray how excell does it in 4 clicks and access sounds like its going to be a big task lol

 

[isladogs]

Just out of interest, if you are using an older version of Access from A2000 to A2010, you might be able to do this using pivot tables.
Unfortunately, the feature was dropped in A2013

 

[dtdukes86]

Just out of interest, if you are using an older version of Access from A2000 to A2010, you might be able to do this using pivot tables.
Unfortunately, the feature was dropped in A2013

no im using 2019 i think :-(

 

[isladogs]

OK have a look at union queries here SQL UNION Operator (w3schools.com).
As stated you can use crosstab queries for each part of the union query.

It may be worth creating a procedure to work through the fields in the original table to do this

 

[pbaldy]

Welcome to AWF. FYI, I moved this out of the introductions forum.

 

[Pat Hartman]

I believe that your table isn't actually normalized. All those different columns look like a weird repeating group. The procedure that I think Colin pointed you to will actually normalize the table if you want to save it that way. Since this app is reporting only, having it be normalized is not critical but you would have more flexibility for reporting.

 

[bastanu]

Maybe have a look at my free pivot table designer that allows you to design an Excel pivot table based on an Access query and open the (new or existing) Excel file with the raw data from the query and the pivot table.

FBA Pivot Table Designer - ForestByteApps

ForestByteApps - A place for Microsoft Access code samples and utilities to make your life easier. Free Microsoft Access tools and sample VBA code

forestbyte.com

Cheers,
Vlad

 

[The_Doc_Man]

I'm going to diverge here. You might be able to get the table to pivot - but you don't really want to do that. Instead, you want to take the table in its normal form and DISPLAY it / REPORT it pivoted. That is because of the massive internal difference between Excel and Access. Given the nature of the Access beast, I am going to say that you cannot EVER actually pivot a table. But there are loopholes...

When you have Excel, each cell is independent of everything around it (until you put external references in cell formulas). Oh, sure, you can do a whole-row or whole-column format operation, but then you can go back and spot-format any cell any time anywhere in violation of the row or column format. Transposition is merely swapping letter tags and number tags in an orderly manner. It can be done mechanically.

With Access, fields (the element corresponding to an Excel column) are not fully independent of each other. Every row's structure is predictable because each record (row in Excel) has a structure that is FORCED to conform to the field data typing rules. If the 3rd field on the record (3rd cell in the row) is a date/time value, then the 3rd field of the previous and next records/rows will ALSO be a date/time value (if not null.) Access is massively oriented towards a vertical repetition of formats. Your first-post exhibit cannot be in an Access table unless EVERY FIELD is text because your layout would require vertical variation of data type.

Therefore, if it helps you to conceptualize this, consider solutions that will allow you to exhibit the data pivoted but the underlying table cannot be pivoted. If this distinction enables you to find a better solution, great. If I've confused you, I apologize - but I thought I'd try to explain why you are having fits with this action.

 

[Gasman]

You keep harping on how excel can do this in 4 clicks.
Have you thought about putting the data into excel and issuing those 4 clicks in code?

Just a thought.

 

[dtdukes86]

hi all thank you for the comments so far ,

 

[dtdukes86]

i did manage to get this to work "kinda" with one set of data over and over again it worked! However ran into another issue today regarding the data, the date used changes from week to week as we are looking at a 12 week window so between (date) and (date-84). so when we hit a new week the date data changes. not that much of a deal i thought but then the sub report is looking for a control with a date that is longer longer included in the cross tab and therefore it throws up an error.

i tried a series of cross tabs into a union qry that simply didn't work, in the end i used 13 cross tabs and 13 exports to excel and 13 imports all appending to the same table , hay ho its long but i got the data rotated however now i ran into this new problem

i like what The_Doc)man wrote above and i do agree but i have no idea how to display the data rotated if the table isn't. Any clues tip here doc ?

also Gasman i didn't mean to harp on, im happy with any method that works if you can make a recommendation id be happy to try anything at this point, ive seen the project further down the road and this issue is one im going to have to over come time and time again so i feel the doc may be right. !

as i said before im no expert and just one guy trying his best to provide what my customer needs. any and all help or suggestions is massively appreciated.