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, 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 :)
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
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.
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.
hi - i have tried search for the solution to my query - but to no avail, so here goes:
I have a query which is based on more than one table. In the query, I have specified a calculation, eg. Final Price: SUM([Sale Price] - [Discount])
From this query I have a form, just showing everything. Details are put into the form, and viola, they appear in the query if checked. However, they do not appear in the table
My assumption why this is not happening was because the Final Price is no longer "record source"d from the original table. How can I combat this so that it does appear in the table?
Thank you (sorry if it is easy - but i dont have a clue!)
Hi, All:
I have been struggling for this question for a long time.
I have a total query coming from two tables. This query has following field:
productline
device
component
The table has more field. One of them is status for component field.
My application is that there are many productline. Under each productline, there are many devices. Under each device, there are many components. Each component has one of 4 statuses. The status is text value, 'Yes', 'No', 'UR', and blank.
In the form, I need to use continuous form to display each device with totalcomponent (I use Count of component), percentage of status1 based on totalcomponent, percentage of status2, etc.
My question is:
I tried to use Count(IIF([status] = 'Yes', 1, 0)) to get percentage. But [status] = 'Yes' seems not right because I got a count of all statuses, the same result of CountOfComponent
Really need help
Thank you much
awu10
Hi all, I have a problem in returning values from 2 different tables because they are not related. Let me explain:
I'm trying to do "Payment Due" query by substracting the amount in the "Cost" table with the amount in the "Payment" table ([CostAmount]-[PaymentAmount]). However, since no payment has been made, the table contains no related record.
At first I thought the problem lies in null values the table return hence I tried to use NZ function to convert null to zeros. Then I realised that no values has been returned from the table due to no related record available.
Can anyone help me?
Cheers
I have a query which calculates and original amount, looks to see if there is an increase or a decrease in the amount, and if it's an increase, it adds the original amount to the amount of the increase, and gives a "revised" amount. If it's a decrease, it subracts the original amount from the amount of the decrease, and gives a "revised" amount as well. This works fine for the line item, as long as there's only one Revision to the line item.
What I need it to do, and I'm not sure if I should still be doing this at a query stage, or if it should be coded, is, if there is more than one revision to the line item, it needs to look at the new "revised" amount (from the first revision), and then add the increase or subtract the decrease from the revised amount, and give a new "revised" amount, instead of taking it from the original amount.
example of what it's doing now.
Rev #1 Orginal line item - $4,300 Increase - $500 New Revised amount - $4,800
Rev #2 Original line item - $4,300 Increase - $50 New Revised amount - $4,350
What it should be doing.
Rev #1 Orginal line item - $4,300 Increase - $500 New Revised amount - $4,800
Rev #2 Revised Line item - $4,800 Increase - $50 New Revised amount - $4,850
I need to have it look to see if the revision # is higher than 1, and then look at the revised amount, and do the increase or decrease at that time, and then give another "revised" amount.
Is this doable? And if so, any help would be greatly appreciated!
:confused:
Here is the Expression that I have in my query to calculate my "revised" amount. It looks at the original amount, and looks to see if it's an increase, and if it is, it adds it to the revision amount, and gives a new total for the line item, otherwise it sees that it's a decrease, and it subtracts the revision amount from the original, and gives an new total.
New Amount: IIf(tblRevisions![Increase?]=True,[revision Amount]+[Line Item Amount],[Line Item Amount]-[Revision Amount])
Hi ..
I have an issue I do not know how to tackel. I have a select query that selects from three tables to calculate the commission for each transaction. The query is working just fine.
My problem is that I need an additional function ... What if I need to charge a special commission for that specific trade ?
I need to ammend the commission based on a figure I input in a text box from the form view.
Example:
- Commission (calculated by the query) is 100.00
- Special Commission (which is a text box on the form) is 80.00
Then Commission field = 80.00
PLEASE HELP .. Please let me know if you have any other suggestion in tackling this problem. Thanks
Query
Commission: IIf([Shares]![Currency]="USD" Or [Shares]![Currency]="CAD",IIf([Blotter]![Quantity]*[Clients]![USD_2]<[Clients]![USD_1],[Clients]![USD_1],[Blotter]![Quantity]*[Clients]![USD_2]),IIf([Shares]![Currency]="GBp" Or [Shares]![Currency]="EUR",IIf([Subtotal]*[Clients]![EUR_GBP_2]<[Clients]![EUR_GBP_1],[Clients]![EUR_GBP_1],[Subtotal]*[Clients]![EUR_GBP_2]),IIf([Subtotal]*[Clients]![HKD_JPY_2]<[Subtotal]*[Clients]![HKD_JPY_1],[Subtotal]*[Clients]![HKD_JPY_1],[Subtotal]*[Clients]![HKD_JPY_2])))
Hi all,
I have a problem creating a query calculating a running total based on the last 12 months grouped by product.
The table "Data" (attached):
Product no. / Period / Gross Rev / Clients
810 / 01-01-2006 / 1.000 / 10
810 / 01-02-2006 / 1.000 / 10
...
810 / 01-01-2007 / 500 / 10
990 / 01-01-2006 / 1.000 / 10
990 / 01-02-2006 / 1.000 / 10
From this table I want to create a running total so that the running gross rev of product 810 MAR07 is based on APR06-MAR07 data. The same applies to the number of clients. Is this possible?
I have tried using the Dsum function but I have only managed to calculate a total running (not based on the last 12 months). Can somebody help me here?
:confused:
Hi, I am wondering If I can Sum the value of rows in one field of my Query Table and Inserting that Calculated value into the bottom of the summed Row in the VBA or through Query Design View.
Please let me know, Thank YOU!
Below I have attached the picture of the data that Im trying to sum and insert into a new row that Hopefully can be created through MS access Query.
Part of a database I'm developing stores details of events. Each event is funded from a variety of different sources.
There are three tables involved in making this work:
tblEvents - which stores the main event details
tblFundingOptions - which stores all the possible funding sources
tblEventFunding - which links the two together.
I have created a form to access all of this, with the information from tblEvents at the top and the Funding details on a subform.
I have created a query based on tblEventFunding which adds up the total amount of funding allocated to each event. I want to display this total on the form I've developed.
Can I do this without creating a second subform to pull the details in from the Query? I've tried creating a text box and linking it to the appropriate query, but it just displays #Name! - presumably because it doesn't know which record from the Query it should display, and I don't know how to tell it!!
Is what I'm trying to do possible, or do I need another subform?
Many thank in advance,
Gary