Queries :: Finding Work Orders That Only Contain Certain Type Of SN

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 Replies


ADVERTISEMENT

Modules & VBA :: Entering Orders Into The Orders Table?

Mar 5, 2014

I have a database where I have 3 tables. first is client data, second orders data and third the products data.
What I would like, is to have a button on my form that after selecting from a combobox a product, it wil enter it in the tabel orders in the next available free record (row). I created a button and the combobox in the client form. The Products table has also the price per unit in it. It should take the whole row of data and put it in the Orders table.

Is there a way to do this? I guess it needs to be done with vba, but not sure.

View 9 Replies View Related

Sort Orders In Queries

Dec 7, 2006

i've now converted a large database to run with sql as well as linked jet tables. The main issue was checking every recordset to ensure it opened properly. I also found that sql returns a few different errors code from Jet, that needed trapping differently = all OK.

Now, in some cases I had opened the backend Jet table directly in order to use seek on a particular index, so this needed changing, as this is not available with SQL

I may well be wrong but i couldn't see an easy way of traversing a recordset table in SQL/ODBC according to a particular segmented key

Instead I was able to modify my process to extract the data I wanted with a query, and I then used a recordset on the query, which is fine.

HOWEVER, to emulate the index I was using, the query is sorted on about 6 or 7 different segments, the first of which is CustomerCode. Now when I opened the query to view it, I changed the sort order by clicking on a date column (as it happens) to sort it. When I then closed and reopened the query it still opened sorted on the date column, which is not right, as the customners are now all out of sequence. When the whole dataset was re-extracted it did return to the original and correct order

THEREFORE Is there a way of forcing a query to reset itself to its original ordering sequence. I think it is not likely that any user would ever open a query directly anyway, but it would be nice to ensure the query is always processed in the intended order. Perhaps it isn't a problem at all - maybe when a query recordset is opened in code, the sort order is reset anyway, but I would appreciate some views on this.

Thanks in anticipation

View 3 Replies View Related

Queries :: Evaluate How Many Orders Were Open On Specific Day

Dec 12, 2013

Access 2010 project I am trying to develop for my workplace.

I want to be able to show a chart, a graph, of how many open orders we have had during the last month (showing per day) and during the last year (showing per months). In order to do this I am trying to create a set of queries that would provide me with such information in the form/structure;

[Date] [OpenOrdersTotal]
Yyyy-mm-dd 4
Yyyy-mm-dd 5
Yyyy-mm-dd 3
Yyyy-mm-dd 4

And after that I would create a chart I have
1. TblOrders
[OrderID] Autonum
[CaseID] Foreign primary key
[DepartmentID] Foreign primary key
[DateOrderMade] Date (yyyy-mm-dd)
[DateOrderAnswerd] date (yyyy-mm-dd)

2. TblDates
[ReportDate] Date (yyyy-mm-dd)

The evaluation in a query that runs all [ReportDate] logically would say;

Code:
Count(IIF([ReportDate] BETWEEN [DateOrderMade] AND [DATEORDERANSWERD]);1;0)

...but that wont do it.

I have attached a copy of the segment above ....

View 14 Replies View Related

Queries :: Dates In A Query - Show Orders By Month For Last 12 Months

Apr 22, 2013

I want to create a simple query from a list of orders dating back over 12 months.

The fields I have is Ord_Date, Qty

I need to show the orders by month for the last 12 months.

The problem I am getting is that the orders for the month of April (as we are in April now) contain orders from 1st - 22nd April 2013 and orders from 23rd - 30th April 2012, therefore confusing the figures.

I would just like orders grouped by month with a total qty dating back 12 months, but without any old orders for the current month.

View 6 Replies View Related

Queries :: Finding The Next Min?

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

Queries :: Finding First And Last Value?

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

Queries :: Finding Non-Zero Records

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

Queries :: Rolling 12 Month Query - Keeping Track Of Orders Placed For Given Part Number By Month

May 5, 2014

I am trying to create a database that will keep track of the orders placed for a given part number by month. Currently, my table houses the part number, and the ordered amount for the past three years by month (there are thirty-five columns for every part). My column headings are ORDER_MAY_2013, etc. I would like to set a query up that will look at the column headings and pull the amounts ordered for each part for the past twelve months. In other words, I have three years of data in my table. In my query, I just want one year. However, I don't want to have to rewrite the query every month so that it will pick up the new data. Is there a way to accomplish this?

Is there a better way to build this database? I thought about just have four columns in my table - PART_NUMBER, ORDER_MONTH, ORDER_YEAR, ORDER_AMOUNT. The only problem there, is that every part (there are about 450 parts) would have to be listed 35+ times. That seemed too redundant to me, so I built the table this way. However, now I am having trouble querying against it.

View 2 Replies View Related

Queries :: Finding Duplicates Using Last Name And First 3 Letters?

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

Queries :: Finding Records Between Dates?

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

Queries :: Finding Names That Appear More Than Once In A Table?

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

Queries :: Finding Difference Between Variables

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

Queries :: Report Not Finding A Field

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

Queries :: Finding Top Three Values In Six Fields?

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

Queries :: Finding Unmatched Records With A Condition?

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

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 4 Replies View Related

Queries :: Finding Latest Number Using A Query

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

Queries :: Finding Booked Vehicles Between Dates

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

Queries :: Calls Table - Finding Last Contact Date

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

Queries :: Finding Records In A Query Where Date / Time Has Been Used?

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

Queries :: Finding Max Record Based On Primary Key In Query

Jul 25, 2015

How to find the max value of a field in a query... How does a query display the max record based on the primary key... want to isolate the last record created, the one with the max primary key.

View 2 Replies View Related

Queries :: Finding Previous Record Date Difference?

Dec 17, 2013

I am trying to use a query to find the previous record of a transaction and provide an additional field that computes the difference in the dates in days. (The days from TransferID 23 to TransferID 24)

TransferID Job Number Tool Number TranferDate DaysOnRent
22 1 10 9/1/13
22 1 12 9/1/13
23 2 10 10/18/13
23 2 12 10/18/13
24 3 10 12/15/13
24 3 12 12/15/13

I have seen other posts that reference the following link.

[URL]

View 8 Replies View Related

Queries :: Booking System - Finding Available Room For Date Range?

Feb 18, 2014

So I'm trying to do this database for my ICT coursework and its a full system for dog kennels.

So in actuality the rooms are kennels.

I have a table tblbookings that amongst others has fields:

Kennel No
Date In
Date Out

I need a way of users entering the requested dates for a new booking and getting an output of a list of all kennels that are available to book for that full date range or even better, a way of running this straight from the form for a new booking frmbookings to just leave the first available kennel no. in the field KennelNo?

View 5 Replies View Related

Can 2 Random Queries Work?

Sep 27, 2005

Hi all,

I am not real sure whether or not this can be done with a query, but here goes.
I have a query randomly selecting the top 6 from a selected group.

SELECT TOP 6 tblConsortium.ContactID, tblConsortium.RandomID, patients.LastName & ", " & Patients.Firstname AS [Employee Name], Contacts.Company AS [Company Name]
FROM patients INNER JOIN (Contacts INNER JOIN tblConsortium ON Contacts.ContactID=tblConsortium.ContactID) ON patients.PatientID=tblConsortium.PatientID
WHERE (((tblConsortium.ContactID) In (SELECT ContactID FROM tblConsortium GROUP BY ContactID HAVING Count(*) <25))) And (((tblConsortium.PatientID)=Randomizer())=0)
ORDER BY Rnd(IsNull(tblConsortium.patientID)*0+1);

What I would like to do now is: The randomly selected Employees need to have a randomly selected TEST. There are only 3-Test to choose from. I have placed the TESTS in a separate table and entered 20 of TEST1, 4 of TEST2, and 1 of TEST3. I then set a query randomly shuffling these items. I need these TESTS to randomly be assigned to the randomly selected Employees above.

I would greatly appreciate any thoughts or help…
Thanks Enviva

View 1 Replies View Related

Queries :: IIF With Sum Doesn't Work

Jan 9, 2014

I need to do the price in table [price] multiply by 1.20 if the price is higher then 150. If the price is between 75 and 150 it have to multiply by 1.25. Continue... continue... continue...

expr1: IIF([Price]>"150",[Price]*1.20,IIF([Price]>75,"[Price]*1.25",IIF([Price]>50,"[Price]*1.28",IIF([Price]>30,"[Price]*1.35",IIF([Price]>15,"[Price]*1.45",IIF([Price]>0,"[Price]*1.6"))))))

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved