Queries :: Finding Only The Clients That Has Only 4 Types Of Products?
Sep 14, 2014
find only some clients that has only 4 types of products, but no other type of products.
Just to put it in a much easier way to understand. If I have to find only the client that did buy only 1 or more from the 4 products. Fridge, tv, dvd player, mobile phone. But I'm not interested in the clients that also did buy for example, laptop, pc, video cameras, etc. So if the client has only one of the 4 products, I want to list them all, did they buy another type of product too, then not.
How could I create a query that will show me only those clients?
View Replies
ADVERTISEMENT
Sep 10, 2013
I am using Access 2010. I've calculated the age of clients by creating a new field with Age: Year(Now())-Year([D O B]) but I cannot figure out how to use a parameter query to return the age of the clients between age 20 and 30, 30 and 40, 40 and 50 etc.
View 14 Replies
View Related
Apr 16, 2015
I have been trying to get this to work for days and I cant get it to work correctly. I have a query that I need to search only for the records that have 12,22 and 24 so in the fields criteria I have been trying
Total: Where
Criteria: In (12,22,24)
and it works BUT it only works for the first record it finds then it seems to skip and continue to search for all records after that.
View 3 Replies
View Related
Dec 6, 2013
As you know, the prices are, unfortunately, dynamic "objects".
So, I have a table for products:
tblProducts
ID_Product - Autonumber (PK)
ProductName
and a table for prices
tblProductsPrices
ID_ProductPrice - Autonumber (PK)
ID_Product - Number (FK on tblProducts
Price - Double
PriceDate - Date/Time
The problem: I have a certain date. I need to know the available prices at that date for all products.
I have a solution; but, for each product, I apply an external function that use two aggregate functions: DMax and Dlookup.
For my small tables, this isn't a problem but I like to know if it is a better way.
View 10 Replies
View Related
Apr 8, 2013
Finally got my DB straight so I am not using calculated fields however can't figure out the best way to handle the Combo Box so I do not show closed records ( Item checked out and item returned).
Please see the attached table of materials checked out and materials returned. How to setup a query so that if an item has been returned and matches the item checked out, Both the original check-in and the matching return records are filtered and not displayed in the Combo Box.
Also, I am a little worried about partial returns as when an employee only returns half the amount.
View 14 Replies
View Related
Nov 9, 2014
I am trying to make a dsum for products coming in and coming out. find the example below
Products In
Apple 10
Mango 5
Products out
Apple 5
Now I want to show statement for sum as follows
Product. In. Out. Net in hand
Apple. 10. 5. 5
Mango. 5. 0. 5
But it is showing only
Product. In. Out. Net in hand
Apple. 10. 5. 5
what I understand is dsum cannot find the out field for mango as there is no field found for mango
View 14 Replies
View Related
Apr 1, 2014
I use access 2010. I have a form that allows the user to assign products to operations. Each operation can produce multiple products and each product can be produced at multiple operations. I manage this many-to-many relationship through the use of a third table that stores primary keys for associated products / operations.
tblOperation
tblProducts
tblOperationProducts
The query im trying to design will populate a listbox on the form that contains products which are available to be added to a given operation. In addition to the listbox there is also a combobox that allows the user to select the operation they want to add products to. As the user selects an operation, the query behind the listbox should return records for all products except the ones that have already been assigned to the operation selected in the combobox.
So far I've been able to make the listbox return all products always or return products that haven't been assigned to any operation at all, but I'm struggling to formulate the logic in my mind on how to show only products that have no association (through the tblOperationProducts table) with the operation selected in the forms combobox.
This is the query I have right now, it's not working:
Code:
Select tblProducts.ProductID
From tblProducts
EXCEPT
SELECT tblProducts.ProductID
FROM tblOperation RIGHT JOIN (tblProducts RIGHT JOIN tblOperationProductMM ON tblProducts.ProductID = tblOperationProductMM.ProductIDMM) ON tblOperation.OperationID = tblOperationProductMM.OperationIDMM
WHERE (((tblOperation.OperationID)=[Forms]![frmOperationProducts]![cboOperation]));
View 1 Replies
View Related
Apr 17, 2014
I have a table that track monthly data for several thousand products.
The idea is to grab the first month and the first three months of sales for each product.
So I simply need to have a statement that scans through a predefined set of fields and seeks the first none zero value. This become the first month of sales (even if it is the 5th month of the year). It then needs to be able to grab the proceeding 2 months to create a sum of sales in the first 3 months. (however that would be for a second field, so presumably that is just a slightly more complex version of the same formula used to find the first month of sales).
View 2 Replies
View Related
Apr 3, 2014
I have a products form, we are a manufacturing company, with a listbox to show recent inventory transactions. This is based on a query which shows all transactions with the current part id, and that all works well and fine.
The problem is, I would like to limit this query to show only the last 10 transactions in the listbox and not make it editable, ie not enabled. I set the show only in query design view to 10 and it says in the sql statement select top 10, however, the listbox consistently shows all related records. What am I missing?
View 8 Replies
View Related
Jun 5, 2014
I have a query that selects invdate, status and type from a table, I want to be able to search for invdate=today status between 50 and 70 and to count types 30, 31+32, 33+34 and 35.
View 4 Replies
View Related
Mar 20, 2013
I have a simple query like below.
Code:
SELECT [score_admit] / SELECT [score_discharge] AS Ratio, facility_type
FROM tbl_test
group by facility_type;
It's really just based on one table, but what I'm trying to do is to calculate the ratio for the scores, then arrange the result based on facility types.
The error I get is a syntax error the SELECT statements.
View 3 Replies
View Related
Jul 6, 2013
Im looking to display the 4 lowest figuers obviously Min is the lowest but how would you dind the 2nd 3rd and 4th Min or is not possible?
View 14 Replies
View Related
Oct 8, 2013
I have a table with assignments if employees to teams.
PositionsID(autowert),EmployeeID, timefrom, timeto, TeamNo
3, 123, 13:30, 16:45, 4
4, 123, 17:00, 19:00, 7
5, 432, 17:00, 20:00, 8
6, 987, 17:00, 19:00, 9
7, 987, 13:30, 16:45, 5
....
Some of the employees can be assigned to more than one team, the assignement is not chronicaly, meaning an earlier timefrom can be assigned later (see Employee 987)
As a resultlist I need following:
123, 13:30, 19:00, 4
432, 17:00, 20:00, 8
987, 13:30, 19:00, 5
I can get out MIN(timefrom) and MAX(timeto) per EmployeeID but not the first teamNo, which is the first team he starts his duty!!
View 11 Replies
View Related
Aug 7, 2015
I have a table in Access that looks like:
fiscal_year acct_period loc_rc account afe description amount system
2015 6 01001028 745003 100050652John A Doe -69.72 E
2015 6 01001028 745003 100086599Jane Doe -33.37 E
2015 6 01001028 745003 100086599Jane Doe -587.69 E
2015 6 01001028 745003 994709 AP - EXPRPT050815SG - Jane Doe - 1 33.37 L
2015 6 01001028 745003 994709 AP - EXPRPT050815SG - Jane Doe - 2 587.69 L
2015 6 01001028 745003 994709 AP - EXPRPT05
2115PK - John A Doe - 1 69.72 L
2015 6 01001028 745006 100048910KROGER -389.74 E
It's combining data from 2 system Identified by E or L. The data is similar but not exact & I'm comparing it using the description. What I need Access to do is find the record that doesn't "zero-out". In the example above the record it should pull is the last one "Kroger $389.74". How to do this but I do have a query/report that will subtotal based on the LOC-RC field.
View 4 Replies
View Related
Apr 2, 2014
I need to create a query that will pull duplicate names out of my db.
I would like it to pull all names that have:
duplicate LastName and duplicates of the first 3 letters of the FirstName.
For example, if I had the names:
Bland, Abe
Brown, Abe
Brown, Bill
Buster, Jon
Buster, Jonathon
I would like my query to return only Buster, Jon and Buster, Jonathon.
View 4 Replies
View Related
Jun 4, 2013
I have a table tbl_PolicyDetails with details of the policy the customers have with us.. In the tbl_PolicyDetails, I have two fields (these are the ones in question) called
"policyStarted" - Start Date of a Policy and
"policyPeriod" - Term of Policy Monthly/Annually/Quarterly
So in a Form view I just get the Next installment for the customer based on this information for display.. For example..
Mr Butters Stotch's policy started on 26/04/2013 and is paying Quarterly; the next (i.e. 2nd) installment would be on 26/07/2013..
Ms Wendy Testaburger's policy started on 07/04/2013 and is paying Monthly; the next (i,e. 3rd) installment would be on 07/06/2013..
This is not hard to get.. I have that sorted.. But the problem is, my manager wants to be able to specify two dates and search all policies that will be 'paying in' that Date range should be picked up..
So if the search range happens to be.. 01/07/2013 and 31/07/2013 Then Mr Butters Stotch's policy should be picked up, as his next installment falls on 26/07/2013.. Since this information is not stored, I cannot run a direct Query on this.. Also as the Installment number varies for each customer, I would not be able to just add 1 - Month or Quarter or Annum, and see if the date falls in that range..
View 2 Replies
View Related
Jul 29, 2013
I have a table with associate names and rankings for each month. I need some way to find out which associates appear in 2 consecutive months with a certain rating. I have a query that will allow you to key in which month you want to look at, but I can't figure out how to write the query to return results only for those associates that appear twice in the months chosen. For example:
John Doe was rated 1.5 in May and 3 in June.
Jane Doe was rated a 1.5 in May and 1.5 in June.
I want a query that will allow me to put in May and June as the criteria as well as < 2 for the rating and only return those that show in both months...
View 5 Replies
View Related
Nov 8, 2013
What I am trying to do is the following
I have three columns
Name, Date, Number
What I need to do is find the difference in the number column where the Name is the same but the date is not ex:
Bob 2013-12-12 123
Bob 2013-12-15 456
Jane 2013-12-14 789
Jane 2013-12-25 987
So what I need to do is to get a result that is independent of date i.e.
Bob (456-123)
Jane (987 - 789)
View 2 Replies
View Related
Oct 11, 2013
I have a form that runs a report with two text boxes, one combo box, and a button.
The text boxes hold dates and SQL feeds them the oldest and newest dates from their fields (Date Received and Date Taken). They work fine, when the form is opened, the fields are filled and if I change the dates, the report pulls back data from the correct range.
The combo box is called Analyst Name and this is the row source within the parenthesis (SELECT [PickList-AnalystName].ID, [PickList-AnalystName].[Analyst Name], [PickList-AnalystName].[Analyst Manager] FROM [PickList-AnalystName];). The bound column is 2. The correct names are displayed, it looks like it works fine but then there's the button.
The button is named cboAnalyst. Enabled - yes. On Click - Event Procedure. This is the code with the backslashes added:
option compare database
//Private Sub cboAnalyst_Click()
Dim strCriteria As String
If IsNull(Me.[Analyst Name]) Then
strCriteria = ""
Else
strCriteria = "[Analyst Name] = "" & Me.[Analyst Name] & """
End If
DoCmd.OpenReport "Ad Hoc Reporting", acPreview, , strCriteria
End Sub
If I run this without a name, I get back all of the data within the right time frame. Once I put a name in, the report comes back empty, showing 0 for the count. I don't even get an error, I just get that empty report.
View 3 Replies
View Related
Oct 3, 2013
I'm doing a medical db, where I'm trying to calculate ISS
Code:
en.wikipedia.org/wiki/Injury_Severity_Score
There are 6 values, and i have to sum the largest three (squares to be precise). How can I get three highest values from 6 fields in the same record?
View 1 Replies
View Related
Dec 26, 2013
I want to find unmatched records, where there is a condition. Specifically, which employees did not get a specific mail.
My employees are in this table:
tblEmployees
---------------
EmployeeID (PK)
FullName (text)
and sent mails are logged in tblMailsSent
tblMailsSent
--------
MailID (FK)
EmployeeID (FK)
so I want to find all the employees in tblEmployees whose EmployeeID may or may not be in tblMailsSent with some other MailID's but definitely not with the specific one I am looking for.
I find my own method for doing this rather clumsy, so do you happen to have a nice recipe?
View 3 Replies
View Related
Jun 19, 2013
I need to find the latest number using a query.
All of the numbers follow this format:
[Doc No]
ABCD123
but the tricky part is the number could be suffixed with a revision. It is the latest revision I would like to find. For example, If I have a number followed by 3 revisions I would have:
[Doc No]
ABCD123
ABCD123+200
ABCD123+300
ABCD123+400
I need a query to give me the latest revision, in this case
[Doc No]
ABCD123+400
There is a possibility there are no revisions in which case the latest will be
[Doc No]
ABCD123.
How is this possible in a query???
View 5 Replies
View Related
Apr 15, 2013
I have a Table with Date Booked From and Date Booked Till
I want a query to show all booked vehicles between the dates in a parameter query, I thought I solved the issue but this is not the case
I am using the following codes
DateFrom - >=[ENTER Start Date]
DateTill - <=[Enter End Date]
All works fine but I have noticed if I put in the
start date 01/01/2013 and End Date 07/01/2013
if there is any vehicles booked between these dates E.g. 03/01/2013 - 05/01/2013, they wont show up.
how can i get round this problem?
View 14 Replies
View Related
Aug 13, 2013
I am new to access but understand how to do a simple query. in this case i have 4 fields in my query. I only want to return work orders where the only SN's for that work order begin with 600 or NEX. when i use the like button it gives me all those work orders, however it doesn't exclude the work orders with other types of SN's. is there a formula i can use that says return work orders that only contain this type of SN?
View 14 Replies
View Related
Aug 7, 2014
There are three tables. An [Action Register] table, a [Calls] table and a [tblContacts] table. The Contacts are common to both.
The Calls table records calls to customer by date
The Action Register table records issues that Customers send in by Open date.
I am trying to make a query where we see the latest date the customer was contacted regardless of which table.
I created two queries.
qryLastCallDate finds the max date from the Call table:
SELECT Max(Calls.CallDate) AS MaxOfCallDate, tblContacts.ContactName
FROM Calls LEFT JOIN tblContacts ON Calls.ContactID = tblContacts.ContactID
GROUP BY tblContacts.ContactName
ORDER BY Max(Calls.CallDate);
qryLastIssueDate finds the max date from the Action Register table:
SELECT Max([Action Register].Open) AS MaxOfOpen, tblContacts.ContactName
FROM tblContacts RIGHT JOIN [Action Register] ON tblContacts.ContactID = [Action Register].Contact
GROUP BY tblContacts.ContactName
ORDER BY Max([Action Register].Open);
The problem I am having is that if I use Left Join I can see all the records from the Calls table but not all from the Action Register table. And vis versa if I use Right Join. This is because sometimes we have calls but no issues in the Action Register table and sometimes issues with no calls.
This is my Left Join query using a Min Max Module I found here: [URL] ....
SELECT qryLastCallDate.ContactName, qryLastCallDate.MaxOfCallDate, qryLastIssueDate.MaxOfOpen, qryLastIssueDate.ContactName, DateValue(MaxOfList([MaxOfCallDate],[MaxOfOpen])) AS [Last Contact], ([Last Contact]+21) AS NextCall
FROM qryLastCallDate LEFT JOIN qryLastIssueDate ON qryLastCallDate.ContactName = qryLastIssueDate.ContactName;
How do I get to see ALL the records from both queries.
View 3 Replies
View Related
Aug 15, 2013
I have a database that is used (partially) to enter appointments during a day. In the appointment column I've entered the date and time as dd-mmm-yyy-hh-nn-ss. I'm now trying to search for appointments entered on a specific day through a user input ([Enter Date] in the query criteria) Problem is, this only returns records where the time hasn't been entered and the time shows as 00:00:00.
I've tried CDate which gives me add/mmm/yyy return. if I try to search against that I get nil returns though. As a test I put DATE() in the criteria but it then says that it's an invalid foremat. I've also tried various machinations of "Like" and "Between" without success.
View 5 Replies
View Related
