running sum problem..


.

i have a problem which may be easily by someone but i do noe know how to approach it...

i have a table called bookings with a column called 'ratetype' and a with a date column called 'starDate'...

i'm wanting to be able to generate a report where the user can choose a rate type and then specify a date range to give me a count of how many records which are in fact beds booked for that date range and rateType.

because i have a start date and an end date to this range i'm running a query which loops and counts for each day.... So i am left with a cloumn of totals for a monthly period... However, i want to be able to total these... How do i go about this\t

below is a copy of the code i'm using. I'm open to other suggestions if there is a better way to go about this...

i'm using a jet/access database.

thanks in advance....

dim daycount
' number of days for the booking
' ie add daycount to startdate to get enddate

daycount=datediff("d",firstmth,lastmth)

function sqldate(s)
if isDate(s) then sqldate=day(s)&" "&monthname(month(s))&" "&year(s)&" "&hour(s)&":"&minute(s)&":"&second(s)
end function

dim i,maxbeds
maxbeds=0

' loop from 0 to daycount
' add i to start date to get each date between start and end dates

for i=0 to daycount

dim comparedate

comparedate=dateadd("d",i,firstmth)

Set objConn = Server.CreateObject("ADODB.Connection")

objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("db1.mdb") & ";"

sql="SELECT COUNT(nono) AS Booked FROM Bookings WHERE datediff('d',#"&sqldate(comparedate)&"#,nono)
Posted On: Monday 22nd of October 2012 05:58:02 AM Total Views:  263
View Complete with Replies

RELATED TOPICS OF Microsoft Access PROGRAMMING LANGUAGE




Column sum problem

MS Access 2007. I created a simply query which tallies 6 "Qty" columns. I then created another query which looks at the first query and multiplies the "Total Qty" by "Market Value" Field name = Total Worth: [Total Qty]*[Market Value] This the second query works fine and gives me the results I need, (mathematically checked by calculator) for the four test records I have created. The thorn in my side is that no matter how specific I follow access for dummies (applicable to me) instruction for generating the total sum for the "Total Worth" column so that I get my net worth for the four test records I can't get a result! What have I done wrong Do I have to create a third query I have followed various different methods and nothing will generate the one single total I need. Pulling my hair out!
VIEWS ON THIS POST

149

Posted on:

Wednesday 10th October 2012
View Replies!

Fast running sum or a way to index times.

I have a table with the following Autonumber Time Temp 1 01:00:10 500 2 02:01:10 600 3 02:00:15 650 . . . . 11,759 23:59:00 700 11,760 23:59:10 600 11,761 23:59:50 650 11,762 00:00:10 750 11.763 00:05:50 800 . . . . . 23,759 23:59:10 700 23,760 23:59:15 700 23,761 23:59:55 760 23,762 00:00:15 900 23,763 00:06:50 800 . . . . Etcetera.... My issue is I need to graph the time on the X coordinate and the temperature on the Y coordinate (easy) but the clock resets every 24 hours and the logs run for days. The time is not equal, the records are not equal, I need the x coordinates to be scaled and the times vary so I cannot graph by the log number, and I have no control over the software writing it. I have created a running sum using Dsum, but it takes too long (it is for a multiuser application that will be used many times on many different changing data sets) I'm out of ideas, any help would be appreciated. Thank you.
VIEWS ON THIS POST

150

Posted on:

Wednesday 17th October 2012
View Replies!

SQL Statement problem...

Code: ] #1 UPDATE MonitorPallets SET Status = 'Shipped' WHERE EXISTS ( SELECT [TruckInvoice Pallet Query].ID FROM [TruckInvoice Pallet Query] WHERE [TruckInvoice Pallet Query].ID = MonitorPallets.ID) #2 UPDATE GeneralPallets SET Status = 'Shipped' WHERE EXISTS ( SELECT [TruckInvoice Pallet Query].ID FROM [TruckInvoice Pallet Query] WHERE [TruckInvoice Pallet Query].ID = GeneralPallets.ID) #1 works fine and updates only the values in MonitorPallets which exist in the TruckInvoice Pallet query. #2 doesn't work as expected. It updates ALL values in GeneralPallets table. The tables have no relationships defined. Why isn't it working
VIEWS ON THIS POST

135

Posted on:

Wednesday 17th October 2012
View Replies!

Duplicacy in report: Error in sum

I am making a payment data base and table structure is like this: ProjectNo OrderNo OrderDate InvoiceNo InvoiceDate InvoiceAmount ChequeNo ChequeDate ChequeAmount 1st thing is to be done is data input till Invoice Amount and then on due dates payments are released thru cheques. Sometimes payments are not one to one, means there can be 3 part payments for one invoice. So when we take the balance payment due, we get errror. Report shows total invoice amount 3 times, because details have three fields. Means like this: Overall Supplier Invoice Payment Status For The Supplier Name - True Fab Engineers Private Limited InvoiceNo Date Amount PaymentDate ChequeNo ChequeDate ChequeAmou Supplier Name True Fab Engineers Private Limited ORDER NO. P-158/B/10848 DATED 12-Apr-07 54 13-Apr-07 2,714.00 12-Jun-07 523631 06-Jun-07 2,714.00 12-Jun-07 0 0.00 57 23-Apr-07 4,154.00 22-Jun-07 523631 06-Jun-07 4,154.00 22-Jun-07 0 0.00 TOTAL PAYMENT DUE: 13,736.00 TOTAL PAYMENT MADE: 6,868.00 You can see that total amount due is just double of actual. Beacuase of this all accounting is going waste. Please help.
VIEWS ON THIS POST

184

Posted on:

Thursday 18th October 2012
View Replies!

Macro running module failure

hey there, i have a macro that has 2 actions: set warnings to warning on: NO and runCode to Function Name: cmdImport_Click() where cmdImport_Click() is a Private Sub in a module within the same database. when i try to run the macro i get an error that stated "the expression you entered has a function name that MS Access can't find." but if i open the module called ImportMSExcelFile and set my cursor on the Private Sub cmdImport_Click() and select the green arrow to run the procedure it runs fine. any suggetions tuktuk
VIEWS ON THIS POST

68

Posted on:

Thursday 18th October 2012
View Replies!

Dsum with Multiple Criteria

Hi! I have two related tables, one containing human resource information, the other containing a weekly record of hours worked by employees. Here's the dilemma: I need to keep a running total of hours available based on hours worked. Employees begin each 7 day work week with 70 hours of time available. Any hours worked in that week are subtracted from the available hours for that employee. If there are two days in a row not worked during any given week, the 70 hours needs to automatically reset. These hours are tracked in the Hours Available field (a calculated control which needs the Dsum expression) which should examine the Hours Worked field. Presently, the Hours Worked control gives a total of all hours worked for the employee shown on screen, but I need to examine the current work week to see whether or not an employee has worked more than 70 hours (it's a legal requirement.) If during any 7 day period counting backwards from the present date the employee had two consecutive days off, the employee becomes eligible to work 70 hours and the countdown starts again. In short, the total hours worked should be subtracted from 70 until 0 is achieved in any given week, unless the employee has had two days off (Hours Worked sum for two consecutive days =0.) I have attached a paired down version of the database for review. Any ideas Tom
VIEWS ON THIS POST

80

Posted on:

Thursday 18th October 2012
View Replies!

sum query problem

Hy,I have problem how to make query for my report I've got three tables containing next fields: firm: firm_id,name of firm calculations: calculations_id,month_id,firm_id,amount,year months: month_id,name of month where fields ending with "_id" are primary keys. And i have form with listbox showing months and textbox.When i doubleclick on specific month my textbox shows me months_id.(like "1,2" for january and february for example) What i need is how to make query that shows me sumary amount for chosen months (for distinct distinct firms of course). I've tried with this but it returns me an error "Select firm.firm_id,firm.name of firm,sum(calculations.amount),calculations.year from firm,calculations where calculations.firm_id=firm.firm_id and calclulations.month_ id in (forms!myForm!myTextBox.value);
VIEWS ON THIS POST

102

Posted on:

Thursday 18th October 2012
View Replies!

Can query return named parts and sum of shipped if table has no listing?

I am trying to create a query that will return 5 given part numbers and the quantity sold (via our ship date), given a Begin Date and an End Date. I have the query working so it accurately given the correct sum of sold items, but if no items were sold in this time, there is no listing. Is there a way to get the part number to list with a number shipped set to zero I've tried multiple variations of Nz() and IFF() functions yet it still only returns a list of the items if an item has been shipped. I simplified the code below. Any suggestions are greatly appreciated! Thank you in advance. Code: SELECT [Invoice Detail].PartNo, Inventory.ProductDesc, Nz(Sum(Nz([Invoice Detail].[QtyShipped],0)),0) AS Shipped FROM (Invoices INNER JOIN [Invoice Detail] ON Invoices.InvNo = [Invoice Detail].InvNo) INNER JOIN Inventory ON [Invoice Detail].PartNo = Inventory.PartNo WHERE ((([Invoice Detail].UnitCost)>0) AND ((Invoices.DateShip) Between [Begin Date] And [End Date])) GROUP BY [Invoice Detail].PartNo, Inventory.ProductDesc HAVING ((([Invoice Detail].PartNo)="A" Or ([Invoice Detail].PartNo)="B" Or ([Invoice Detail].PartNo)="C" Or ([Invoice Detail].PartNo)="D" Or ([Invoice Detail].PartNo)="E"));
VIEWS ON THIS POST

135

Posted on:

Monday 22nd October 2012
View Replies!

Calculate a running total of the last 7 days?

I have a table containing daily sales quantities by product. I want to calculate a running total of the last seven days of sales over a two year period. I'm not sure how to do the calculation in a query Please could someone give me some advice My table is structured as follows: Store.......Product....SalesDate.....SalesQ A............Cans........20110101......100 A............Cans........20110102......110 A............Cans........20110103......120 A............Cans........20110104......130 A............Cans........20110105......140 A............Cans........20110106......150 A............Cans........20110107......160 A............Cans........20110108......170 A............Cans........20110109......180 A............Cans........20110110......190 A............Cans........20110111......200 I am trying to get results which looks like: Store.......Product....SalesDate.....Last7DaysSale s A............Cans........20110107......910 A............Cans........20110108......980 A............Cans........20110109......1050 A............Cans........20110110......1120 A............Cans........20110111......1190 I am using Access 2003.
VIEWS ON THIS POST

70

Posted on:

Monday 22nd October 2012
View Replies!

Want query with running count based on group

Here's my scenario. A table with applications, a table with developers. One-To-Many relationship. One application could have 1 or 20 developers (no limit, twenty is the current max). Client requires a report with all developers in their own column. My first thought was to use Access's cross-tab, but it creates too many columns and errors out. Next thought was to create a query that listed the application, developer, and a count. Then to create a query with a case statement and 30 columns across (since 20 is the current max after 6 years of running think I'm "fairly safe" with increasing it by 50%). so something like this: App Developer Position 1 bob 1 1 Sue 2 1 Rick 3 2 Rick 1 3 Frank 1 3 Maria 2 ... and so on Then use a bunch of case statements based on the position. I can't find a way to do the running count based on app. So I'm looking for suggestions on how to do this or a better way to write this miserable query.
VIEWS ON THIS POST

134

Posted on:

Monday 22nd October 2012
View Replies!

Fiscal year running sum on daily basis

Dear all, I have few years data on daily basis and I want to create a query which should give me daily running totals based on fiscal year starting from July-1. Running totals should reset after each 30th june next year. I have tried using Dsum function but it gives me only correct running sum upto month end then starts adding previous month day 1st to present month day 1st and so on. please help.
VIEWS ON THIS POST

84

Posted on:

Monday 22nd October 2012
View Replies!

I need to show the sum value on the textbox

, I have a table which is called "clients_data". It contains the following information: Id|Name|Surname|Mobile|Expenses|Date| I can generate reports based on Names, Surnames, Mobiles, filters on Expenses and filters on Date which looks like this: Id|Name|Surname|Mobile|Expenses|Date| 1 |aa |zzz |111 |50$ |18-Jan-09 2 |bb |xxx |222 |30$ |19-Jan-09 3 |cc |yyy |333 |40$ |20-Jan-09 What I exactly need to do is that when I generate this report I need also the sum of Expenses displayed in a Textbox (in this case 120$). Could someone help me to resolve this problem
VIEWS ON THIS POST

139

Posted on:

Monday 22nd October 2012
View Replies!

Total (sum) with subtraction

designing a database for Uni and I have been asked to make it around a training company. It requires to hold a maximum number of places per course. During booking it is required to show the amount of places still available. Each booking can book more than 1 place (allocated places). I had tried using an update query to calculate available places [Schedule].[Max Places]-[Booking].[ocated Places] but my formula only calculates on the last booking. I need to total up all bookings for each course and then subtract it from the max places available. I can total up the courses and total places allocated in a query but cant use this data in the update query mentioned above. Any ideas
VIEWS ON THIS POST

63

Posted on:

Monday 22nd October 2012
View Replies!

Weekly summary of tasks

I am just starting to use Access and have set up many simple forms, datasheets, reports, summaries, etc. I have not worked with macros, etc. and need help for this project. I have set up a task table F1 - DateOfCall - date - now() F2 - Client - text F3 - DateCompleted - date F4 - DueDate - date F5 - problemDescription - memo F6 - Resolution - memo I need to run a query and/or report every week summarizing what has been done the past week. Would also like to view previous weeks. I need help. How do I go about doing this.
VIEWS ON THIS POST

60

Posted on:

Monday 22nd October 2012
View Replies!

Sorting results problem...

Working with Access 97 (willing to try your ideas even if your not sure! ) I have a query that calculates the distance between a Zip code entered and a list of cities. What I need is to sort by closest cities to the Zip. Here is my code. I'll explain what I've tried below it. SELECT CommunityClasses.Community, (69.1*(CommunityClasses.Latitude-Zip.Lat)) AS X, ((69.1*(CommunityClasses.Longitude-Zip.Lng))*Zip.keviekev) AS Y, Sqr((X^2)+(Y^2)) AS Distance FROM CommunityClasses, Zip WHERE (((Zip.[Zip Code])=[Please enter a ZIP Code])); When I try to sort, it asks for a value for X and Y. If I don't ask to sort, it calculates the values as it should do. If I ask it to ORDER BY Sqr((X^2)+(Y^2)) it again asks for values of X and Y. Do I need to do ORDER BY .... then tell it WHERE for the ORDER BY
VIEWS ON THIS POST

88

Posted on:

Monday 22nd October 2012
View Replies!