Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old March 18th, 2004, 05:10 AM
Registered User
 
Join Date: Mar 2004
Location: , , .
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
Reply With Quote
  #2 (permalink)  
Old March 18th, 2004, 05:12 AM
Registered User
 
Join Date: Mar 2004
Location: , , .
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");

Reply With Quote
  #3 (permalink)  
Old March 18th, 2004, 06:03 AM
Registered User
 
Join Date: Mar 2004
Location: mandalay, , .
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 = ??? ).




Reply With Quote
  #4 (permalink)  
Old March 18th, 2004, 02:01 PM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Location: Clarksville, TN, USA.
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
Reply With Quote
  #5 (permalink)  
Old March 19th, 2004, 06:32 PM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Location: Clarksville, TN, USA.
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
Reply With Quote
  #6 (permalink)  
Old March 22nd, 2004, 06:20 AM
Registered User
 
Join Date: Mar 2004
Location: , , .
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
Reply With Quote
  #7 (permalink)  
Old March 24th, 2004, 12:23 PM
Registered User
 
Join Date: Mar 2004
Location: , , .
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
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 01:25 AM.


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