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])
I have a find duplicates query with the following SQL:
Code: SELECT tblData.Vendor, tblData.[Loccurramount EUE], tblData.Last4, tblData.ID, tblData.Line, tblData.CoCd, tblData.[Document record number], tblData.PurchDoc, tblData.Reference, tblData.Curr, tblData.[Entry dte], tblData.Status, tblData.Version, tblData.Outcome FROM tblData WHERE (((tblData.Vendor) In (SELECT [Vendor] FROM [tblData] As Tmp GROUP BY [Vendor],[Loccurramount EUE],[Last4] HAVING Count(*)>1 And [Loccurramount EUE] = [tblData].[Loccurramount EUE] And [Last4] = [tblData].[Last4]))) ORDER BY tblData.Vendor, tblData.[Loccurramount EUE], tblData.Last4;
This works fine however I want to add another clause to the WHERE and I'm not sure how. At the moment the query highlights duplicates where the Vendor, Loccurramount EUE and Last4 match. I want to further restrict it so that it only finds duplicates where the Vendor, Loccurramount EUE and Last4 match BUT the number shown in version Does Not Match
So if two records have the same details for Vendor, Loccurramount EUE and Last4 and also have the same Version number then they don't show in the result.
Is it possible to amend this hidden table? It is for my ODBC links, I would like to amend (enmass) where it is linked to and the table name. If I could amend this table, it would only take seconds.
Is there a way whereby I, or another user, can amend the ‘TOP’ value of a query via a Combo or Text box entry on a form? I have searched this Forum for a solution to this very problem but have had no success. I understand how to achieve this via the queries ‘SQL’ view but cant figure out a way to do it by the way that I have already described!!
Your advice or a ‘pointer’ in the right direction would be extremely well received.
I have a form that has four text boxes populated by a combo selection on a prior form; on the form in question, these four text boxes are locked, however there is a command button to allow users to amend the information in these boxes - all works fine, however the changes they make are only applied to the current record. On occasion, it would be useful if these changes were able to be sent back to the source table to amend it.
Giving the option would be easy enough with a Yes/No message box, but is there a way with some code to amend the source record of the combo?
I now want to change where it says "Qry BM to Management" to "Qry Tom to Management",
Example with Command Query:
Again Textbox Contains Tom
Query has been copied but now i want to change the following SQL string from where it says Bhavins Table to Toms Table
INSERT INTO [Management Table] (Postcode) SELECT [Bhavins Table].Postcode FROM [Bhavins Table];
Im not sure if this is possible but i would like to know as adding a new user to my Database takes me about 10-15 minutes but I know what im doing (copy paste change specific parts of Queires and points of forms etc ) but i need to make it easy for management to add new Users....
I'm trying to create a function to update and amend records in a table.
The update part works and updates existing records with new data but I'm getting an error with the insert part.
Run time error 3078 The Microsoft Office Access database engine cannot find the input table or query 'FALSE'. Make sure it exists and that its name is spelled correctly.
Nothing called 'FALSE' so not sure what that means?
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
I 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
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))
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.
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.
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.
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.
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.
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.
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.
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.
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?
