UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V  1 2 >  (Go to first unread post)
   Reply to this topicStart new topic
> Transpose / Transforming Data Format, Access 2016    
 
   
BarryStoker
post Aug 21 2019, 09:16 AM
Post#1



Posts: 7
Joined: 6-February 08



Hi everyone,

I'm hoping someone can advise on a data re-formatting solution. I've spent a few hours this morning trying transform queries and pivots but have had no luck thus far in getting the data into the format that we need.

We have an old 3rd party system that provides some basic excel reports that are used for general admin/housekeeping tasks.

The report that we need some help re-formatting provides a list of users and their access levels for a particular cost centre.

Each cost centre has 5 levels of user access and each user access level has a maximum of 5 officers that can be placed within it.

So a single cost centre may have 2 officers at access level 1, 4 officers at access level 2, 3 officers at access level 4.

The next cost centre may have no officers at level 1, 2 at level 2, none at level 3 and 4 but 2 officers at level 5 and so on.

I've attached a cut down version of the data to provide an example of how the data looks when we get it. There's an example in the attached database too

Attached File  CurrentDataFormat.jpg ( 82.57K )Number of downloads: 28


Ideally, we need only 1 row of data for each cost centre with each UserID and Username going alongside it with a column heading that relates to their access level. Something Like

Attached File  Reformatted_example.jpg ( 60.81K )Number of downloads: 34


So 1 row per cost centre, with a potential maximum of 5 officers x 5 user levels for each cost centre

I appreciate this looks bizarre however I am assured that this is what is needed after meeting and discussing this with colleagues. I had one of them mock up a spread sheet just to make sure!

Regards,

Barry



Attached File(s)
Attached File  qry_help.zip ( 20.12K )Number of downloads: 5
 
Go to the top of the page
 
theDBguy
post Aug 21 2019, 10:00 AM
Post#2


Access Wiki and Forums Moderator
Posts: 76,380
Joined: 19-June 07
From: SunnySandyEggo


Hi Barry. Welcome to UA! welcome2UA.gif

This should be possible but would take a bit of code. Are you trying to create an Access report or an Excel worksheet?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
projecttoday
post Aug 21 2019, 11:36 AM
Post#3


UtterAccess VIP
Posts: 11,056
Joined: 10-February 04
From: South Charleston, WV


Looks like you need to insert a column specification for A, B, C, etc.

--------------------
Robert Crouser
Go to the top of the page
 
BarryStoker
post Aug 21 2019, 12:03 PM
Post#4



Posts: 7
Joined: 6-February 08



Thanks for the welcome DBGuy laugh.gif

I've spent the afternoon breaking the data into chunks, pivoting then using the costcentre code to try and sew it back together. After several false dawns, I am still working under the shadow of failure.

Ideally the data would end up back in an excel spread sheet.

I appreciate any ideas or advice you can offer DBGuy.

Cheers,

Barry
Go to the top of the page
 
theDBguy
post Aug 21 2019, 12:09 PM
Post#5


Access Wiki and Forums Moderator
Posts: 76,380
Joined: 19-June 07
From: SunnySandyEggo


Hi Barry. For your case it might easier to create a template in Excel first and then simply use Excel Automation to populate the data from your table(s).

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
dale.fye
post Aug 21 2019, 12:19 PM
Post#6



Posts: 161
Joined: 28-March 18
From: Virginia


Not sure I understand what the "_A", "_B", ... identifiers mean in your 2nd images column headers.

if you have a table with fields:[Cost Center], Level, UserID, UserName

then you can create a query like:

SELECT T1.[CostCenter], "Level" & T1.[Level] & "_UserID" as Source, T1.UserID as SourceValue, Count(T2.UserID) as Sequence
FROM yourTable as T1 INNER JOIN yourTable as T2 ON T1.CostCenter = T2.CostCenter and T1.Level = T2.Level AND T1.UserID >= T2.UserID
WHERE USERID IS NOT NULL
GROUP BY T1.[CostCenter], T1.Level, T1.UserID
UNION ALL
SELECT T1.[CostCenter], "Level" & T1.[Level] & "_UserName" as Source, T1.UserName as SourceValue, Count(T2.UserID) as Sequence
FROM yourTable as T1 INNER JOIN yourTable as T2 ON T1.CostCenter = T2.CostCenter and T1.Level = T2.Level AND T1.UserID >= T2.UserID
WHERE USERID IS NOT NULL
GROUP BY T1.[CostCenter], T1.Level, T1.UserID

This should give you something like
CODE
Cost Center     Source          SourceValue         Sequence
AC101           Level_1_UserID          12345            1
AC101           Level_1_UserID          23456            2
AC101           Level_2_UserID          34567            1
AC101           Level_3_UserID          45678            1
AC101           Level_1_UserName     OfficerA            1
AC101           Level_1_UserName     OfficerB            2
AC101           Level_2_UserName     OfficerC            1
AC101           Level_3_UserName     OfficerD            1


You could then use that query as a subquery for a CrossTab query using CostCenter and Sequence as row headers, Source as the Column header, and First(SourceValue) as the Value
This post has been edited by dale.fye: Aug 21 2019, 12:22 PM

--------------------
Dale Fye
Microsoft Access MVP 2013-2016
Developing Solutions, LLC
Go to the top of the page
 
projecttoday
post Aug 21 2019, 01:09 PM
Post#7


UtterAccess VIP
Posts: 11,056
Joined: 10-February 04
From: South Charleston, WV


If your spreadsheet is a pivot table then probably the only changes you need are to change the pivot specifications and to add a column to your Access query for the letter designation.

--------------------
Robert Crouser
Go to the top of the page
 
BarryStoker
post Aug 22 2019, 03:38 AM
Post#8



Posts: 7
Joined: 6-February 08



Hi again,

Thanks everyone for your ideas, I will try your suggestions and see how I get on today.

The data comes out in a straight spread sheet dump like this (I've tidied it a little and trimmed a few hundred rows of data but not changed the layout)

Attached File  useraccess_Spreadsheet.jpg ( 94.09K )Number of downloads: 15


> Hi Dale. The _A _B _C _D _E requirement for is because there can be up to 5 different officers assigned to each of the 5 different access levels across a cost centre and they need the UserID and UserName to be in 2 separate columns under a user access level. I like the look of your suggestion and example which I'm going to explore now. Thanks very much.

Cheers,

Barry


Go to the top of the page
 
BarryStoker
post Aug 27 2019, 04:39 AM
Post#9



Posts: 7
Joined: 6-February 08



Just had a long weekend here in the UK so back to work today thumbdn.gif

I have tried the excellent recommendations people have kindly suggested, but alas I Just cannot get the thing to work as required.

I've certainly read and learned a fair bit these last couple of days. I am left with a feeling of frustration as I keep obsessing how the data coming out of this system could surely have been done better. Alas those folk who developed and implemented the system are long gone and it's easier to blame them than to dwell on my shortcomings in unpicking this thing.

I thought I could crack it with Dale's suggestion, alas I could not get it to function.

Thanks again everyone for taking the time and offering a hand.

Regards,

Barry
Go to the top of the page
 
projecttoday
post Aug 27 2019, 10:02 AM
Post#10


UtterAccess VIP
Posts: 11,056
Joined: 10-February 04
From: South Charleston, WV


So you have some system already in place, not Access, and you're trying to do some reporting in Access? Please describe.

--------------------
Robert Crouser
Go to the top of the page
 
ADezii
post Aug 27 2019, 12:01 PM
Post#11



Posts: 2,676
Joined: 4-February 07
From: USA, Florida, Delray Beach


@BarryStoker:
Sorry for coming in late, but I just noticed this very interesting Thread. I do believe that I can can arrive at a viable solution Transposing your Data to a single Record state for east Cost Center. It would involve a couple of steps, the results would end up in a Results Table, with 50 Fields (MAX of 5 Levels * MAX of 5 Users per Level * ID & Name Field for each User. My initial idea was to dynamically create the Table, but I though a static representation would be easier to implement. I forgot to mention that it would require lots and lots of VBA Code. If you are still interested, let me know and I'll give it a shot.
Go to the top of the page
 
ADezii
post Aug 28 2019, 08:48 AM
Post#12



Posts: 2,676
Joined: 4-February 07
From: USA, Florida, Delray Beach


  1. I exported my Results Table (tblResults) to Excel, removed the Columns with no data, patterned the empty cells for effect, and split the Columns with data into two for a better visual appearance. Hopefully, you can verify the Results with the Data in the Attachment of Post #1.
  2. Field designations are as follows:
    CODE
    L1U_ID_2 - Level 1/User ID 2
    L3U_NAME_4 - Level 3/Name of 4th User
  3. Hope this helps

This post has been edited by ADezii: Aug 28 2019, 08:55 AM
Attached File(s)
Attached File  Results1.JPG ( 41.37K )Number of downloads: 9
Attached File  Results2.JPG ( 36.26K )Number of downloads: 6
 
Go to the top of the page
 
ADezii
post Sep 5 2019, 02:21 PM
Post#13



Posts: 2,676
Joined: 4-February 07
From: USA, Florida, Delray Beach


Here is the Demo as you requested. Personally, I found no easy way to accomplish this Task so I came up with a 2-Step process, a lot of Code, and a very strict Field Naming Convention. In any event, have a look and see if this is what you are looking for. A strictly based SQL solution would be preferable, but I could not come up with one.
Attached File(s)
Attached File  Transpose_Transform.zip ( 40.05K )Number of downloads: 10
 
Go to the top of the page
 
BarryStoker
post Sep 6 2019, 11:21 AM
Post#14



Posts: 7
Joined: 6-February 08



Hi ADezil,

I have had a look at your demo this afternoon, and I have to say that your solution looks perfect. Your idea and execution of formatting this mess of data is so well done.

I need to catch up with some colleagues next week when they return from annual leave let them see if your solutions fulfils all of their requirements (I believe that it absolutely does)

I will PM you soon.

Thank you so much and kind regards,

Barry
Go to the top of the page
 
ADezii
post Sep 6 2019, 11:22 AM
Post#15



Posts: 2,676
Joined: 4-February 07
From: USA, Florida, Delray Beach


yw.gif , Good Luck with your Project.
Go to the top of the page
 
isladogs
post Sep 6 2019, 01:07 PM
Post#16


UtterAccess VIP
Posts: 1,684
Joined: 4-June 18
From: Somerset, UK


Adezii's solution is certainly impressive but it certainly does require a lot of code...
However I think it can be done without any code as follows.

1. Export the data to Excel and use PowerQuery / Get and Transform to convert the data to a normalised structure … as in tblIntermediate.
2. Reimport the normalised data to Access and run a crosstab query on that.

For anyone unfamiliar with using PowerQuery for this purpose, please see this thread explaining the idea with some examples
Normalise Spreadsheet Data for Access using Excel Power Query

I haven't tried the approach on this specific data and am about to go out for this evening but I'd be interested to know how it goes if anyone else wants to test it.

Hope that helps

--------------------
Go to the top of the page
 
ADezii
post Sep 6 2019, 02:23 PM
Post#17



Posts: 2,676
Joined: 4-February 07
From: USA, Florida, Delray Beach


QUOTE
However I think it can be done without any code as follows.

I threw in the towel on this one! pullhair.gif The only way that I could come to any kind of resolution given the strict formatting involved as well as the convoluted structure was with an inordinate amount of Code. Should you arrive at a non Code-Based solution I would be very happy to see it, but for now it is beyond my skill set. Thanks for the suggestions.
Go to the top of the page
 
isladogs
post Sep 7 2019, 07:34 AM
Post#18


UtterAccess VIP
Posts: 1,684
Joined: 4-June 18
From: Somerset, UK


Hi adezii
You're far too modest! I think your skill set is well above mine dazed.gif

Anyway I've done the easy bit with minimal code (but that was just to run two queries in sequence)
1. I used Excel PowerQuery to create an intermediate table called tblTemp

2. Then used an unmatched update query followed by an update query to create a normalised table tblFinal - identical to your tblIntermediate

What I haven't attempted to do is the tricky part creating the 'crosstab type' table which you've called tblResults.
Now I've looked a bit more carefully at this, you may well be right in saying that part isn't possible without code acclaim.gif

EDIT:
Oops. Forgot to attach the files earlier! Now added
The zip file includes:
- Excel file complete with the PowerQuery item
- Access file with the queries / procedure mentioned above

2nd EDIT:
Oops x2
Now with both files added to the zip! crazy.gif
Attached File(s)
Attached File  NormalisedStructureData.zip ( 55.38K )Number of downloads: 2
 

--------------------
Go to the top of the page
 
ADezii
post Sep 7 2019, 08:30 AM
Post#19



Posts: 2,676
Joined: 4-February 07
From: USA, Florida, Delray Beach


Hats off to you, isladogs! hat_tip.gif
Go to the top of the page
 
isladogs
post Sep 7 2019, 02:08 PM
Post#20


UtterAccess VIP
Posts: 1,684
Joined: 4-June 18
From: Somerset, UK


Ha! My skill set doesn't seem to include uploading the related Excel & Access files!
Have just edited the previous post to include them

--------------------
Go to the top of the page
 
2 Pages V  1 2 >


Custom Search


RSSSearch   Top   Lo-Fi    15th October 2019 - 06:39 PM