This is my query:- SELECT DISTINCTROW Customer.custName, Sum(Invoice.invAmount) AS SumOfinvAmount, Sum(Payment.payAmount) AS SumOfpayAmount FROM (Customer LEFT JOIN Invoice ON Customer.custID = Invoice.invCustID) LEFT JOIN Payment ON Customer.custID = Payment.payCustID GROUP BY Customer.custName, Customer.custID;
Result:- custName SumOfinvAmount SumOfpayAmount blake $20.00 julie $50.00 kate $375.00 $290.00 steven $200.00
The resulting SQL query returns incorrect invoice and payment totals for Kate. The Sum() function has duplicated. Can anyone tell me how I would change my query to fix this problem?
Multiple IF functions in a form. I have a button that on click will print a report if certain fields in the form have data in them. If not, a msgbox pops up to let the user know that data must be filled and then sets focus on whichever field needs data in the form. There are many IF statements, and they all work fine until I get to a certain one then the remaining IF does not continue.
Here is some code:
If IsNull([Alert]) Then MsgBox "An alert time is required." _ , vbCritical, "Data Required" Me.Text591.SetFocus Exit Sub Else If IsNull([Enroute]) Then
[Code] .....
The next IF argument will bring up the msgbox when there is no data, thats great. However, when the user fills in the field and continues with the button click to print the report, the button does nothing.
Here is the next IF, the one with the problem:
If IsNull([Combo608]) Then If IsNull([Combo620]) Then MsgBox "You must select either a planned event or an emergency event." _ , vbCritical, "Information" Me.Combo608.SetFocus Exit Sub Else
How to get it to work, to continue with the remaining IF arguments after this one (there are several more)?
Or are there a better way to code this and not have the many IF arguments?
Any way to use multiple count functions in a query with their own individual filters without affect the others?
For example:
SELECT [E&I Table].System, [E&I Table].DeleteRecord, Count([E&I Table].[Status#1]) AS [CountOfStatus#1], Count([E&I Table].[Status#2]) AS [CountOfStatus#2] FROM [E&I Table] WHERE ((([E&I Table].[Status#1]) Like "ITR Rcv'd by QA" Or ([E&I Table].[Status#1]) Like "Completed" Or ([E&I Table].[Status#1]) Like "Ready for T/O" Or ([E&I Table].[Status#1]) Like "Reviewed by JVV") AND (([E&I Table].[Status#2]) Like "ITR Rcv'd by QA" Or ([E&I Table].[Status#2]) Like "Completed" Or ([E&I Table].[Status#2]) Like "Ready for T/O" Or ([E&I Table].[Status#2]) Like "Reviewed by JVV")) GROUP BY [E&I Table].System, [E&I Table].DeleteRecord;
I am trying to count in each column of the E&I table with criteria using WHERE but the problem is when you have more than 1 WHERE it affects the other columns as well... tried a bunch of different ways and I am now having to create seperate Queries than combine them using another Query...
and i would like that if Text0 is "test.exe" i want to return "exe", however, with the above i am getting ".exe" ... can someone tell me what i am doing wrong please ?
I currently have a text box that is calculated off of 6 other text boxes. I want the one field that is calculated to update any time I change any of the other fields. I can do this by putting the same calculation in each ones afterupdate event. Below is one section of my code for the arithmetic, and I want to perform that code many times. Can I use a function to do this, and if so some help would be nice? thanks
In access, I'm trying to create a database to track material that has an expiration date of one year from the appointment letter date. I'm not sure what I need to do here. Here's what I have: I have a table that has fields for the individual (UNIT), the appointment letter date (Appointment Letter Date), and a True/False field (Out of Date) that I want to make True when the current date is greater than the (Appointment letter date + 365 days). Is this possible through Access tables and/or queries or will I have to incorporate VB? I am an extreme novice when it comes to expressions and query building in Access.
I have build a database which work perfectly on most computers. But one or two for the computer are error with the Left, Right and Date SQL function. Is it simplely miss for the install or it a problem with my code?
I will firstly give you the low down on the business that I am doing the database for. It is a Car Restoration Business, where we deal with vintage/classic/muscle cars and restore them to the former glory.
I do not know Microsoft Access very well, I have attached a file with what I think is a databae :) I could be wrong. The main function of this database is to search for suppliers for a specific car part for a specific car.
So if I want to find a doorhandle for a 1970 Ford Mustang, I need the suppliers pertaining to those criterias to come up.
I have developed a MS Access database which uses several functions such as DateDiff and inStr. When I use these function on my machine the database runs fines and the values are calculated.
However the database I have developed is not for my computer and when run on this one computer produce some errors (The date function is mentioned). I believe this is some sort of security issue but I cannot be sure.
Both computers run the same version of Office. Does anyone have any ideas.
I have developed a MS Access database which uses several functions such as DateDiff and inStr. When I use these function on my machine the database runs fines and the values are calculated.
However the database I have developed is not for my computer and when run on this one computer produce some errors (The date function is mentioned). I believe this is some sort of security issue stopping her running the functions.
Both computers run the same version of Access. Does anyone have any ideas.
I need some help with a function inserted into a query.
I created a function to convert numbers into text. I then created a query to pull some fields from a table. the last field of the query, I inserted the function using the build feature. I was able to see the function in the list of custom functions for the query.
However, when I run the query I get an error message of undefined function - name-. Can I use my function there? or is there some other way to do so.
Hi Im trying to run two count functions on one column in sql (access 2003). I need to return two new column, pass and fail. something like this; select count(grade) as pass, where grade>=40 and select count (grade) as fail, where grade<= 39.
my table is student_ID, module and grade:
student_ID Module Grade 0012 history 59 0034 history 34 0045 maths 78 0031 maths 45 0046 spanish 66
I have looked and looked at this expression line and cannot find what is wrong.
Repaired: Sum(IIf(work!status=4 And work!code=11 And work![tin date] Between DateSerial(Year(date(forms![enterprise report card].[combo31])),Month(date(forms![enterprise report card].[combo31])),1) And DateSerial(Year(date(forms![enterprise report card].[combo31])),Month(date(forms![enterprise report card].[combo31]))+1,0),1,0))
This gives me an error "Expression entered has a function containing the wrong number of arguements"
However this one: Repaired: Sum(IIf(work!status=4 And work!code=11 And work![tin date] Between DateSerial(Year(Date()),Month(Date()),1) And DateSerial(Year(Date()),Month(Date())+1,0),1,0)) works fine... The only difference is I inserted forms![enterprise report card].[combo31] inside of the Date() function.
I am doing some work on a loan calculator which I have done successfully in Excel. I now wish to do this with an Access form (A2K). Can anyone please confirm that the Financial Functions (ie CUMPRINC etc) that are available in Excel can be used in Access. The Access help menu calls them Worksheet Functions and I can't see any reference to the above function in this forum. I have entered it in a control and get a #name? error. I dont know if I have made a syntax error or that Access doesn't recognise it.The following is the formula I have used =CUMPrinc([rate]/[freq],[nper],[amount],1,[nper],0).
I have not had the same problems with the following formula =PPmt([rate]/[freq],1,[nper],[amount],0)
I`ve got a problem with access. I Have data in several columns like:
Date Value 1 Value 2 Value 3 Value 4 1-1-01 12 10 11 9 2-1.01 14 7 16 11 etc. Now I would like to add two columns that determine the min and max value of a row. Like below:
Date Value 1 Value 2 Value 3 Value 4 Min_value Max_value 1-1-01 12 10 11 9 9 12 2-1.01 14 7 16 11 7 16
Is this possible with the standaard min function? or how else do i do this?
I need to find the latest of several date fields in a SINGLE record of a query. The Max function seems only to work for a single field across several records in a query. However the worksheet function Max suggests I can use it for in-record fields but it does not work in queries. It is called a Worksheet Function but what is an Access worksheet? I know what it is in Excel but not Access. Neither Help nor two thick reference books even mention worksheets.
hi, what im trying to do is use the code below, coupled with a append query, to make the value of the 'lag' variable go into the feild in 'tblplayer' 'Lowes t_age_group"
can anyone see what im doing wrong?
thanks any help would be much apreciated
Nick
Public Function fnlag()
Dim cutof As Date Dim age As String Dim Birthdate As Date Dim age1 As String Dim lag As String
cutof = DLookup("[cutoff]", "[tblseason]", "[tblseason].[is_current] = -1") Birthdate = DLookup("[Birthdate]", "[tblplayer]") lag = 1 lag = DLookup("[Lowest_age_group]", "[tblplayer]")
age = DateDiff("yyyy", Birthdate, cutof, vbMonday, vbFirstJan1) 'MsgBox (age) lag = age + 1
Hope all is well! I need to create a ad-hoc report for the rental items table . For example the table has an Expired Date. What kind of Date function I can use, so everyday, I can query out and show report for those rental that have 1 week prior to expiring date? Please help. Thanks so much!
For example : The Rental table has 3 records: Item 1, Expired Date on 12/13/07 Item 2, Expired Date on 12/22/07 Item 3, Expired Date on 12/29/07
If I run the report on 12/06/07, then the report should show Item 1 since it is expired in 1 week.
1/ First of all, how do I view in the Forums all of my own threads that I created? (silly question! )
2/ In MS Access 2000, what Date function/calculation expression used to count number of days between 2 date EXCLUDING weekends or even EXCLUDING Friday, Saturday, and Sunday? (since the workers work from Mon-Thu weekly). For example:
Date PO enter field: 02/01/08 Date PO sent out: 02/07/08
I would like the Number of Days will return 3 instead of 6 (since we don't count Feb 2, Feb 3, and Feb 4 since they are Friday, Saturday and Sunday)
3/ If I have a Date field called "Rental Expired Date", what is the Date function that I can use to give me the report of any given day of all the items that will be expired 2 weeks from the day I run the report? Better yet, our office use Novel Group Wise email application, is there a way to send this report directly to a particular person Inbox everyday?
Not exactly sure if we has anything to do with sql server so sorry if it isnt strictly relevant. I have a database on sql server and am using an access data project as the front end. Have finsihed this and is working fine till I try and use it on another computer on the network. It still connects to the database fine (well on most machines on the network not sure why not on the others) but the problem is access functions in my reports and forms seem to have stopped working and show up as ?#name Examples of the kind of functions I am talking about are
=Format$([DateFrom],"Long Date")
=[SumOfMinutes]60 & Format([SumOfMinutes] Mod 60,":00")
i'm trying to use a switch function in a query but i'm unfamiliar with the format a bit. here is what i'm trying to do...
switch([Radios]![Warranty] and [Radios]![HousingWarranty]=true,[Radios]![Change front housing]*(25/60*28), [[Radios]![Warranty] And [Radios]![PortableHousingWarranty]=true], [Radios]![MaterialsUsedPrice1])
basically first statement is condition second is execution. I need to add more but i'm getting an error from this much...if I was doing this in vb.net I would just do if, ifelse, ifelse, ifelse...but...
