How to sort crosstab headers and issue with ### displayed in a field report

Hi,

I have to generate a report of how many hours every employees have worked.
Is there a way to sort the columns headers in a chronological order when they are generated dynamically?
I've tried to manually drag the column in the crosstab query, but the report's column doesn't seem to take it into account.
Also, some of my data are displayed as ### even if I've set the fields to 'Can grow'.

How can I fix this?
Report-layout.png
Crosstab.png
Hours-worked-per-week.png
David LelièvreAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

John TsioumprisSoftware & Systems EngineerCommented:
Can you give some more info on what you are after...probably a small mockup with the desired output (Excel)
0
Dale FyeCommented:
in the crosstab query properties dialog you will find a property for Column headers, you can enter the values you want across the top, no matter what they are, in the order you want them in.  But keep in mind, the only columns which will then be included in the crosstab are those where the column you are pivoting on has an exact match with the columns you have defined in the column headers.

In SQL view, this will result in an IN clause in the PIVOT portion of the query, something like:

PIVOT ON [somefield] IN ("val1", "val2", "val3", ...)

You might also want to consider using the Format( ) function to change explicitly define how numeric values should be displayed in the column headers, something like:

PIVOT ON Format([somefield], "00") IN ("01", "02", "03", "04", ...)
0
David LelièvreAuthor Commented:
I've attached an Excel with an example of what I'm looking with the two issues I'm having.

I hope it makes this a little more clear

Thanks.
Report-issue.xlsx
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Dale FyeCommented:
So, you want a report based on a crosstab query?  That is a little bit more of a challenge but I'm sure if you search here in EE on "crosstab report" you will get some examples.  Here is a link to one where I provided a sample database.

Are you always going to have those same column headers, or will they vary?  The advantage of the technique I mentioned above is that you will always have the same set of column headers, in the same order.  The down side is if you have columns which should be in there but are not, or have columns with no data associated with the column header.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
David LelièvreAuthor Commented:
@Dale

I used your sample as reference and it worked perfectly. Thanks!
0
Dale FyeCommented:
glad I could help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.