Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old March 18th, 2004, 05:10 AM
Registered User
 
Join Date: Mar 2004
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Default a couple of problems... please help!

Hello all,

I hope you have some time to read through all of this, this is something that has given me some sleepless nights...
Where to begin... Ok, here goes.

1) In my access 2000 project I have a report with a graph on it. In itself this already poses a problem in that the graph (an excel 2000 OLE object called MS Graph 8) "sometimes" does not display the correct data. Now this is something I have read a lot about, and have seen a lot of tips about, but somehow none seem to work.

2) Secondly, the query on which the graph is based: this is a cross-tab query, based on a number of tables. The idea is that during a week, a number of records is entered about the downtime of forklift trucks in the warehouse, and whether this downtime was caused through an incident, or preventive maintenance. The query on which the report then is based, returns the data per week. It looks something like this:

TruckType | Week | Preventive | Intervention
----------+----------+------------+-------------
X | 2004-W11 | 10 | 20
X | 2004-W12 | 10 | 20
X | 2004-W13 | 10 | 24
Y | 2004-W12 | 10 | 20
Y | 2004-W14 | 15 | 20
Z | 2004-W12 | 10 | 20
Z | 2004-W13 | 0 | 20
Z | 2004-W14 | 10 | 20

The report is then grouped on truck type per page, with a graph for that truck. The problem, however is that as you can see in the table, sometimes no records are entered for an entire week for a certain trucktype (there's no entry for week 13 for truck type Y). The end user however still wants to see this in the table and in the graph. Is there a way to do this? It needs to be done in the query, as both the report and the graph are based on it... I will put the SQL in a separate post, so you guys can play around with it.

3) Excel 2000 does not know the "ww" to format dates. When I enter "yyyy-ww" for a custom format, it returns 2004-ww. As a result, I use a vba sub (called JaarWeek) to format the date to 2004-W12, as you can see in the table above. Therefore, however, the labels on the X-axis of the graph are text, and a time-scale is not possible, to catch the "missing" weeks of the previous point...

Hope you can help me...

SpSp
 
Old March 18th, 2004, 05:12 AM
Registered User
 
Join Date: Mar 2004
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Here's the SQL:


TRANSFORM Val(Nz(Sum([DowntimeRecord].[Percentage]),0)) AS SumOfPercentage
SELECT (JaarWeek([BeginDatum],Null)) AS Week, [TruckType].[Omschrijving]
FROM TruckType INNER JOIN (TruckMerk INNER JOIN (Truck INNER JOIN (DowntimeRecord INNER JOIN TruckDowntimeRecord ON [DowntimeRecord].[DowntimeRecID]=[TruckDowntimeRecord].[FKDowntimeRecID]) ON [Truck].[TruckID]=[TruckDowntimeRecord].[FKTruckID]) ON [TruckMerk].[TruckMerkID]=[Truck].[TruckMerkID]) ON [TruckType].[TruckTypeID]=[TruckMerk].[TruckTypeID]
WHERE ((([DowntimeRecord].[BeginDatum])>=[forms]![DatumRange]![BeginDatum] And ([DowntimeRecord].[BeginDatum])<=[forms]![DatumRange]![EindDatum]))
GROUP BY (JaarWeek([BeginDatum],Null)), [TruckType].[Omschrijving]
PIVOT [TruckDowntimeRecord.ProbleemAard] In ("Preventief","Interventie");

 
Old March 18th, 2004, 06:03 AM
Registered User
 
Join Date: Mar 2004
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I want to uplaod my database to the website. I want to know how to set connection to web site's data ( I mean data sourcs = ??? ).




 
Old March 18th, 2004, 02:01 PM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

if this is an Access Project .adp file, the transform will not be supported. You have to use TSQL.



Sal
 
Old March 19th, 2004, 06:32 PM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I guess you mean that it is a regular .mdb file.
___________________

Quote:
quote:The problem, however is that as you can see in the table, sometimes no records are entered for an entire week for a certain trucktype (there's no entry for week 13 for truck type Y).
You can use an auxiliary table as a join to force items that do not exist. This table would have to have two fields, Week and Truck type. You enter every possible value combination for both and when you join them properly, you will get the desired result.


Let me know if it works.




Sal
 
Old March 22nd, 2004, 06:20 AM
Registered User
 
Join Date: Mar 2004
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Sal,
to make sure I understand you correctly: do you propose creating a table with all weeks from now until... let's say ten years from now already defined in them?
Perhaps that would work. Not an elegant way to do it, but as it's the only apparant way to do it, perhaps the best.
Perhaps I could use three colums in the table: one with the 2004-W12 format, and two with the begin and end-dates of the weeks (in date format). This way I can check for each date in which week they fall, and immediately return the correct format...
Thanx for the tip!
SpSp
 
Old March 24th, 2004, 12:23 PM
Registered User
 
Join Date: Mar 2004
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Default

The above method didn't work in my case, as I not only split the data per week, but also per type of truck. the data was then shown in the report with one page per type of truck.
I just resorted into writing a sub that just adds records to the different tables each week (with a check to a global values table to see whether the "ghostrecords" have already been added the current week)
Regards,
SpSp





Similar Threads
Thread Thread Starter Forum Replies Last Post
Couple Errors on AddEditArticles page pinch BOOK: ASP.NET 2.0 Website Programming Problem Design Solution ISBN: 978-0-7645-8464-0 13 December 14th, 2007 04:01 AM
Couple questions about embedding values into html warhero XSLT 2 July 3rd, 2007 03:06 PM
New to XML. Have a couple basic questions sswingle XML 2 April 22nd, 2006 07:13 AM
A couple of questions: czambran BOOK: Beginning CSS: Cascading Style Sheets for Web Design ISBN: 978-0-7645-7642-3 4 March 23rd, 2005 03:13 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.