I am working with Access 2010, on vista. What I have is a query made up of two tables, one product the other inventory. (see below) query.jpg
In the product table i have a field called "minimum reorder level". In the inventory table i have two fields one called "number in stock" and "number on order". What i want to happen is "number on order" to be filtered by the result, if the "number in stock", is less than "minimum reorder level", if it is, have the result placed in the "number on order" field. EG. if the "number in stock" = 2 and the "minimum reorder level" = 5 then 3 would be placed in the field "number on order" and only the second record from the query would be visible (see below) Query result.jpg The result of this would mean that the field "number on order" would be populated with the result and the and query would also use this to filter the record.
I have searched and search and I can't find the answer to this question when I thought it would be simple. Hopefully you can put me out of my misery!
I have a access 2007 split form with a series of search boxes and a query result window. Enter info into one or more of the search boxes, click search and you get the matching results in the query window below. That works fine but I want to work with the results of the query, how can I do this?
I was looking for the user to be able to select a record and then click a command button to open that record in a form. Or in another instance select a record in the result window, have the current from close and have the primary key passed to a another form which I use to open the split form.
I just can't see how to do it but I expect there are several ways. Happy to use VBA but my experience is with Excel VBA so I'm still getting used to the objects.
Anyone know an efficient way of setting the value of a textbox to the result of an sql query?
I am using the following, but it seems to be slow when populating a large form:
Make.RowSource = "SELECT BarcodeDATA.Make FROM (Customers RIGHT JOIN CustomerSales ON Customers.CustomerID = CustomerSales.CustomerID) LEFT JOIN BarcodeDATA ON CustomerSales.ItemNum = BarcodeDATA.ItemNum WHERE (((CustomerSales.ItemNum)=[Forms]![FormCheckConsignmentStatus]![ItemNum])); "
I'm fairly new to Access. 's various select queries containing useful and useless results. I want to create a select query that will pick out all the useful figures into a 1 row table that can then be pasted into Excel.
e.g Existing Select Query 1 returns 1 row showing Average Age, Average Price, Total rainfall Existing Select Query 2 returns 1 row showing Average Weight, Average Salary, Total snowfall Existing Select Query 3 returns *2* rows: It returns Distance from London, Hours daylight and population for Town A and Town B
I want a select query that returns 1 row showing (6 items):
Total rainfall, Total snowfall, Town A Distance from London, Town A Population, Town B Distance from London, Town B Population.
I've been able to handle getting Total rainfall and Total snowfall. But I cant figure out how to get Town A Distance from London, Town A Population, Town B Distance from London, Town B Population to appear in the same row of the same query results as Total rainfall, Total snowfall.
Simple division, I thought! I'm dividing a Sum of hours by Pay Periods to determine the average of missing hours for year-to-date. Most of the division works perfectly, and some doesn't and appears to be related to values of 1 or less than one.The formula: [Sum]/[Pay Periods].Sum relates to total missing hours and pay periods are total pay periods for the year so far.What comes out wrong:
1 (hr) / 12 (pay periods) = 8.33, when it should be .0833 0.5 / 12 = 4.166 when it should be .0416
What is right is everything else, starting with 1.5 hrs
1.5 / 12 = 0.125
What do I need to do to make all of the values divide correctly?
i I have two queries.. What i'm hoping is to combine the result into one query but not in one column only but instead the result of the second query should be beside the first query.. The result of the second query should be added as a new column.
First Query
SELECT tbl_uSers.UserName, Count(tbl_rEceived_eMail.EntryID) AS eMailReceived FROM tbl_rEceived_eMail INNER JOIN tbl_uSers ON tbl_rEceived_eMail.UseriD = tbl_uSers.UseriD GROUP BY tbl_uSers.UserName;
Second Query
SELECT tbl_uSers.UserName, Count(tbl_rEceived_eMail.EntryID) AS eMailProcessed FROM tbl_rEceived_eMail INNER JOIN tbl_uSers ON tbl_rEceived_eMail.UseriD = tbl_uSers.UseriD GROUP BY tbl_uSers.UserName, tbl_rEceived_eMail.ProcessedYN HAVING (((tbl_rEceived_eMail.ProcessedYN)="Y"));
I am running a very simple query that divides profit over turnover to show the margin. I have many queries throughout my database that do this, here is my little formula;
Margin: Round(([profit]/[Turnover])*100,2)
Now, this runs just fine but if I put in a criteria of <10 I get the division by zero error. The reason I am confused is that there are no zeros or error values or even negative values in either of the profit or turnover columns?
Also I have an almost identical query in another database that has a <5 criteria in it and it works a peach.
I have searched but all I am getting is the usual definition of the division by zero error.
I have a database which contains a table called "orders" that has a field called "quantity" in it. I also have a query called "defective parts supplied" which has a field called "defective parts".How would I use a new query called "% failure/defect rate per supplier" to calculate the following:100 * [defective parts]/[quantity].
I currently have the sql below... UPDATE tbl_Node_List INNER JOIN qryUpdate_P1 ON tbl_Node_List.Zip = qryUpdate_P1.[Zip Code] SET tbl_Node_List.[Date Sent] = (SELECT [MinOfAudit Date] FROM qryUpdate_P1), tbl_Node_List.[Date Recv'd] = (SELECT [MaxOfAudit Date] FROM qryUpdate_P1) WHERE (((tbl_Node_List.Zip)="35243"));
but I get an err.msg stating the operation must use an updateable query.
What I am trying to do is update my dates in 'tableA' with the max and min values stored in 'tableB'. I have read some of the posts but still can't quite get it. As always all help is appreciated.
there is a query which i made which counts data from two tables RAUTH AND PERS
SELECT RAUTH.coy, RAUTH.Auth, Count(RAUTH.coy) AS Held FROM pers INNER JOIN RAUTH ON pers.coy = RAUTH.coy GROUP BY RAUTH.coy, RAUTH.Auth;
THERE IS A BLANK FIELD NAMED HELD in table RAUTH i want to update the held result which i quired into HELD fiedl of RAUTH table where COY (Field) is COY field of PERS table
RAUTH table structure
Coy Auth Held
Tata 12 Dell 11 HP 21 Opera 11 Bangour 10 pers table
Persno Name Coy Qual
124578 Gita Tata Bsc 235689 Sonu Dell BA 748596 Kiran HP BCA 459687 Sony Tata MCA 965214 Baiju Bangour CCNA 963656 Ashok Opera MCA
I have a result for a query made from four different tables for which I would like to refine the result of this query grouping multiple rows into columns.
Attached is a pdf file showing the results being obtained by my query and underneath is how the result would like it be after running the query.
How to take the results of query and copy them to a column in a table?
I am using sharepoint and ms access together, and Sharpoint in this instance will not reference the result of the query. I can only use table fields with Sharepoint...major pain....
Example, I need riders name, weight and height from the column in ALL INfo query to automatically fill the column table in ALL Info