Results 1 to 15 of 15

Trying to build a 2 dimension report, please advise

  1. #1
    Tyroneboon is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Oct 2017
    Posts
    8

    Trying to build a 2 dimension report, please advise

    Hi All,



    I need some advices here, i am pretty new to MS access, i am trying to create a two dimension report as per images attached, can a guru tell me if that is possible?

    I was playing around with expression builder but i never got the right expression/formula to do the sum within time frame(7am-8am) and so on forth.

    I tried Dsum and sum, it didnt work.

    Dont know what else i can do about it.

    please help!!

    Thanks!
    Attached Files Attached Files
    Last edited by Tyroneboon; 10-12-2017 at 09:28 PM.

  2. #2
    aytee111 is online now Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,661
    Don't worry about the report, that will display the data. The data will come from a query, that is where you need to begin.

    Query1: Select entry date, quantity and amount with a time bucket field of: TimeBucket: Format(EntryDate,"hh ampm").
    Query2: using the query wizard, create a query and sum the quantity
    Query3: using the query wizard, create a query and sum the amount
    Query4: bring in query2 and query3, join them on entry date and select all the fields

    Once you have this working, you can create a report based on query4.

  3. #3
    Tyroneboon is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Oct 2017
    Posts
    8
    Hi aytee111,

    Thanks for the quick response.

    I manage to create those reports, but my current issue is that the report is not showing the figure, below is my expression, could you please advise what is wrong?

    =DSum([Sum Of ItemValue],"qrySalesAnalysis",[EntryTimeFrame]="7 PM")

    above is for 7pm time slot, which i will customised each slot individually.

    thank you for your time!

  4. #4
    Micron is online now VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    3,111
    You didn't say if you want the sums/counts for a time period across all dates or per date. Either way, I would have thought a calculated control on the report would suffice, without extra queries. Can't say where it would go though, because the date span of the totals isn't clear. A bigger problem at the moment, I think, is that for 8:00 AM, which column would it go in - the first or second? They both cannot be exactly 08:00:00.
    - "doesn't work" is no help.Error #s/text do. State what's happening.
    - Use code tags for code/sql; show where errors occur
    Make all suggested changes in copies of your database or to its objects

  5. #5
    Tyroneboon is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Oct 2017
    Posts
    8
    Hi Micron,

    Initially i thought it is possible to do via calculated control, but i wasn't able to get around the expression to be between i.e "7:00 AM">=EntryTime<"8:00 AM"

    i have work out the data section, now i am trying to segregate them into its hourly period i.e

    7:00 am - 7:59 am
    8:00 am - 8:59 am
    9:00 am - 9:59 am
    10:00 am - 10:59 am

    I have insert images of my dataset and my template, could you please advise where i have went wrong doing this?

    Thanks alot.

    Click image for larger version. 

Name:	query result.png 
Views:	16 
Size:	12.4 KB 
ID:	30792Click image for larger version. 

Name:	template.png 
Views:	16 
Size:	8.1 KB 
ID:	30793

  6. #6
    Micron is online now VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    3,111
    i wasn't able to get around the expression to be between i.e "7:00 AM">=EntryTime<"8:00 AM"
    I wouldn't try. I meant the control would go in the correct sector of the report and possibly be set to running sum (if in a group) or perhaps in the footer if across all dates for a particular time slot. Seems you are beyond that now as you have a query that calculates totals.

    It seems you are trying to transpose rows of time frame values across columns and I'm not sure you can. Crosstab queries are sometimes used to do this, but when you try to base a report on one, the number of fields (columns) can vary from one time to another by nature of such a query, causing poor data display and often errors as the report is looking for fields based on a query that may not have them. If you cannot design a report that displays the time frames as rows, then I think you're looking at doing something I try not to ever advise - store your data in table fields rather than rows. This violates the norms of database design. Perhaps others will come forward with a better idea, or you can research basing Access reports on crosstab queries to see if anyone ever solved the volatility issue.

    One other aspect which is not clear from your images is the nature of the EntryTimeFrame data. It may look like hour periods to me, but might actually contain minutes/seconds in the underlying data. In that case, comparing 08:00 to 08:00:20 will never work (I see that your table data includes Time). You might then have another problem to work around to ensure you try to slot only hours into your report.

    EDIT: also, you have to compare a field against criteria/values properly for any chance of it working. Not "7:00 AM">=EntryTime<"8:00 AM" but
    [EntryTime] >= SomeTimeValue AND [EntryTime] <= OtherTimeValue
    or you can use BETWEEN, which can be problematic if the field contains minutes and you are trying to use only dates.
    Last edited by Micron; 10-12-2017 at 06:53 PM. Reason: spelin

  7. #7
    Tyroneboon is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Oct 2017
    Posts
    8
    Hi Micron,

    If you look at my dataset, i have already group the EntryTime into one hour frame interval, therefore now my only issue is to show them as individual hour instead of whole day, i would want my hour to be shown in columns instead of rows, i know showing in rows is possible, but it is very lengthy and not easy for comparison.

    Could you please advise if you know why my expression didnt work -> DSum([Sum Of ItemValue],"qrySalesAnalysis",[EntryTimeFrame]="7 PM")

  8. #8
    Micron is online now VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    3,111
    You're not understanding. I'm saying I can see minutes in your table but only hours in your query. Depending on what you have done the minutes may really be there even though I can't see them. In that case, 8:00 AM will not equal any other table value except 8:00:00. You will have to post the query sql or a zipped copy of your compacted db. I may not be able to open it.

  9. #9
    Tyroneboon is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Oct 2017
    Posts
    8
    Quote Originally Posted by Micron View Post
    You're not understanding. I'm saying I can see minutes in your table but only hours in your query. Depending on what you have done the minutes may really be there even though I can't see them. In that case, 8:00 AM will not equal any other table value except 8:00:00. You will have to post the query sql or a zipped copy of your compacted db. I may not be able to open it.

    Hi Micron, I have attached my access file for your reference, please refer to first post.

    thanks again!

  10. #10
    Tyroneboon is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Oct 2017
    Posts
    8
    Quote Originally Posted by Micron View Post
    You're not understanding. I'm saying I can see minutes in your table but only hours in your query. Depending on what you have done the minutes may really be there even though I can't see them. In that case, 8:00 AM will not equal any other table value except 8:00:00. You will have to post the query sql or a zipped copy of your compacted db. I may not be able to open it.

    I have made changes as advised by aytee111, therefore it should all fit into the hourly column.

  11. #11
    aytee111 is online now Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,661
    Oh my, I left out one crucial word in post # 2. What a bad mistake, it is causing all the confusion. Queries 2 and 3 are supposed to be CROSSTAB queries, that is the word I left out. My apologies to both of you.

  12. #12
    Micron is online now VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    3,111
    I'm not able to open tblSalesItems as it has one or more features that are not backwards compatible. My guess is that it's a calculated field.
    So I can only drill down 3 levels from the report to see what's what. With any expression that you create to look for records to stick in a 8 to 9 time slot, asking that expression to find 8:00 AM in your form/report, it will look at the data source and how it's actually stored regardless of how it looks. So your report looks at a query, which looks at a query, which looks at a query, which looks at a table. That table is the one I cannot look at, and my guess is the table probably stores date/time as 08:20:30 AM but includes seconds since you're using Now() as default values in places. So asking for records with 08:00 AM is pointless since that is the only thing it will match given your data. It does not match 08:00:01 if that's what's in the record. That record is one second off of the value you're looking for. The way around this, as I've already posted in #6 is to correctly build the expression. Aside from the mistakes pointed out in that post, I just remembered that you're trying to treat time as text values in your expression: ="7 PM"). Quotes denote text values, so you are looking for the phrase 7 PM, not the time of 19:00:00

    Try aytee111's suggestion perhaps. Hopefully he/she will be able to see you through this. I have to bow out because I can't work with your db. Sorry.

  13. #13
    Tyroneboon is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Oct 2017
    Posts
    8
    Quote Originally Posted by Micron View Post
    I'm not able to open tblSalesItems as it has one or more features that are not backwards compatible. My guess is that it's a calculated field.
    So I can only drill down 3 levels from the report to see what's what. With any expression that you create to look for records to stick in a 8 to 9 time slot, asking that expression to find 8:00 AM in your form/report, it will look at the data source and how it's actually stored regardless of how it looks. So your report looks at a query, which looks at a query, which looks at a query, which looks at a table. That table is the one I cannot look at, and my guess is the table probably stores date/time as 08:20:30 AM but includes seconds since you're using Now() as default values in places. So asking for records with 08:00 AM is pointless since that is the only thing it will match given your data. It does not match 08:00:01 if that's what's in the record. That record is one second off of the value you're looking for. The way around this, as I've already posted in #6 is to correctly build the expression. Aside from the mistakes pointed out in that post, I just remembered that you're trying to treat time as text values in your expression: ="7 PM") . Quotes denote text values, so you are looking for the phrase 7 PM, not the time of 19:00:00

    Try aytee111's suggestion perhaps. Hopefully he/she will be able to see you through this. I have to bow out because I can't work with your db. Sorry.
    Thanks for having a go, in my query i have converted the time using this -> EntryTimeFrame: Format([EntryTime],"h AM/PM"), how do i put in an expression to compare time instead of text value?

    @aytee111, i dont quite understand when you mentioned crosstab queries, step 4 is not quite working because it only select the available column, it doesnt have the columns that the database doesnt have i.e the database only have 7pm and 10pm, other times doesnt exist, it wouldnt have the column and the query wouldnt be versatile enough to add them.

  14. #14
    Micron is online now VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    3,111
    Why format the time? Just work with it as stored in the table. Use the BETWEEN operator in the query criteria and filter the records for times between 08:00 AND 08:59:59 for that particular field. Been in this forum too much lately, methinks. Seems that much is starting to meld because I really feel like I suggested this already.
    Format([EntryTime],"h AM/PM" this doesn't make it text. You made it text in your expression.

  15. #15
    Tyroneboon is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Oct 2017
    Posts
    8
    Quote Originally Posted by Micron View Post
    Why format the time? Just work with it as stored in the table. Use the BETWEEN operator in the query criteria and filter the records for times between 08:00 AND 08:59:59 for that particular field. Been in this forum too much lately, methinks. Seems that much is starting to meld because I really feel like I suggested this already.
    Format([EntryTime],"h AM/PM" this doesn't make it text. You made it text in your expression.
    Thanks micron, i have managed to sort it out, i created a report using query 2 and query 3 separately, i got what i wanted, although i had to create 27 columns, 24 columns for each hour of the day, but still manage to create that.

    I have also manage to create weekly report using the same method, which is very useful for me as well.

    I have attached the pdf reports for you guys to see.

    Thanks all for the time.
    Attached Files Attached Files

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Dimension dependent price on invoice
    By knh2r in forum Access
    Replies: 22
    Last Post: 07-12-2016, 05:45 AM
  2. Replies: 1
    Last Post: 01-29-2015, 11:41 AM
  3. Replies: 2
    Last Post: 08-22-2014, 10:04 PM
  4. Advice on how to Build a Specific Report
    By cbgroves in forum Reports
    Replies: 12
    Last Post: 12-12-2011, 07:27 AM
  5. Please Help with Setting up Fact & Dimension Tables
    By oPEEPINGTOMo in forum Database Design
    Replies: 1
    Last Post: 02-09-2011, 11:03 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums