General :: Subtracting Totals In A Business Database?
Jan 18, 2014
I have set up this database here for my new sole trader business. In it I enter data of my purchases and my gross sales. I have generated 2 queries both based on the same tables. 1 calculates the purchases and the other the sales.
I want to be able to subtract the purchases from the sales so that I can see my profit at any point in time.
The complication is brought on I think from the fact that I am using one table for both Purchasing and Sales. However, I use a filtering method to separate the two.
View Replies
ADVERTISEMENT
Sep 4, 2015
I have 3 reports
Report 1. is attendance from January to June
Report 2. is from June to December
Report 3. is from January to December
The are all run from the same query with different Between Dates.
The scenario is that Paul attended the support group from Jan to June so in the Jan to June report he is counted once. The query removes duplicate values
Paul attended the same support group from June to December so in the June to December report he is counted once. The query removes duplicate values.
This shows the Paul attended he support group from Jan to December so in the June to December report he is counted once. The query removes duplicate values, if you removed the duplicates from this query he would be counted twice.
Not all people attend the same group for 12 months but if they do the are a duplicate and Paul is a duplicate. That is ok because we need to know now many duplicates there are for the year.
If I print all 3 reports with the duplicates remove query and you looked at the totals it would show (Example) 30 attendees in the 1st report and 30 attendees in the 2nd report but in the 3rd report it would show 59 not 60 which is the total of reports 1 & 2. we need to report these figures as 1st 6 months with 30 and the second 6 months with 30 but the whole year would be with 59 and 1 duplicate.
The way it is now I have to run all 3 report and do the math by hand this way.
Is there a way on the to do the math with code calculations on the full year report with the figures from the 2, 6 month reports to automatically show total attendance for the year 59 with 1 duplicate?
View 1 Replies
View Related
Jan 18, 2006
I am setting up a Database for my company, we want to do it in our local branch first then hopefully we can role the idea out to the entire company. I have started the database already just setting up the tables I need, I think my Design is slightly off though. I am asking for the forums help if that is okay, okay here is the setup.
Areas Needed:
Employee
Projects
Clients
Employee Area:
Date of Hire
fname
lname
salary
title
timesheets
etc...
Project Area:
project #
project name
client
budget info
etc...
Client Area:
client name
addy
contact info
etc....
Project area is going to be the main hub for everthing else, the project number is the key to the entire database. client will be attached to a project via project number, employees timesheets tracked by project number, invoices printed by project number. timesheets will generate the invoices to the client. You guys get the idea, I hope that some of you guys can help me in the spare time you have.
Again, i am looking for Design assistance, i will post a sketch of my design in a couple.
View 2 Replies
View Related
Jul 18, 2013
I am trying to add two columns with multiple records in them together using VBA.
I have one column named "Quantity" and that has 5 records going down
Column1(Row1) = 2
Column1(Row2) = 3
Column1(Row3) = 1
Column1(Row4) = 2
Column1(Row5) = 2
I have another column name "Quantity2" and that has another 5 records going down
Column2(Row1) = 1
Column2(Row2) = 4
Column2(Row3) = 2
Column2(Row4) = 2
Column2(Row5) = 2
I need a code to simply add them all together together using VBA and put the value back in a text box.
So the Value in the text box would be = 21
View 9 Replies
View Related
Aug 5, 2015
I have a table that has a column of Equipment Numbers (e.g. CN330) and another column with corresponding costs. The Equipment Numbers are not unique. A number may appear many times in the column and the cost may be different each time. (The source data for this is an Excel spreadsheet provided by another department).
I need the totals for each unique Equipment Number but not in a "messy" format like subtotals. I need to use it as a lookup for another part of my database so want just two columns; unique Equipment Numbers in the first column and the total cost for each in the next column.
I have the unique Equipment Numbers in the first column of a new table but cant figure out how to get the total costs.
View 2 Replies
View Related
Feb 16, 2005
Hello Helpers..
I have a table imported from excel with a large data with columns like..
Level3-
Category
April-03
May-03
June-03
till.......mar-04..
Level3 category April-03 May-03 June-03
Trading Income 10000 13300 15500
Trading expenditure 5000 3000 4000
Investment Income Income 25000 28000 23000
I want to design a form where i select the Level,category and period to display the respective values of income or expenditure..
no:2
How can i calculate the year to date totals based on the table above.....
Thanks a ton.... :confused
View 5 Replies
View Related
Feb 16, 2014
Is it possible to set up a view within an access app to show a number of different "grand totals", related to one or more tables. For example, I'd like to display the total value of all orders, the total number of orders and the total number of items ordered, on a single view. In this case, all the information comes from one table - Orders, which contains fields - Total Cost and Quantity. I've tried to create a Summary view, but that requires me to use the "Group By" function, which I don't want here, so I tried to do it with queries, but it seems to involve multiple queries, which then means multiple views.
View 2 Replies
View Related
Oct 27, 2012
I am trying to obtain totals from two columns in the list box into text boxes on the main form, but my third argument is not working as expected.The source of one of the tex boxes is:
Code:
=DSum("Airtickets","T_Training_Participants.ProgrammeID=Me.lstParticipants")
I want to sum only amounts of the records that equal or belong to a selected programme (ProgrammeID) in the bigger list box above.
View 2 Replies
View Related
Sep 4, 2007
Hey all! This is my first post. Been searching through the net all day trying to find a solution to this problem. Basically i have a table that looks like this (regular text is what i have and bolded text is what I need:Name Date Qty MOBrad 12/12/2007 23323 4423John 12/11/2007 3445 4432 John 12/11/2007 344 4432 John 12/11/2007 45 4432 John 12/11/2007 44 4432 John 12/11/2007 3445 4432 Grand Total: (Qty)And then I'd like to be able to carry this over and display a grand total at the bottom of every page of a report that I would need to generate. Our company produces forms and we sometimes have 60 - 70 people working on a single job. We want to see their hours individually but we would also like to see a grand sum of all their hours. If someone could help with this or needs more info let me know. Thanks for all your help!
View 14 Replies
View Related
Mar 19, 2008
Good Afternoon All,
I am in need of you help in calculating this. Basically, I don't want the code to count zero. For example, today is 3/19/2008 - Date() should equal = 1 and not zero..
Here's some more examples:[lastfollowup] being 3/18/08
Date() - [LastFollowup] = 2
Here's my code:
DaysOpen: ((DateDiff("d",[LastFollowup],Date(),2))-(Int(DateDiff("d",[LastFollowup],Date(),2)/7))*2)+IIf(Weekday(Date(),2)>5,5-Weekday(Date(),2),0)+IIf(Weekday([LastFollowup],2)=6,1,0)-IIf(Weekday([LastFollowup],2)>Weekday(Date(),2),2,0)
Thank you,
John
View 1 Replies
View Related
Feb 22, 2006
I am trying to calualate the business from an Order Date. The Business Days are Monday thru Friday excluding Holidays. It is for a form that will Highlight (conditional Format) Order over five business days old that have not been completed. Has anyone done this before?
View 2 Replies
View Related
Mar 22, 2006
Hi,
Is there any access formula that can calculate the net business days between two dates.
Regards,
Jatz
View 2 Replies
View Related
Jan 24, 2007
Hi. I'm a freshmen currently in college and I'm taking business info systems right now... and well I'm just being paranoid. Anyway, i really hope this isn't at all too confusing or anything... but
For a hw assignment we where sopposed to like dl this database and just do random commands on it. Anyway, we're sopposed to save it at the end... and email the file to the professor who will grade it.
Anyway, I asked her how she would know if we actually did all the steps and shit... and how she could tell (esp if the directions where like... "1. filter this out." then "3. take out the filter."
she replied that access "saves" all the actions in a database in the document so she will be able to see all the actions that we took.
I did all the assignments, and I'm just being paranoid about my work right now... since it's the new semester and the teacher is SO NICE but very picky when it comes to grading.
My question is just to ask if anyone knows the "secret" command/function/button or whatever you do to basically do what the professor would do to grade my database / ie see that I did everything.
I just want to check to see all my work is good before I submit it to her.
Thank you so much for your time and reading this. :)
View 1 Replies
View Related
Nov 17, 2006
I want to use a business card scanner, and use the data for my Access 2000 order database. Most card scanners, can send data to Outlook, can you go from Outlook to Access? Any advise would be appreciate it . . . THANKS!:o
View 2 Replies
View Related
Oct 18, 2006
I am building a database for a Real Estate business.
I have 2 tables tblPurchasers and tblVendors.
I intend to write a query that will match the requirements of the purchasers to the properties for sales (from the vendors).
My issue is this (best illustrated via an example);
I run my Query on the 1st April and four properties are produced as being suitable for purchaser 'A'.
Purchaser 'A' immediately deems properties 1 and 2 unsuitable and views property 3 on the 6th April. Property 3 is subsequently deemed unsuitable as well.
I re-run my qury on the 10th April and five properties are produced as now being suitable for purchaser 'A'.
Purchaser 'A' has already dismissed properties 1,2 and 3 so i don't want to show him these again ....... how do I just ensure that properties 4 and 5 appear ?
Purchaser 'A' has already dismissed properties 1,2 and 3 so I don't want to show him these again ....... how do I just ensure that properties 4 and 5 appear ?
Something involving a yes/no box on a form would be the best method for me of implementing this (as it would fit in with the scematic of the database to date).
I imagine the properties (with a small photo) appearing and tick boxes headed 'Unsuitable', 'Viewed' etc be used to dismiss properties, which do then not re-appear.
MANY THANKS !
View 1 Replies
View Related
Oct 27, 2006
I need to calculate the difference between to datetime fields. However, i need to exclude weekends, holidays and non-business hours. I also need to return the result in hours, minutes, seconds.
can this be done. if so, does anyone know how to do this, or is there code that i can implement and save for future uses.
thx,
john
View 1 Replies
View Related
Dec 13, 2006
Hi,
I am trying to update a field on a form with a date that is +7 days from a date that is in another field, but the new date must only be a business day. Can anyone tell me how to do this?
Thanks
View 3 Replies
View Related
Jan 22, 2007
SELECT
[(2) MAX BATCH].ID_CASE,
[(2) MAX BATCH].MaxOfDT_BATCH,
NOLDBA_LOG_DISBURSEMENT_DETAIL.DT_DISBURSE
FROM
[(2) MAX BATCH] INNER JOIN NOLDBA_LOG_DISBURSEMENT_DETAIL ON
([(2) MAX BATCH].MaxOfDT_BATCH = NOLDBA_LOG_DISBURSEMENT_DETAIL.DT_BATCH) AND
([(2) MAX BATCH].ID_CASE = NOLDBA_LOG_DISBURSEMENT_DETAIL.ID_CASE);
Can someone tell me how to find the ID_CASE's where
NOLDBA_LOG_DISBURSEMENT_DETAIL.DT_DISBURSE is less
than or equal to 2 business days (M-F) of the MaxofDT_BATCH in this query?
View 1 Replies
View Related
Aug 21, 2007
I have a query that calculates business days. However if my end date is missing the query has error codes because the date its looking for is null. Can show me were to put the nz function.
here's the module for the business days
Function Work_Days(BegDate As Variant, EndDate As Variant) As Integer
' "Calculating the workdays between Dates"
' Note that this function does account for holidays.
Dim WholeWeeks As Variant
Dim DateCnt As Variant
Dim EndDays As Integer
BegDate = DateValue(BegDate)
EndDate = DateValue(EndDate)
DateCnt = BegDate
EndDays = 0
Do While DateCnt <= EndDate
If Not IsNull(DLookup("HoliDate", "tblHolidays", "[HoliDate]=#" & DateCnt & "#")) Then
EndDays = EndDays - 1
End If
If Format(DateCnt, "ddd") <> "Sun" And _
Format(DateCnt, "ddd") <> "Sat" Then
EndDays = EndDays + 1
End If
DateCnt = DateAdd("d", 1, DateCnt)
Loop
Work_Days = EndDays
End Function
and here's the query
SELECT [Reques_ID], Work_days([Date_Received],[To_Director]-1) AS Turnaround_To_Director,
Work_days([To_Director],[From_Director]-1) AS Turnaround_From_Director, Work_days([To_VP],[From_VP]-1) AS Turnaround_From_VP, Work_days([Position_Number_Requested],[Position_Number_Recieved]-1) AS Turnaround_For_Position_Number, Work_days([Date_Received],[Approval_to_mgr]-1) AS Turnaround_EPS, Work_days([Date_Received],[JOIS_Posted_Date]-1) AS Turnaround_To_Posting, Work_days([Approval_to_mgr],[JOIS_Package_Return_Date]-1) AS Turnaround_For_Package_Return
INTO tbl_turnaround
FROM Artifact;
View 7 Replies
View Related
Feb 10, 2005
I have a date range of 1/01/2005 to 12/25/2005 for example. I need a formula to return the number of days between these dates excluding weekends. Basically I need to know how many business days are between these dates. Thanks for the help.
View 1 Replies
View Related
Jul 22, 2006
I have seen a few modules that let you count how many business days between two dates. What I am looking for is how to count 10 business days from another field.
Say for example I have a txt box that auto populates =Date(). I want to have another txt box that will count 10 business days ahead and put that date in the box.
This is so I can have the db show a due date for reports sent in.
Anyone seen/done/have code for this?
View 2 Replies
View Related
Jul 28, 2015
I have a mdb file that our company has been using for years and would like to continue. But I need to update our business name and address within the file so when I do reports and invoices it reflects the correct information. I am using Access 2013.
View 14 Replies
View Related
Aug 15, 2007
Is there an Add in for Microsoft access that will using a gui based method, run queries, set up automated reporting (task Scheduler) in an easy to administer method. Quest Toad has a new add in Toad for Data Analysis. I am looking for something similar for access. Right now I am doing this manually via creating macros, etc. But there really should be an easier way.
Thanks
View 1 Replies
View Related
Dec 8, 2007
Hello all,
I have 2 tables:
- Employee
- PackageTracker (Contains PackageNumber, date received, date sent, status)
I would like to query:
- which employee has the package
- How long have we had the package (calculate only business days)
- What is the status
I would like the things mentioned above to be ran in one query.
I am stuck with the calculations. I found a macro code to calculate the business days but I am stuck.
Please advise.
Thank you,
John
View 14 Replies
View Related
Sep 27, 2005
Hi dont know how im gonna explain this but here goes,
I have a table of businesses and each business has vacancies and consultancy time under it through related tables, so i have
Businesses
|
------------------
|.......................|
Vacancies........Consultancy
I want to be able to display all the Vacancies for one company in a form and all the consultancy in one form, i know i could do it though querys but i have 600 companies and that would be ridiculous.
Is there any way i can do it by selecting a business and been able to see all records for that business.
Thanks for your help
View 2 Replies
View Related
Sep 17, 2004
Is there a function in Access that will allow you to add business days to a date? I want to pull a date with a query and then add 5 business days to it to display in a report. Anyone know if this is possible?
Thanks.
View 1 Replies
View Related
