Trying to transpose data for form (1 Viewer)

SmudgerGTS

New member
Local time
Today, 08:24
Joined
Mar 16, 2020
Messages
23
Hi everyone, new here and also to Access although have dabbled on and off for a few years.

My project is to create an electronic version of our current desk diary and delivery board that is on wall. We write delivery in desk diary and then pin on wall any associated paperwork on the day the delivery is due. This gives an immediate visual indication as to how busy we are on a day. Reason for wanting an electronic version is to enable remote working by sales team.

So I have created a basic database and populated with some sample data, have queries and reports working ok for various dates and date ranges.

What I'm trying to achieve is what I would call a tabular report, where the dates are listed in the top row (Header) and then under each day our sales order numbers are listed in colomns under the date in the header. The colomns of numbers would need to be sorted by delivery time with the time deliveries being at the top of the colomn and the non timed being afterwards.

I have tried and crosstab query and it works ok BUT I cant get the colomns to populate as I want as as there are large gaps at the tops due to other data.

I have attached PIC1 that show the result of my crosstab query
and PIC2 that shows how I would like the report to appear. The key on the right is not required - it just shows the conditional formatting that would need applying.

Hope that makes sence

Smudger
 

Attachments

  • Pic2.JPG
    Pic2.JPG
    38.2 KB · Views: 295
  • Pic1.JPG
    Pic1.JPG
    44.9 KB · Views: 466

theDBguy

I’m here to help
Staff member
Local time
Today, 01:24
Joined
Oct 29, 2018
Messages
21,357
Hi. Welcome to AWF!

Maybe you could try creating another query, on top of your crosstab query, to get the final result. Just a thought...
 

SmudgerGTS

New member
Local time
Today, 08:24
Joined
Mar 16, 2020
Messages
23
I did try that but my lack of knowledge stops me from knowing how / what to do with subsequent queries
 

theDBguy

I’m here to help
Staff member
Local time
Today, 01:24
Joined
Oct 29, 2018
Messages
21,357
I did try that but my lack of knowledge stops me from knowing how / what to do with subsequent queries
Hi. Maybe we could help you if you could post a sample db for testing.
 

SmudgerGTS

New member
Local time
Today, 08:24
Joined
Mar 16, 2020
Messages
23
What am I doing wrong - I get "The uploaded file does not have an allowed extension. "

My file name is Diary11.accdb
 

SmudgerGTS

New member
Local time
Today, 08:24
Joined
Mar 16, 2020
Messages
23
Lets see if that worked, see attached
 

Attachments

  • Diary11.zip
    80.5 KB · Views: 452

theDBguy

I’m here to help
Staff member
Local time
Today, 01:24
Joined
Oct 29, 2018
Messages
21,357
Lets see if that worked, see attached
Hi. Thanks. Assuming I understood what you were trying to do, give this query a try.

SQL:
TRANSFORM First(TabDELDetail.DelSO) AS FirstOfDelSO
SELECT IIf(IsNull([DelTime]),Null,Format([DelTime],"hh:""00""") & "-" & Format(DateAdd("n",Hour([DelTime])*60-1,0),"Short Time")) AS [Key]
FROM TabDELDetail
GROUP BY IIf(IsNull([DelTime]),Null,Format([DelTime],"hh:""00""") & "-" & Format(DateAdd("n",Hour([DelTime])*60-1,0),"Short Time"))
ORDER BY IIf(IsNull([DelTime]),Null,Format([DelTime],"hh:""00""") & "-" & Format(DateAdd("n",Hour([DelTime])*60-1,0),"Short Time"))
PIVOT Format([DelDate],"Short Date");
Hope it helps...
 

SmudgerGTS

New member
Local time
Today, 08:24
Joined
Mar 16, 2020
Messages
23
Nearly, I think I may have confused as the key does not need to be visible. I hope to apply conditional formating to the listed s/o numbers to indicated their respective delivery times. See earlier Pic 2 - again pleae ignore the Key colomn.

I just need the s/o numbers in vertical colomns with any times at the top and then the untimed below. On days where ther are no timed deliveries the list should start to build durectly from row one.

I think you are nearly there, thanks for your help.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 01:24
Joined
Oct 29, 2018
Messages
21,357
Nearly, I think I may have confused as the key does not need to be visible. I hope to apply conditional formating to the listed s/o numbers to indicated their respective delivery times. See earlier Pic 2 - again pleae ignore the Key colomn. I just need the s/o numbers in vertical colomns with any times at the top and then the untimed below. On days where ther are no timed deliveries the list should start to build durectly from row one. I think you are nearly there, thanks for your help.
Okay, the other way I'm thinking of doing that is by creating a separate query for each column in the crosstab filtering out the null values and then stitching them together into one query again, so the values populate from the top. That's a lot of work and may require using code if the dates are going to be dynamic.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 01:24
Joined
Oct 29, 2018
Messages
21,357
Wait... I'm trying something else that might work. Please stand by...
 

theDBguy

I’m here to help
Staff member
Local time
Today, 01:24
Joined
Oct 29, 2018
Messages
21,357
Hi. Please see the attached modified copy of your db and open the query xtabDBG. If that does what you want, I can explain what I did.
 

Attachments

  • Diary11 (2).zip
    67 KB · Views: 477

SmudgerGTS

New member
Local time
Today, 08:24
Joined
Mar 16, 2020
Messages
23
Well done thats it exactly 👏👏

A brief explanation would help me understand the extra queries that I see.

Thanks again

Smudger
 

theDBguy

I’m here to help
Staff member
Local time
Today, 01:24
Joined
Oct 29, 2018
Messages
21,357
Well done thats it exactly 👏👏

A brief explanation would help me understand the extra queries that I see.

Thanks again

Smudger
Excellent! So, the first idea I had earlier was to create a separate query for each date, which didn't really sound practical, so I thought of another way. Since the requirement was to place the "first" data for each date on the top, we just needed a "way" to designate them as "first." That's where the Grp calculated column comes in. We are basically assigning a counter for each data for each date. The "catch" with this approach (for now anyways, until I can figure out a way to overcome it), is we have to then create a temporary table to house that new "grouped" data. But once we have done that, we can now use a crosstab query to get the result you wanted. Hope that makes sense...
 

SmudgerGTS

New member
Local time
Today, 08:24
Joined
Mar 16, 2020
Messages
23
Hi would it be possible to pull across to the xtabDBG query fields DelWhere and DelTime ? At present I can not get the conditional formatting to work due to the time not being available, the DelWhere would also be useful.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 01:24
Joined
Oct 29, 2018
Messages
21,357
Hi would it be possible to pull across to the xtabDBG query fields DelWhere and DelTime ? At present I can not get the conditional formatting to work due to the time not being available, the DelWhere would also be useful.
Hi. Sorry for the delay. I'll check it out when I get to work and let you know.
 

Users who are viewing this thread

Top Bottom