Is there a way or what is the expression used if I want to do a running total in a query under a field called "Amount Donated". In addition, I would like to filter by selection using the DonorID as the primary key to show the total.
Thanks.
First off, I am not sure if this belongs in the queries forum but it seems most of the question is geared towards a table so I have posted it here.
At the moment I have a query that contains several fields that use nested IIf statements to determine which set of rules to apply. I was wondering if it is possible to somehow set the IIf variables up in a table and then for just to lookup the table in the query.
For example, one of my simpler IIf's looks like this...
Cost:
IIf([AType]='Skill',
IIf([StartDate]<#01/08/2004#,
IIf([AOP]>0,
([BasicRate]-[Frame])/[AOP]/100*[Proportion]),
([BasicRate]-[Frame])/[OP]/100*[Proportion]),
([BasicRate]-[Frame])/[OP]/100*[Proportion])
So I was thinking could I setup a table to show:
FieldName AType StartDate AOP Calculation
Cost Skill <#01/08/2004# >0 ([BasicRate]-[Frame])/[AOP]/100*[Proportion])
Cost <>Skill Is Not Null Null ([BasicRate]-[Frame])/[OP]/100*[Proportion])
and then somehow perform the calculation rather than just look it up.
The main reason is it would be so much easier to see what is being calculated rather than try and weed my way through nested IIf statements.
Can anyone shed any light on this or maybe even a better way to accomplish what I am after?
Hi all!
I'm new to this forum and could use some help on a simple Access app. I have attached the relations for the db. I need to collect some data to a subform as:
tblFunctionCodes.FunctionCode,
tblAccounts.AccountNumber,
Monday (hours),
Tuesday (hours),
Wednesday (Hours),
Thursday (hours),
Friday (hours),
Saturday (hours),
Sunday (hours)
Where I have the tblEmployees.EmployeeID, tblDates.DateWeek and tblDates.DateYear as variables.
The weekdays need to be an expression with my current design, which seems to work ok from Monday to Saturday, but when I throw in Sunday Access says the query is too complicated.
I have this query to get the data, but with no expressions (can't get it to work):
SELECT tblFunctionCodes.FunctionCode, tblAccounts.AccountNumber, tblRegistrations.Hours, tblDates.DateWeekDay
FROM tblFunctionCodes INNER JOIN (tblEmployees INNER JOIN (tblDates INNER JOIN (tblAccounts INNER JOIN tblRegistrations ON tblAccounts.AccountID=tblRegistrations.AccountID) ON tblDates.DateID=tblRegistrations.Date) ON tblEmployees.EmployeeID=tblRegistrations.EmployeeI D) ON tblFunctionCodes.FunctionCodeID=tblRegistrations.F unctionCodeID
WHERE (((tblRegistrations.EmployeeID)=[intEmployeeID]) AND ((tblDates.DateWeek)=[intWeek]) AND ((tblDates.DateYear)=[intYear]));
I've been trying to use that as a foundation for other queries but I just can't get it to work. Does any of your guys have any ideas? Maybe an INTERSECT query??
Anyway I'm out of ideas and very pressed to makes this work. Any ideas are welcome!
Thanks in advance, jon.
I'm trying to create a query which will retrieve holidays for a specific week, so it would allows me to enter WeekDesc (e.g. week 1) and Line (e.g. Line 1) and then retrieve some information relating to holidays. I have gotten this far already.
However, I've just realised however that for example, if I enter Week 1 and Line 1, it will only retrieve those records for which (in the Holidays Table) have Week Description as Week 1 (this other fields in the table are HolidayID (pk), PersonID (pk), StartDate (pk), EndDate and Approved (checkbox). However if the holiday runs for over a week and runs into Week 2, if i run the query for Week 2 and Line 1, it will not retrieve that holiday.
I want to be able to update stock levels by clicking a button on a form. The button executes code to run the updatequery. Details of Items are listed on a sales form. The fields include item code and quantity sold. Details of the items are stored in a table called 'Items' - fields include item code and quantity in stock. I want the clicking of the button to compare the item code in the form with the item codes in the Items table, and then reduce the quantity in stock by the quantity sold in the form.
Any help given would be much appreciated.
thanks
Hi there,
I have a list (listA) which has a record source of a query object (queryA).
The form (formA) which has listA in it, also has a text box for some of the fields requested in queryA. The goal is to provide a flexible filter for the records in the list, so the user can just type in 'K' in text box filter_name, and they will only see records matching the entry.
N0w, the scene is set! I can easily go into the query object, and in criteria type something like this:
Like "*" & [Forms]![formA]![filter_name] & "*"
This works! It flexible filters out the records matching the text box value..
But, if the record has NULL for the name, then it won't even match "**", which is the minimum criteria. So therefore I get exclusion of records, which I dont want.
So I thought perhaps there might be some control structure I can use in expressions... some if, else, end if statement? Does anyone know how to do this?
Alternatively I thought, I could write a function like this..
Function criteria(ByVal val As Variant) As Variant
If (IsNull(val) Or IsEmpty(val) Or val = "") Then
'do nothing
Else
criteria = "Like *" & val & "*"
End If
End Function
And then in the queryA object for criteria you just use:
=criteria(Forms!formA!filter_name)
But this doesn't seem to work.. and I dont know how to debug this...
Any ideas? I thought the function was a neat solution, but it seems not!
Thanks in advance!
Hiero
Alright, I've got all the pieces to this puzzle, I just can't fit them together properly. I have two tables, tblTickets and tblTicketNotes. They are in a one-to-many relationship, there can be multiple Notes attached to a single ticket. I am trying to set up a query that will pull the first (earliest by date) note for each individual Ticket. Here are the fields from each table that would be of concern here:
tbTickets
TicketID - Autonumber - Primary Key
Issue - Text - I will be using this later as a criteria to limit with, but not neccessary
tblTicketNotes
NoteID - Autonumber - Primary Key
TicketID - Long Integer - This is the foreign key of the relationship
DateStamp - Date/Time - This is the Note Date, I only want the first (earliest date) one
Content - Memo -This is the note information I want
So all I want is the Content of the first/earliest Note for each individual Ticket. I know this should be fairly easy, but I am at a loss.
I am trying, and failing quite spectacularly to create a single query that will contain multiple expressions on the same field.
Please see attached jpg for example.
SQL: SELECT Count(StockData.status) AS FaultyPCs
FROM StockData
HAVING (([StockData].[Status] In ("Waiting Repair") And [StockData].[DeviceType] In ("Thick PC")));
What I want to be able to do is then add additional expressions to filter and count in the same way for "Thin PC" "Thin Laptops" "Thick Laptops" and so on.
But when I add one of these expressions to the next column in the builder, it seems to apply itself to the expression to it's left, causing that one to now give a result of 0.
Is there a way of separating one from another or do I have to create a separate query for each expression and then use another query to pull them all together (as I have done in another situations - but this will involve creating probably 20 separate queries.
I want to have Access run a query, open a report, and print the report based on the ID number of the form that I have open. Is this possible?
The situation would be, I have a form that uses the the primary key as a record number. After I enter the data into the form, with the form open I want to add a button to open and print a report of the current record number.
Hello, I have the following criteria for my Query >=0.8*[Projects]![EstimatedHours]. This tells me when we've used 80% of our actual hours. How would I go about the criteria line if I wanted it to return above >= 50% hour use, but below 80%? Thanks
View 1 Replies View RelatedI have a query that is built up of 5 fields with only the 2 below being visible. I am wanting to be able to calculate the total price for each pole no i.e. Pole no 01-02 total price would be £1441.79, What is the best way to do this? Sample data is shown below
New Pole NoTotal Cost
01-02.....£437.08
01-02.....£476.37
01-02.....£36.21
01-02.....£70.27
01-02.....£51.56
01-02.....£370.30
01-1.....£437.08
01-1.....£476.37
01-1.....£36.21
01-10367.....£493.43
01-10367.....£70.15
01-10367.....£76.51
01-10367.....£102.67
01-10367.....£87.54
01-10367.....£36.21
01-10367.....£51.56
01-10367.....£39.54
01-11448.....£437.08
01-11448.....£463.25
01-11448.....£493.43
01-11448.....£476.37
01-11448.....£70.15
01-11448.....£76.51
In qryAddticket, I am trying to calculate two conditions (see Condition 1 & 2)However, I am having a problem on the first condition because it is adding 8 hours when it shouldn't. Can someone help to either fix my code or write a new one?
Condition 1: If the day_of_wk is 1, calculate time-reg_time/60, If the day_of_wk is 1 and time is less than 0, calculate time-reg_time/60+24
Condition 2: If the day_of_wk is 2, calculate time/60, If the day_of_wk is 2 and time is less than 0, calculate time/60+24
My code: pre: IIf([day_of_wk]="2",[time]/60,IIf([time]<0,([time]-[reg_time])/60+24,([time]-[reg_time])/60))
Thank you very much!
I need to calculate the total cost of the order and enter this into the "CostOfOrder" field which you can see in the picture...
http://i13.photobucket.com/albums/a298/steve01/untitled2.jpg
I need to read all the orderlines for an order into a record set and calculate the cost for each orderline. I need to use a loop in VBA to add them all together.
Here is the query for inclusion in VBA...
Dim StrSQL As String
StrSQL = "SELECT orderline.qty*sandwich.price"
StrSQL = StrSQL + "FROM sandwich INNER JOIN orderline ON
sandwich.sno=orderline.sno"
StrSQL = StrSQL + "WHERE (((orderline.orderno)="
StrSQL = StrSQL & orderno
StrSQL = StrSQL + "));"
Do Until recordSetData.EOF
Total = Total + recordSetData.Fields(0).Value
recordSetData.MoveNext
Loop
I think the Query is correct, but where do I put this in the form (or where do I actually put it as I don't know!) to make it work?
Thanks in advance!
Steve :)
Im building a database for a car dealership. what do i need to do to take how many times someones name comes up in a certain field in a table and show it as a number. I need to include this calculation in a subfrom on a form which displays "employee info" which is already stored. Basically i just need to take how many times there name shows in the salesman field and show it as a number.
thanks
please respond, URGENT!
Hello
Can any body advise me.
I am running a simple sum
sum ([SUBTOTAL]/1000)
it does not give me the results I am after for example:
8.32 /1000 = 0.0832 - This is the desired result
I am getting
8.32/1000 = 0
It does note help if I change the decimal places.
What am I missing?
Thanks
YearsOfService: DateDiff("d",[Employee]![SIV Date],Date())/365
First I'm calculating the current Years of Service. SIV Date = Hired Date
Then I would like to calculate the years of service as of 12/31/"Current Year", where the Current Year would not require updating every year and 12/31 would be constant.
I'm just not sure how to specify the Month/Day and use Current Year not hard coded. I could set up a user defined date for that half of the calc but my users are not really bright and allowing them to simply click a button is my only option there.
Ultimately I will compare the 2 years of Service numbers to determine if there will be a change in the level of vacation accrual an individual receives during the year. The only part I have issue with is coding that date. Thanks for the help.
Hi
I am struggling with something im sure is quite simple.
I have two tables - the first has employee name and each record represents 1 telephone call answered. The second table also has the employee name but this table has a field with calls answered.
What I need to do is crosscheck the calls answered by employee in both tables. For example:
Table 1 has 3 records for John. (This means 3 calls answered).
Table 2 has 1 record for John with a calls answered field which is populated with the number 4. (This means 4 calls answered).
What I need is a report with both these sets of data and a new field with shows the the difference between the two.
Can someone please help me.
Many Thanks
I have a query that needs a calculation field for a report.
This is my field in the query.
Gross: [TBLinvamt]-[TBLinvqty]*67-[TBLfrtamt]-[TBLocamt]
Here lies the problem
example: 3210.81 - 25 * 67 - 0.00 - 0.00
If the frtamt and ocamt fields are 0.00 then my Gross field does not display any value
But if i put 1000.00 for frtamt and 500.00 for ocamt then the gross field returns a value.
Help is greatly appreciated
Hi all
I have a query field doing a simple calculation: 874*(18,3/55,65*0,0592)
Access calculates this to: 17,0144948838454
Excel as well as my own calculator gets: 17,0144948787062
All table fields is defined as double.
If anyone can help me with an explanation as to why access doesn't seem to get this simple calculation right, I would be most thankful.
I have a query that ends with a final calculation, giving a typical answer of 0.033333333.
I have created an append query, which works but doesnt pull the result of the calculation through, just displays 0.
Any ideas?
Hi to all,
In my report, there is a calculated column called Cost that display as $13.13. The orginal value is 13.125. This column is used to calculate another field called leftover.
The expected result is $16.00 - $13.13 = $2.87
The actual result is $16.00 - $13.125 = $2.88. So there is a increase of $0.01.
The results in my report is run using a query. In the query, I set the format of the calculated field, Cost as $#,##0.00;($#,##0.00). How can I ensure that whatever it calculate is what's displayed? Meaning to say if it display 13.13, use it instead of using 13.125 to calculate the leftover value.
Thx!
hi, i have a query which retrieves data on products.
my problem is how to display a specific total i need.
there are three fields;
[itemprice],[itemlabour],[itemdiscount]
the item price and labour columns are in currency format, but the discount column allows the user to select a discount between 0 and 50%.
now in a form, when i want to calculate the total price to be paid i have a simple code like this:
[itemtotal] = ([itemprice]+[itemlabour])/[itemdiscount].column(1)
the idea being, when a discount is selected, there is a hidden column in the combo which contains a specific number to divide the total of [itemprice]+[itemlabour], thus giving a total.
now i tried to use this code within a query, but it doesnt work.
any ideas?
jared james
In my Employees Sub form I have a calculation called Gross, the
calculation works fine but I can not get the query to gather the amount
On my form I have a number of boxes that contain the results of a query. The query itself contains calculations that total items from a number of other forms. Going back to the form, their is a final box that then holds another total. this time it is the result of all the boxes totaled topgether. My problem is that if one of the queryshas no data i.e there is no information on that form then no total result is produced. Ive tried putting a default setting of 0, but that dosen't seem to work. Basically I'm trying to say that if any of the criteria contains no values then it should be 0. Does this make sense?
View 9 Replies View RelatedHello Everyone
Its been a while but I'm back (hope everyones fine) with a question that should be fairy simple
In a query
I have 2 Fields
1) Receipt Date - Formatted ##/##/####
2) Warrant Mths - Formatted as Number ie 3 (= 3 Months)
I would like to use the above [receipt date] + [Warranty Mths] to provide the expiry date of the warranty.
Hope that's clear enough
Thanks Aaron
First off, I want to thank ansentry for providing a solution to a problem I had (new record entry), and I apologize if reverse engineering the form you wrote for my own usage is considered bad form.
Ok, here's my issue. I'm designing a database that keeps track of materials mined in an MMO (quit laughing ) in this fashion:
pick out a selected, user-inputted date rangepull all records that fall within the date rangeadd up the sum total for ALL users that submitted any minerals in the date range per mineral typeadd up the total for each mineral type for each person during the range (seperate from the total for everybody)run a calculation on each type for each person in the date range against the total of everyone, and output a percentage of what they mined vs. total
Now, I'm pretty sure that I have to do this via a query or two. Or three or four, however many it takes. What I have little to no clue on is how do actually program the query(ies) itself to perform these actions.
Was wondering if someone could give me a few pointers/tips to point me in the right direction. Also, if you want to take a look at the DB, (and if you don't mind) post your e-mail address and I'll mail you a copy of what I have so far as it might clarify any questions.