Trying to transpose data for form

[SmudgerGTS]

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

 

[theDBguy]

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]

I did try that but my lack of knowledge stops me from knowing how / what to do with subsequent queries

 

[theDBguy]

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]

What am I doing wrong - I get "The uploaded file does not have an allowed extension. "

My file name is Diary11.accdb

 

[theDBguy]

What am I doing wrong - I get "The uploaded file does not have an allowed extension. "

My file name is Diary11.accdb

Try zipping it first...

 

[SmudgerGTS]

Lets see if that worked, see attached

 

[theDBguy]

Lets see if that worked, see attached

Hi. Thanks. Assuming I understood what you were trying to do, give this query a try.

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]

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]

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]

Wait... I'm trying something else that might work. Please stand by...

 

[SmudgerGTS]

ok

 

[theDBguy]

ok

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.

 

[SmudgerGTS]

Well done thats it exactly

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

Thanks again

Smudger

 

[theDBguy]

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]

Think i undersdtand the basics.
Thanks again

 

[SmudgerGTS]

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]

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.

 

[SmudgerGTS]

No worries, thanks

 

[theDBguy]

No worries, thanks

Hi. The site was down yesterday, so I didn't get a chance to take a look. I'll try again later today.

Just letting you know...