473,320 Members | 1,978 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

crosstab query dynamic colums date range in days

I use ACCESS 2013.
I would like to view by a specified period of dates all promotion that meet the date range
especially I would like to see a report grouped by client resulting all clients, name of the promo and if the promo cover a specific day in the range specified

example:
date range: 01.01.2016 to 10.01.2016 (from form)

Result I want to obtain:
Columns:
Expand|Select|Wrap|Line Numbers
  1. Client    promoname    01.01.2016  02.01.2016  03.01.2016   04.01.2016   ...  10.01.2016
  2. rows:
  3. c1        promo1          -           OK          OK            -               -
  4. c2        promo2          OK          OK          -             -               -
  5. c3        promo3          -            -          -             -               OK
  6. ....
  7.  
the promo table is filled with these fields:
Expand|Select|Wrap|Line Numbers
  1. Client,promoname, promostartdate,promoenddate
  2. c1,promo1,02.01.2016,03.01.2016
  3. c2,promo2,25.12.2015,02.01.2016
  4. c3,promo3,10.01.2016,10.02.2016
  5.  
I am not able to create the dynamics columns by date range I want to see all days in the range... and also a dynamic report...
Anybody can HELP?
:-(
Thanks
Oct 7 '16 #1
11 1704
Oralloy
985 Expert 512MB
burolla,

Will you tell us the solutions that you've already tried?

Cheers,
Oralloy
Oct 8 '16 #2
hello no solution tried... do not know how and where to start.. -:-(
I develop in c# I am very basic in ms access... :-(
I am very beginner in access.
Help appreciated...
thanks
Oct 8 '16 #3
hello probably access is not the right tool?

Do I have to use recordset VBA to create the column selection?
Oct 11 '16 #4
Oralloy
985 Expert 512MB
burolla,

My apologies, I haven't received notification messages correctly, and I lost track of this conversation.

There is a cross-tab query available in access.

Unfortunately I'm not in a position to write one for you right now.

Try looking up the SQL keyword "TRANSFORM".

This is an implemnetation of a Pivot query.

Luck,
Oralloy
Oct 11 '16 #5
PhilOfWalton
1,430 Expert 1GB
Oralloy is almost certainly correct, but we would need to see your table structures (and preferably some test data) before giving further advice.

Phil
Oct 11 '16 #6
hello...
oralloy
thanks for your help... and advice on cross tab and sql transform I will take a look shortly...

Phil
the table structure is the one written above with some other fields that are not important...

Expand|Select|Wrap|Line Numbers
  1. string,string,date,date
  2. Client,promoname, promostartdate,promoenddate
  3. c1,promo1,02.01.2016,03.01.2016
  4. c2,promo2,25.12.2015,02.01.2016
  5. c3,promo3,10.01.2016,10.02.2016
  6. ....
  7.  
as I wrote above I am not able, never used, cross tab table and do not know where to start...
Probably I need to create recordset or tmp table to fill for each promo selected in date range these fields...
day,promoname,promoclient

and then using this table as the cross tab is it the only way? is it correct?
:-(
thanks
Oct 13 '16 #7
PhilOfWalton
1,430 Expert 1GB
Am I correct in assuming that you are using English date format dd/mm/yyyy rather than American format mm/dd/yyyy.

On the results table what are you trying to show? Is it which promotion is active for each client for however many days there are in each month? (31 columns for January, 28 or 28 for February etc)

If so, how are you indicating which month to use?

Phil
Oct 13 '16 #8
hello phil,
I would like to show:
client, promo name,[number of days from date range selection max 60 days ] in each cell of the day if in that specific day the promo is available/active...

example of report ...

https://www.dropbox.com/s/8l77inrqm3...10_03.png?dl=0

thanks for your help...
Oct 13 '16 #9
PhilOfWalton
1,430 Expert 1GB
3 things

You didn't answer my question on date formats.

Your illustration shows 17 dates. If you want 60 dates, the columns are going to be very narrow. Is this acceptable?

Have you had a look at any Calendar databases. I certainly have one that will work for you but it is exceedingly complicated and slow, so I don't use it. I now use Google Calendar.

It is not worth showing an image of it in this forum as it will be too small to see, but if you send a private message with your email address, I can send a picture. I'm sure there are better calendars out there though.

Phil
Oct 13 '16 #10
hello phil
sorry for delay... here I am...
so.. dates are in IT format (dd/mm/yyyy), in my example image there were only few days, only for semplification... but the maximum date range will be 60 days... and the minimum could be 10 or two weeks (14)...
columns width will not be a problem

---
Let me know if there is a simple solution... thanks...
Oct 18 '16 #11
PhilOfWalton
1,430 Expert 1GB
Well here's a start. Simple solution - No. Took me 4 hours to crack.

You need to do to the module and in the immediate window type something like CreateCrossTab(#20 dec 15#, #1 mar 16#)

This will throw out a warning that the period is over 60 days, and cut you down to 60 days from the first date.

The Query "PromoCrossTab" gets deleted and re-created.

Hopefully, the output will be a start for your report.

Keep me advised of progress.

Phil
Oct 18 '16 #12

Sign in to post your reply or Sign up for a free account.

Similar topics

2
by: Claus Haslauer | last post by:
Hi, I want to create a crosstab query that looks like this Date | Elevation 1 | Elevation 2 | ... ______________________________________________________________________ Date 1 | xx.y | xx.y...
14
by: Tina | last post by:
My employer tracks productivity/performance of clinicians (how much they bill) each week, its averages for the month, and the 6 months. These averages are compared to their expected productivity....
1
by: jilppe | last post by:
I am creating a report based on a crosstab (BodyPart). This crosstab counts the number of incidents for each body part by month. However the data is for different years and I would like the user to...
3
by: russellhq | last post by:
Hi, I'm fairly new to access and have a little trouble with a crosstab query I've setup. I have a main form where the user selects a project name and below in a subform, a crosstab query is...
22
by: boliches | last post by:
I am trying to get a crosstab query (in access 2000) to group data by date range. TRANSFORM Sum(tblInvoice.InvBalance) AS SumOfInvBalance SELECT tblInvoice.DealerID, Sum(tblInvoice.InvBalance) AS...
2
by: Jim Devenish | last post by:
I wish to create a crosstab query as the record source for a report. It needs to count data between selected dates which are entered by the user in a popup window. The following Select query...
11
ollyb303
by: ollyb303 | last post by:
Hello, I am using a dynamic crosstab report to track performance statistics for my company and I have hit a problem. I would like the option to track stats daily (for the last 7 complete...
1
by: Kyrillos T | last post by:
Hello, I have created a crosstab query in Access which shows me stuff holidays calculated in days for the current year , year - 1 and year -2. Therefore there are 3 columns with headings...
4
by: Quizzed | last post by:
Hi, Im attempting to run a query over a remote Db400 table using MS Access'97 where i need to specify the from and to date. Depending on the current date, the required dates will change so I'm using...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.