Queries :: Looping Through Query To Find Highest Priority Item
Aug 20, 2013
I have a database where part of it consists of an Item table and a Formula table. There is a many-to-many relationship between them so I created a junction table for this purpose.
Tables
tblItem
tblFormula
tblFormula_Item_JNT
There is a column in tblFormula called [Priority] that is a lookup field. It has the values of "High", "Medium", "Low".
So here's what I'm trying to do: I have a query that searches against tblItem for all Items that have a value of No/False for a field called Item_Status. That's simple enough. But I also want to show the Priority of the Formula that the Item belongs to. When I add that to my initial query I get multiple results of the same Item if it belongs to more than one Formula, which I understand. But I really want an Item to show up only once.
I want the query to go through each Item (based on its Item_ID), look through all the Formulas it's in, and if at least one of those Formulas has a Priority of "High" then it will display "High" in a field (could be a calculated field) in the query results. If there are no "High" Priority Formulas, then look for if there are any "Medium" Priority Formulas, etc.
View Replies
ADVERTISEMENT
Jun 3, 2015
I have a form presented in datasheet layout.
I set the initial sort by two columns, but I would like to keep highest priority for that initial sort, so when user wants to sort by some other columns that sort happens inside my initial sort.
View 13 Replies
View Related
Sep 7, 2014
I need finding the First to fourth highest numbers in four columns.
For Example:
Student Name Subject1 Subject2 Subject3 Subject4
Max 60 40 50 10
I need query to find the first, second, third and fourth highest marks in subjects.
View 2 Replies
View Related
Jul 6, 2013
Setup a query to find the result in a table containing the highest date value.
The query is linked to two tables : Payment information containing the date, and tenant information containing the tenant.
In the query i have selected the tenant name from the payment table (which is linked to the tenant name in the payment table) and the payment terms - ie weekly / monthly etc. I've then selected the payment date from the payments table.
The query should return for each tenant the latest date they paid.
On the pay date i selected the Max option.
But it shows me more than one record.
SQL query is shown here
SELECT Max(tblPayments.DateDue) AS MaxOfDateDue, tblLease.cboPaymentTerms, IIf([cboPaymentTerms]=2,DateAdd("ww",1,[DateDue]),IIf([cboPaymentTerms]=3,DateAdd("ww",2,[DateDue]),IIf([cboPaymentTerms]=4,DateAdd("ww",4,[DateDue]),IIf([cboPaymentTerms]=1,DateAdd("m",1,[DateDue]),"n/a")))) AS calcNextPayDueDate, tblPayments.cboTenant
FROM tblPayments INNER JOIN tblLease ON tblPayments.cboTenant = tblLease.cboTenant
GROUP BY tblLease.cboPaymentTerms, IIf([cboPaymentTerms]=2,DateAdd("ww",1,[DateDue]),IIf([cboPaymentTerms]=3,DateAdd("ww",2,[DateDue]),IIf([cboPaymentTerms]=4,DateAdd("ww",4,[DateDue]),IIf([cboPaymentTerms]=1,DateAdd("m",1,[DateDue]),"n/a")))), tblPayments.cboTenant;
View 2 Replies
View Related
Apr 21, 2006
I know how to setup a Replica's Priority, but where do I go if I want to change it afterwards? I have searched everywhere, or am I just supposed to create another replica and set it then?
View 2 Replies
View Related
May 9, 2014
Show me the selected item in a Listbox without looping in the whole list, because my Listbox is multiselect and I want only the item that selected recently.
View 8 Replies
View Related
Nov 20, 2013
This query returns a running asset balance for 60 months. Each month is 1 column.
1) I need to create a query based on those results that can cycle through each column and determine if it is NEGATIVE. Then if it is negative, I need to perform some math on it to determine how to get it back up to being positive.
2) I need to loop through X columns based on the assets lead time. Each lead time varies and I have a master table that contains it. So if the assets lead time is 3 months, I need it to move over 3 columns and return that value. If it is 6 months then I need it to return the value in the 6th column.
View 9 Replies
View Related
Oct 3, 2013
using access 2010. I have a table where I need to pull out the highest value. But sometimes; there are two or more with the same high value. I need a way to retreive the first highest value. I've done this before a while ago and I can't make it work again. I started off with grouping highest value but are getting all three same values.
View 2 Replies
View Related
Jan 23, 2014
I have 4 columns
PostCode
District
District Name
Count of Postcodes = Count(Left([hull_PostCode.PostCode],4))
Now some Postcodes display 4 lines as they cover 4 Districts, my questions is I only want to show the highest Count per postcode and disregard the rest, Ive tried many variations of the Max function but am a little stuck.
View 1 Replies
View Related
May 16, 2014
I have several tables which have an indexed, no dup field. When inputting a entry that is not in the referenced table, how can I be taken directly to the input form for that field.
View 1 Replies
View Related
Apr 8, 2013
Access Database in which i download the table from an Excel file.I am looking to create a query with certain criteria but did not have any luck recently.
1) I will need the query to show <Short code> that are only unquie to Korea.That means shortcode with a count of 1 belonging to Korea only.
2) I will need the query to show as a single line item <Shortcode> that appears two times under the country Korea. For example short code CB01406 is shown as two rows, i will like it to only show as a single row.
3) For all other shortcodes which exist in China as well as in Korea or China only. I will like them to be hidden in the query. That means not shown at all.
View 7 Replies
View Related
Oct 27, 2005
I have 3 fields in query. I want the highest value from field2 in field3. Shown as below: How I do this without using Group By?
field1 field2 field3
1 1 2
1 2 2
2 1 3
2 2 3
2 3 3
View 3 Replies
View Related
Oct 31, 2014
I have a query pulling data from two other queries (qry_Reports and qry_Surveys). Clients may have more than one ReportID, but only one ClientID. I need to query for only the most current ReportID (which is the larger value) for each client to find the surveys for the most recent report. How can I query for only the most recent report for each client based on the highest value of the ReportID per ClientID?
View 11 Replies
View Related
Oct 25, 2005
Hi,
I am using this sql query : -
SELECT Monthly_below_report.Device
FROM Monthly_below_report
GROUP BY Monthly_below_report.Device, Monthly_below_report.[FS-ID]
HAVING (((Monthly_below_report.[FS-ID])=780));
The problem is this currently pulls out 5 records (all devices found and the amount of times they appear).
How can i get it to pull out JUST the highest count.
I.E :-
that query above will pull out
15
12
10
3
1
How can i get it just to display 15.
Thanks
k0r54
View 6 Replies
View Related
Aug 3, 2006
Hi, not sure looping is the correct word so i will try to explain what i am trying to do.
i have a tbl and need to mark the records in groups of 12. the first group would be in group 1. The next group of 12 would be 2. and so on. the highest number is unknown as the data will grow.
is this posible with a qry? or do i need a for next loop and run a qry each time? if i do that how do I incriment the to the next level.
Thanks.
View 14 Replies
View Related
Oct 31, 2006
I am trying to add another record to a table. Pid and nsn are the keys. the table has 70 unique pid's. I need to add another nsn to each of the pid's. I tried the following code but no go:
Set db = CurrentDb
Set rst1 = db.OpenRecordset("tblHandReceipt")
Set rst2 = db.OpenRecordset("tblHandReceipt")
rst1.MoveFirst
Do Until rst1.EOF
rst2.AddNew
rst2!PID = rst1!PID
rst2!NSN = Forms!frmparts!NSN
rst2!ISSUEDSOURCE = Forms!frmparts!subfrmEndItemParts.Form!cbxENDITEM
'Add like above line for each field
rst2.Update
rst2.MoveNext
rst1.MoveNext
Loop
rst1.Close
rst2.Close
Thanks for any help.
View 3 Replies
View Related
May 29, 2015
I have a table 45 fields. There is a field ID. I would like to get the records where fields contains "No". I would not like to create 45 queries for each field check. how can i generate a table which shows ID, Field Name that contains value "No".
View 4 Replies
View Related
Apr 23, 2015
I have a multi slect list box (simple) and I need to find and select an item using vba - e.g., the bound column is the ID field and I need to select a specific ID (which will be different each time) as opposed to selecting the 100th record for example. How do I do this?
View 2 Replies
View Related
Sep 16, 2014
I am attempting to build a small database for my firm to keep a track of equipment. The equipment can be in one of three places. In the warehouse, out on a job or at the repairers. I want to create a query that will let me know where a piece of equipment is at any one time. I'm sure my tables are set up correctly. I have use a union query to work out when equipment is on a job or in repairs but I need one to show me where all the equipment currently is.
View 1 Replies
View Related
May 20, 2013
i have created a parameter query which will find a students best and worst time for each exercise they have done. so you enter the student ID when you run the query and it works fine but i have a problem i need to query to find two students in particular and then i have to create a report from this query on the two students identified and this is where i come up stuck. i have tried typing in both student id's into the criteria but this doesnt work no matter if i put and in it or not and im not sure how to get the job done.
View 7 Replies
View Related
Feb 13, 2014
I am after getting an access 2003 database to look after and it contains SQL pass through query's. The database is a front end to a MS SQL server database with a connection string that is contained in a module.
I believe the SQL pass through queries are connecting to the same database as the rest of the application and somehow is using the connection string in the module. However I cannot find how that is configured on the SQL pass through queries.
Most documentation on the net seems to point at using ODBC to connect slq pass through queries to outside databases but I don't think this is the case. There are not Odbc sources set up for the database I am look at.
View 8 Replies
View Related
Jun 13, 2015
How do I find the previous record in a query using the autonumber field?
View 10 Replies
View Related
Oct 23, 2014
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.
View 3 Replies
View Related
Jun 15, 2013
I am trying to manipulate a find duplicates query using the following criteria:
Fstnm L2, Lstnm L5, Add1, Zip
This is what I have done so far:
SELECT [Duplicate Identification Dataset].[FSTNM], [Duplicate Identification Dataset].[LSTNM],
[Duplicate Identification Dataset].[ADD1], [Duplicate Identification Dataset].[ZIP],
[Duplicate Identification Dataset].[ID], [Duplicate Identification Dataset].[MIDNM],
[Duplicate Identification Dataset].[SPFSTNM], [Duplicate Identification Dataset].[SPMIDNM],
[Code] .....
View 1 Replies
View Related
Mar 8, 2014
ID machine value
1 111 0
2 112 1
3 113 2
4 111 0
5 112 2
6 113 2
7 111 1
8 112 0
9 113 -1
10 111 2
11 112 1
12 113 -1
13 111 1
14 112 -1
15 113 1
16 111 1
17 112 1
18 113 0
I have three machines. They all have random variations, but a machine could also have a static deviation. If the last 4 values are above 0, the deviation is considered static and should be corrected. In this example 111 has a static deviation
Nowadays the operators look at the graphs and when they observe the static deviation they will adjust it.
Question is: Is it possible to use a query to extract 111? In that case I can give operator a warning in stead of him looking at the graph.
View 1 Replies
View Related
May 29, 2013
I have a pretty complex database that uses several queries that all lead to a final query with a parameter (order number). I have a report linked to that query so every time I open the report it asks me for an order number. Once I enter the order number the query is run and I print the report. I am curious if I can create a query that pulls out order numbers (possibly for a date range) and then have a macro or something loop through that query and print a report for each order number. So if the query returned 50 order numbers then the report with the parameter (called Final results) would be printed 50 times.
View 1 Replies
View Related
