Query Only Latest Info

Sep 7, 2005

How do I make an append query only append the records beginning with the next autonumber?

I have linked tables in db...one db is for warehouse employees to input their orders and the other table is in the original db for managers to use for reports. I need to append the records to original db throughout the day, but the records cannot be deleted from warehouse db because it keeps running total of order minutes (for production purposes) so employees know how many minutes they have (and need to make up) through the day. Also, managers need up to date db so they can see if they are on track.

The autonumber field is my primary key..
I hope this all makes sense.

Thanks in advance
Noreene

View Replies


ADVERTISEMENT

Query Latest User

Sep 11, 2006

Hi,

I am working on a report and I am stuck with the following problem:

Here at work we use two tables. One for creating records and for the updates of the created records. In the attachment you see a part of the table 'updated'. What you see is one ServiceNumber (1-551747091) which is changed users (Eigenaar.) three times.

I want to query the latest user of each ServiceNumber. So that the result is:

ServiceNumber | User
1-551747091 | MKB_VERHUIZEN
etc.

How to do that? :confused:

Thanks!

View 3 Replies View Related

Query To Get Only Latest Entries

Feb 26, 2007

I am having difficulty formulating this query.

I have 2 tables... 1 is customer info (name, address, etc...), and the second is shipments (ShipToCustomerName, ShipmentDate, etc...).

Now, I wish to query the shipments table to get ONLY the LATEST shipment information for EACH customer.

If customer A has a shipment in Jan 1, Feb 2, and March 3... while customer B has a shipment in April 4 and May 5. then the result of the query should be

customer A - March 3
customer B - May 5

Is there a simple way to construct such a query?!?

Thank you for your help,

- arm

View 1 Replies View Related

Query Based On Latest Time

Apr 7, 2007

I have two fields. A barcode field will have several instances of the same data, and the other field has different dates and times when it was scanned. I need to be able to query the barcode field and only return the record with the latest time. Can this be done in a query or do I need to do something else? Here is a sample of data I am working with. I attached a small sample of data as an example.


Thanks,

View 3 Replies View Related

Find Latest Record With Query

Aug 22, 2007

I'm fairly new to SQL and have a problem finding the latest record.

Lets say I have a development project that has many disbursal payments to be sent. I want to find the latest disbursal record created for that one project instead of having all of the disbursal records show on the query.

I'm trying to work out how to do this in a query but haven't got a clue where to start.

Please, if anyone can help I'd be very grateful!:D

View 2 Replies View Related

Pick Latest Record From Query?

Dec 16, 2005

I am trying to pick the latest record from "tblEmpVac.id" field but I keep getting syntax errors. I am thinking if the "tblEmpVac.id" = Count('tblEmpVac.id') then that will show the most current record as it will count and match the last record correct?

Another problem is if I put it into the WHERE section, it will give a conflict. So I don't know where to put it. Below is the SQL, if anyone can help it would be great.


SELECT DISTINCTROW tblEmp.position, tblEmp.fname, tblEmp.lname, tblEmpWorkHistory.region, tblEmpWorkHistory.[current store], tblEmpVac.current_year, tblEmpVac.id, tblEmpVac.entitlement, tblEmpVac.days_taken, tblEmpVac.days_carryover, [days_carryover]+[entitlement]-[days_taken] AS total
FROM ((tblEmp INNER JOIN tblEmpVac ON tblEmp.ssn = tblEmpVac.ssn) INNER JOIN tblEmpWorkHistory ON tblEmp.ssn = tblEmpWorkHistory.ssn) INNER JOIN tblEmpWorkHistoryData ON tblEmpWorkHistory.ewh_id = tblEmpWorkHistoryData.ewh_id
WHERE (((tblEmp.position)<>"Terminated") AND ((tblEmpVac.current_year)="2005"))
ORDER BY tblEmp.position, tblEmpWorkHistory.region, tblEmpVac.id;

View 2 Replies View Related

Show Only Latest Note Entry In Query

Jan 13, 2008

Two of the fields in my query are for Progress Note and Progress Note Date. Each client has several progress notes. How can I have the query show only the Progress Note with the latest date?

:confused:

View 9 Replies View Related

Select Query For Latest Previous Date

Oct 2, 2006

My table shows
ID ID_Clients EndDate

A particular value of ID_Clients can recur many times, but with different EndDate value each time.

I want to create a select query that displays
ID_Clients EndDate LatestPreviousEndDate

Any help with this would be much appreciated.

-Thanks

View 1 Replies View Related

Queries :: Query To Show Latest Date

May 29, 2014

I have two tables. The first is called Drawing Register and contains the fields Drawing No (primary field) and Drawing Title.The second table is the Drawing Register Details table which contains the fields Drawing No (joined field - Drawing Register table), Revision, Revision Notes, Date Issued.

I have a query and report which will list all the revisions for each drawing. This is very important and useful. However I want a summary report which will only show the last revision for each drawing.

I copied the original query, turned on the Totals option and under the Date Issued field changed Group by to Max. However it is still returning all results for each drawing instead of only the last issue date for each drawing.

View 14 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 :: Getting Latest Record For A Particular Customer Number Using MS SQL Query

Jul 27, 2013

My table appears like this with the following columns:

Name| CUSTNo| ORderNo.| AcctDate| OrderDate| OrderDescription

I would like to write a ms-sql query to return the records of each customer number with the latest OrderDate. One more thing, if the customer has two separate order numbers on the same date(should be the latest date), the query should be able to pull up both the records.

I tried with the code below but its taking a long time to execute and finally hanging up MS ACCESS.

SELECT * FROM TableName AS a WHERE Not Exists (SELECT *
FROM TableName b WHERE b.CUSTNo = a.CUSTNo AND b.OrderDate >= a.OrderDate);

View 2 Replies View Related

Queries :: Latest Date Per FName - Query From Table

Nov 23, 2014

How do I query from this table:

ORIGINAL_table

FName | END_Date|
--------|---------------|
John| Oct 09, 2014|
John| Oct 15, 2014|
John| Oct 25, 2014|
Mike| Dec 10, 2014|
Mike| Dec 15, 2014|
Mike| Dec 20, 2014|
Mike| Dec 25, 2014|
Jimm| Dec 10, 2014|
Jimm| Dec 15, 2014|
Jimm| Dec 20, 2014|
Alex| Dec 01, 2014|
Alex| Dec 05, 2014|
Alex| Dec 10, 2014|
Thom| Nov 10, 2014|
Thom| Oct 10, 2014|
Thom| Aug 10, 2014|

The output will be:

FName | END_Date|
----------|---------------|

John| Oct 25, 2014|
Mike| Dec 25, 2014|
Jimm| Dec 20, 2014|
Alex| Dec 10, 2014|
Thom| Nov 10, 2014|

The query table(output) will just have to be latest date("END_Date") per "FName"

View 1 Replies View Related

Queries :: Query Result For Latest And Max Date From Table

Apr 26, 2013

I try to make a query that can show me data from the lastest and max date from the table i have ( see attchment file )

I try using max but still the result is not what i am looking for...

View 6 Replies View Related

Queries :: Subquery To Return Latest / Most Recent Value For Each Record In Main Query

May 7, 2014

I have a table of accounts and a table of rates. There is a one-to-many relationship between them (i.e. each account can have multiple rates, each with their own - unique - effective date)

I'm trying to build a query which will show me all of the accounts in the accounts table and the most recent rate (based on the effective date) for each of those accounts.

This is as far as I've gotten with the SQL :

Code:
SELECT [tblAccounts].[AccountID], [tblAccounts].[AccountNumber], [tblAccounts].[AccountName], [LatestRate].[IntRate], [LatestRate].[EffectiveDate]
FROM [tblAccounts]
LEFT JOIN
(SELECT TOP 1 [tblRates].[AccountID], [tblRates].[IntRate], [tblRates].[EffectiveDate]
FROM [tblRates]
ORDER BY [tblRates].[EffectiveDate] DESC) AS LatestRate
ON [tblAccounts].[AccountID] = [LatestRate].[AccountID]

But this can't work because the [LatestRate] subquery can only ever return one record (i.e. the most recent rate across all of the accounts)

I need the most recent rate for each of the accounts in the main query

(FYI - I use an outer join as it is possible for no rate to be available in the rates table for a given account, in which case I want to return the null value rather than omit the account from the resulting dataset...)

View 2 Replies View Related

Update Order Info Based On Quote Info

Nov 7, 2006

I need help on this, from what the best concept is, to what I need to look into using:

I store Quote data from phone calls into tblQuotes. There is a seperate table that holds much of the same information except that it is for actual orders called tblOrders.

As far as function goes, I have each working much the way I need it to except for one thing... If a sales person is on the phone with a customer with a quote already in the system, right now they re-enter the data into the Order table. Most times, the order is what was quoted, but maybe with a few small changes (so I will wnt to keep a historical record of the quote). How could I copy the contents of the quote recordset into the recordset for an order, where all the sales rep does from there is edit the quote to the actual order?

Would I use VBA or an update query that is executed via VAB? Honestly, I am unsure how to do this at all, I hope someone has seen this before and has a good suggestion...

Thank you.

View 2 Replies View Related

How To Use Query To Remove Info

Aug 17, 2005

Is there any way I can remove specific information from all the records in a field using a query?
For example- how could I write a query that would remove the "NME" from NME ACTIVE; FENCE CO INC? I know you could add text to a field by including & "text".

View 1 Replies View Related

Can't Enter Info Into Query

Nov 12, 2006

For some reason the query I built will not let me enter any info into it. Is this a relationship problem or because I am using the querie in a form that has subforms, (I can enter new info into the subforms.) or because I wrote code to just print the report to the current record? Or something else????

Thanks!
Julie

View 7 Replies View Related

Combining Info In Tables For A Query

Sep 25, 2005

Hi there,

I just can't figure out how to solve my next problem. What I need to do is to make a query showing all the country's next to germany(nr.5) with their capitals. There are three tables which I can use, nr.1 showing the country_id's with their "neighbour country's", nr.2 showing the name of the country's with their capital_id's and nr.3 showing the name of the capital with their corresponding country-id.
Is there anybody who could help me with this problem, or has some suggestions? If you find this message unclear, i would love to e-mail you the tables.
As i am dutch my english isn't that good.
Thanks in advance

1:
country_1 country_2
12
13
14
15
24
25
26
210
35
45
57
512
612
89
1011
1314

2:
idname capital_id
1Belgium 1
2France 4
3Netherlands 9
4Luxembourg 14
5Germany 6
6Italy 12
7Denmark 15
8United Kingdom 11
9Ireland 16
10Spain 17
11Portugal 18
12Austria 19
13Sweden 20
14Finland 21
15Greece 22

3:
idname peoplecountry_id
1Brussel 1370001
2Antwerpen 4970001
3Gent 2357001
4Parijs 21762432
5Lyon 4184762
6Berlijn 34000005
7Bonn 2930005
8Munchen 12670005
9Amsterdam 10380003
10Rotterdam 5760003
11Londen 66780008
12Rome 28260006
13Milaan 15150006
14Luxemburg 790004
15Kopenhagen 4820007
16Dublin 5260009
17Madrid 320000010
18Lissabon 80700011
19Wenen 151200012
20Stockholm 65100013
21Helsinki 48400014
22Athene 100000015

View 3 Replies View Related

Using Info From One Table In The LIKE Statement Of A Query

May 17, 2007

Is it possible to construct a query that uses information from one table to run a series of Like OR Like... criteria?

I have created a table that contains partial part numbers and would like to query a second table for all the part numbers that contain these partial part numbers.

Meaning, BACB30DX6 would return the desired information for BACB30DX6-7, BACB30DX6-8 and so on.

The TBL_PARTS_FAM_DIA table contains approx. 200 items so 200 LIKE statements can't be the way to go... I'm hoping.

I tried: LIKE "*[TBL_PARTS_FAM_DIA]![PARTS_FAM_DIA] *" thinking that perhaps this would go through all 200 items... but of course that was wishful thinking.

Do I really need 200 LIKE statements or is there some easier way to do this.

Thanks in advance!

View 2 Replies View Related

Need A Query That Shows Only Info With No Duplicates.

Sep 5, 2007

Okay I have a database that stores information on some payments.

Payments can be made on different dates and multiple times during the month.

There is an account number which will have duplicates in the table if there is more than one payment. This is fine.

However, I need a query which will show only those records for account numbers which appear only once.

So if it has duplicates, it is ignored, otherwise it's reported.

TIA for the help.

View 2 Replies View Related

Queries :: Delete Info From A Query?

Aug 18, 2014

Okay I have 2 tables and I used queries to filter and delete the info off those tables. Now I combined the 2 tables by using a query which is query 1. What I found was a few cells that are blank cells.

I tried creating another query based on query 1 from the 2 tables to try and delete it but it won't work. I tried everything and nothing seems to work. I can't filter the data since I am creating another query called query 3 to display the final results.

View 2 Replies View Related

Queries :: Max Query Returning More Info

Jun 16, 2015

I have two tables:

tblSITE and tblUSID (joined by SITEID)

and I am trying to create a query which lists each site and the USID with the latest date in its ConstructionDate field (USID and ConstructionDate are both in tblUSID). If I omit the USID from the query and run a totals with MAX on the ConstructionDate field, I return what I would like to, but I want to add the PK (USID) of the record with the latest ConstructionDate for another query, and every way I attempt it the query returns multiple records per SiteID.

View 3 Replies View Related

Query Column Info Rather Than Actual Value?

Dec 15, 2011

In a table I have a field that gets its value from a lookup with multiple columns. Is it possible to query information in the columns rather than the actual value.

Here is an example of a single field value in the table I want to query (this would be in a drop down box in the table):

ID | Name
1 Nick
2 Nick

The actual values are the 1 and 2, but I would like to query the name 'Nick'. How would I do this?

View 5 Replies View Related

Append Query Based On Existing Info

Oct 6, 2005

Wow, seems like this is my favorite place in the world now.

My problem is that I need to append data from one table to another, on the condition that any of the fields in the record have changed since the last time the query ran.

For example,

tblEmployee has the fields 'EmployeeID', 'Name', 'Position', and 'Office'. We are appending to tblEmployeeHistory with the same fields in addition to a field for a date. When executed, the query should append the records where something has changed and attach today's date to them. This enables us to later use another query to call up old information based on the date.

Once again, this is a hypothetical situation and not my actual database. Any solution or comments are welcome.

View 2 Replies View Related

Help With Running Query From Info Keyed In Form

Feb 22, 2005

I need help to figure out how to create a form that includes a place to enter date ranges (A start and an end date: the field name in the table is CrossClampDate), patient name, and case number as a search. I have just taken 3 levels of Access classes and saw an example of this, but we never went into it and from the Properties box for all parts of the form I still can't decipher how it was done.

Basically, I want to run the operations a query goes through by inputing this information in a form.

I have created an actual query that performs this operation, and it works. But I am prompted for each criteria separately in individual pop up boxes. I just need to streamline this operation for the users.

Any help would be GREATLY appreciated!!!

View 1 Replies View Related

Form Showing Info Pulled From A Query...

Jun 20, 2005

Actually, I don't think i've done this right, but here's the goal I wish to achieve.

I have 2 tables, one containing contract info and another containing client info. I have the 2 related using the client name field. I have a form which is based on the contract table which contains a drop down field of client names. When I select a client, I want it to update a read only client address field within the form, just for display purposes.

Is this easy to do and can anyone point me towards some examples on how to achieve this???

Many thanks.

View 1 Replies View Related







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