SELECT Row N FROM TABLE

Sep 20, 2006

Hello,
I am trying to familiarise myself with MS Access and have the following problem:
Lets say I have a Table A with two columns as follows:
Name Total
Tom 1000
Mary 200
Brigid 150
Niamh 75

My question is how can I - using SQL only - select the nth, ie 3rd or 4th row?

I have spent some time on this and it irks me becasue it is probably very simple.
Any help or advice will be gratefully received.
Best Regards,
Tony

View Replies


ADVERTISEMENT

Insert Records Into Table Based On The Select Criteria From The First Table.

Mar 22, 2005

Hi

I have a small database with 4 tables that I am using for the current problem.
The tables are call, parents, mailman, orders.
Call and parents are related by the call ID (a primary key in the Call table.).
Mailman and orders are related by a Unique Id (a primary key in the mailman table.).

Forms involved are frmmain and frmsub.

Frmmain contains the call table information in the main form and parents information in the subform.

When a user enters a call with call ID and enters the operator name and parents information in the sub form,
When a user clicks the OK button on the main form, necessary changes should take place
if they enter the case type in the sub form part of parent information as ‘missing information’ or ‘missing link’ then the parent information with fields first name, lastname, case type, operator information should be inserted into mailman table in appropriate fields.

Simultaneously a record should be inserted into orders( after the record is first inserted into mailman, since both tables are linked with unique id) with the following information.
Orderid being autonumber.
Uniqueid from the mailman table.
Orderdate system date.
Ordertype should be “Mailman”

View 4 Replies View Related

Reports :: Select Most Recent Record From A Table That Is Linked To Another Table

Jul 13, 2014

I have a basic database design, well I think so anyway. It only has two tables:

1. tblClientInfo
2. tblNotes

Basically each client has multiple notes/comments that can be linked to its record, hence the tblNotes table. The two tables have a one-to-many relationship, being that each customer can have many notes.

I then have two forms:

1. Claims Loss Form
2. tblNotes_DatasheetSub1

So I can enter multiple notes for each customer. The problem I am having is with the report output. It wants to print every note (record) that is linked to the customer. I just need it to print the most recent note for each customer (It would save a lot of wasted paper).

Example of a print out of what it is doing:

John Doe - 123 Easy St - Notes 1 (Most Recent Note)
John Doe - 123 Easy St - Notes 2 (Previous Note)
John Doe - 123 Easy St - Notes 3 (and so on...)

It is printing duplicates of the same customer by adding the additional notes for that same customer on a new line.

How can I tell it to only print the customer one time in the report, and most importantly, to only use the most recent note that is linked to the customer?

I tried using DMax("NoteDate","tblNotes"), but this only returns one customer with one note. I need it to do that for each and all customers.

I have successfully ran a subquery (two queries with one linked to another) by following detailed instructions from this page I found: [URL] .... It works, but the problem is it only shows the latest date for each note, not the actual contents of the note. I feel so close with this option, but so far at the same time.

The TOP n records per group looks promising that I found here: [URL] ...., but I honestly don't know how to implement it correctly in my SQL. I am very much still learning Access and apparently have stumbled into something that is much more complicated than I had originally imagined. I just assumed I could filter the duplicates out, or tell access to print the last or most recent note record for each customer.

View 2 Replies View Related

Select Records From One Table That Does Not Exist In Another Table

Dec 11, 2006

Hi Guys!

Need help in putting up SQL string.

I have two tables. The first one contains customer information. Primary key is custno. The second one contains customer logins, primary key is also custno.

What I want to do is to view customer information that does not have customer logins.

Is it possible to do in MS Access 2003?

Regards,
Aga

View 7 Replies View Related

Select Records In Table 1 That Are Not In Table 2

Oct 3, 2007

I have two tables [Reviews] and [Results].

When a Review has a Status = 'Not Accepted' then there should be one or more records in [Results] that indicate what the result of the review was.

I want to structure a query to perform a data integrity check to select any Review ID that satisfies the following criteria:
- The Review Status = 'Not Accepted'
- There are zero records in [Results] for that ReviewID

I can't figure out how to do it.

Here is an example of the tables where all record are OK...

[Reviews]
ID...Status
1....Accepted
2....Not Accepted
3....Accepted
4....Not Accepted
5....Not Accepted
6....Accepted

[Results]
ReviewID....ResultID
2................5
2................6
4................3
5................3
5................7

Here is how [Results] would look if there is a data integrity problem, where the results for Review 5 are missing....

[Results]
ReviewID....ResultID
2................5
2................6
4................3

So I want to structure a query that would return the following...

ReviewID
5

Because this is a Review with Status = 'Not Accepted' but there is no corresponding record(s) in [Results].

I guess in plain English it would be like this...

SELECT ReviewID
FROM [Reviews]
WHERE (ReviewID is in [Reviews]) AND (ReviewID is NOT in [Results])

Can this be done in a single SQL query?

View 10 Replies View Related

Select Only First Two Characters From Table

Dec 5, 2005

I have a table which contains a nine digit number. How do I write a query to limit the restults to just the first two digits of that nine digit number?

Thanks

View 2 Replies View Related

How To Let User To Select A Table To Run?

Aug 17, 2007

Query
SELECT s.contracttypename, sum(s.sumrtr*v.pct) AS [predicted $]
FROM sumrtr AS s, varcurve1 AS v
WHERE s.mdiff=v.monthodr and s.contracttypename=v.contracttypename
GROUP BY s.contracttypename;


I have varcurve1 varcurve2 varcurve3....
I want to provide a mean (interface?) for user to choose varcurve1 varcurve2 varcurve3 to run above query.

for eample if user choose varcurve1
the query will run

SELECT s.contracttypename, sum(s.sumrtr*v.pct) AS [predicted $]
FROM sumrtr AS s, varcurve1 AS v
WHERE s.mdiff=v.monthodr and s.contracttypename=v.contracttypename
GROUP BY s.contracttypename;

if user choose varcurve2
the query will run

SELECT s.contracttypename, sum(s.sumrtr*v.pct) AS [predicted $]
FROM sumrtr AS s, varcurve2 AS v
WHERE s.mdiff=v.monthodr and s.contracttypename=v.contracttypename
GROUP BY s.contracttypename;



is there a way to do that?

Thanks

View 1 Replies View Related

Table Select In Form

Mar 17, 2005

Hi in the attached database there is a form called "with table select".

I want to be able to select any one of the three tables i have in the top combo box. When this is done the bottom 2 combo boxes will display the relevant data if needed (look at the other form "myform" to see the fields that are requseted).

Is this possible? :confused:

Thanks

View 1 Replies View Related

Select Last 4 Rows Of A Table

Dec 17, 2004

Is there any way to get the last 4 rows which have been entered into the table?

Thank You.

View 2 Replies View Related

Select And Update Another Table.

Jun 27, 2005

Hey All!!
This is what I wanna get done. I have a big table I am querying into and extracting data and populating it into the new tables that I have constructed.

For eg:
SELECT dbo_analyst.anls_id, [fst_name] & [lst_name] AS Analyst
FROM dbo_analyst;

This query selects the name and the ID of the Analyst out of the big table. I want to store these values in the Analyst table that I have made. Do i need to combine an Insert or an update query with the select query to simultaneously put the values in the Analyst table.
Please help me on how to go about with this.

Thanks
Mo

View 1 Replies View Related

Select Specific Table To Relink

Jun 21, 2005

I'm trying to use code to relink the the table but that is not the only table that is linked in the DB. The other linked tables are in other databases so I want to select specific tables to relink as the others may not need it. I alway will want to select the path.

here is an example of the code I'm using I got it from one of the other users here.

View 14 Replies View Related

Create Table AS Select Query

Oct 25, 2007

Hello all,

Is there anyway to create a table from a query like you can in oracle?
i.e.

Create Table [B_match] AS
SELECT *
FROM [B_All]

Take care

View 2 Replies View Related

Select Record From Table For A Report If...

Sep 7, 2004

Hello! I am building a report that tracks excessive call-in's for my hospital's employees. Data is entered into a form which then is stored in the table (duh!) "Call-In's." I have a query that lists all those who have more than 5 call-in's.

Here is the problem: In order to get the sum of callin's per person, I did a totals field in my original query and counted on Name. But now I needed to know the sum of each person's, and so I had to do a cross-tab query that included the name, department, the count (from the first query) and then I summed on the count field. What a mess, right? Also, I added a field for If >5, Yes, No.

I based my Excessive call-in's report on the final (above) query. Like this:

Call In's table
Name, Department, Date of Call in, Call in type

Call-in's query
Name, Department, Date of Call in, Call in type, Count of Name

Call-in's crosstab
Rows: Name (GROUP BY), Department (GROUP BY)
Columns: Count (SUM) and total of Count (SUM)

Excessive Call-In's query
Name, Total of count, iif >5, Yes, No

There are already 54 records in the table, one of which has excessive call in's (more than 5).

Is there a way to run a report that not only lists the person's name and number of call in's but also the date of each one and the call in type? Both of these are fields in the call-in's table.

When I try to combine my Call-In's query with my Excessive Call In's query I get the matrix. Example: I get the same record for however many callin's they have. Thank for your help. I'm so sorry this is complicated. I've got to be doing something wrong, or at least inefficiently. Does any of this make sense? Thanks again.

Kelly (the clueless, but I'm trying)

View 3 Replies View Related

Table Select In A Form Combo Box

Mar 17, 2005

I have a databse which uses a query and a form to filter records from 1 table into a report. It works well, however the way in which it works currently means i would need around 15 forms as there are (going to be) around 15 tables.

What I would like to do is add a combo box at the very top of the form to select the source table for the other combo boxes on the form (hope this makes sense!!).

This link is a test version of the database (Access 2000):

View 5 Replies View Related

General :: Select A Record From Table

Jun 6, 2013

I am trying to run a sql statement in vba in which it selects all the records in a table related to a order Id number. I then want it loop through all the records and check for a check box which is selected. if its selected then "A" variable becomes "1" and i will then run a if statement saying if variable is 1 then run this code else run code b.

The problem I am having is that if there are 4 records the code checks through all of the 4 records and keeps changing the variable so

record 1 variable is 0
record 2 var is 0
" " " 1
" " " is 0

so although the records for a given order number does have a checked box the code wont recognise that.

What I can do or what code I can add my minds gone all blank on this.

View 2 Replies View Related

Modules & VBA :: Drop Down Select From Another Table

Jul 7, 2015

I had 3 drop down filters that worked when the word being filtered was written in the table. I have since then linked them to new tables to be more database-y. The problem I have found (a couple weeks later too) is that my drop downs broke.

It makes sense why, they are now 1,2,3,4 instead of words but how do I make this work now that it isn't in the current table?

Code:
Private Sub cboMDS_AfterUpdate()
Dim myPlane As String
If Me.cboMDS = 0 Then
myPlane = "Select * from Baseline"

[Code] ....

View 10 Replies View Related

Using A Combobox On A Form To Select Table

Jan 23, 2014

I am trying to use one form to manage multiple unrelated tables that share the same field names (i.e. SKU, Name, Description) . My goal is to utilize a combobox or listbox to choose which table the data is sent to.

View 5 Replies View Related

Select Distinct From Multiple Columns In Same Table

Sep 7, 2006

I am trying to write a query that will select distinct values from three columns. The table name is Hoods. The columns are C1, C2, and C3. All three columns contain colors and can have the same values, but I only need to have a particular color selected once. For instance, all three columns contain "Black", but "Black" should only be listed once. The result of the query will be used in a drop down list.

The following works for one column:

SELECT DISTINCT [Hoods].[C1]
FROM Hoods
WHERE C1<>"";

Are there any suggestions?

Thanks

View 2 Replies View Related

Can You INSERT INTO Table With SELECT.. UNION Queries

Mar 29, 2005

I have a UNION of two SELECT queries. It works very well but I need the end-user to be able to modify the data so I am using INSERT INTO tablename. This will work for one query at a time but if I try to use the UNION it reports “Syntax error in FROM clause”. Can you use UNION in this case or can anyone see my error?



The full statement is rather complex, I have added a few spaces and linebreaks to make it readable as follows:

INSERT INTO TableCompleteForMailing

SELECT [Address List].[FamilySurname] AS Surname,

[Address List].[DEARFirstnames] AS FirstName,

[Address List].[Mailing] AS Mailing,

[Address List].[Christmas Mailing] AS OtherMailing, [Address List].[Address 1] AS Address1,

[Address List].[Postcode] AS Postcode

FROM [Address List]

UNION

SELECT [Names].[LastName] AS Surname,

[Names].[FirstName] AS FirstName,

[Names].[MailingList] AS Mailing,

[Names].[Selected] AS OtherMailing,

[Address List].[Address 1] AS Address1,

[Address List].[Postcode] AS Postcode

FROM [Names],[Address List]

WHERE [Names].[AddressListID]=[Address List].[AddressListID]

ORDER BY Surname, FirstName;

View 3 Replies View Related

Select Specific Sectors From Table Field

Nov 14, 2005

Hi

I need to select from a table field of upto 50 characters: the first 15 characters and then the remaining charachters (i.e. after 15...)

I would much apreciate some help...

Thanx

View 9 Replies View Related

Select Query Using A Lookup Table As Criteria

Aug 29, 2007

Using MS Access 2002

Need some help trying to filter out some data so it can be deleted from the main table so updated data can be pasted into it that table.

Table name is "dbo_VG_PropertyValues"

AppID - Number
DNIS - Text (This is the column i need filtered)
PropertyName - Text
ProperetyValue - Text

I had a query where i would just type in the 3-4 DNIS numbers that i needed to search for by using "11111" or "22222" or... etc... in the simple query view under criteria..

I'm now getting lists of 50 + numbers i need to do searches for, delete and replace with updated data. I can't run a string of "or" statements on 50+ numbers, it won't allow it. I created a table called 'DNISList" with the 50+ numbers i need to search for out of the "dbo_VG_PropertyValues" table. In the simple query view i linked on the DNIS number from both tables and set the join property to show all the records that matched. Hey it looks good, so far, i got the results i wanted but can't delete anything.

Is there anyway in the criteria field under the column DNIS from "dbo_VG_PropertyValues" where i can so a search on that table without linking the tables? I hope I'm making sense. You know something like:

IN ([DNISList]![TFN])

View 3 Replies View Related

Modules & VBA :: Report With Only Select Records In Table?

Dec 17, 2014

I have a master table located on the backend of the server with about 3 fields:

Software
Version
Description

I have 4 databases, one for each software on the front end accessing this master table.

So far I have in the master table a dropdown to select 1 of the 4 software's, the version number and a brief description.

One the front end I have a button that when pressed will bring up the report based on the master table.

Can I have the report just select the software that it refers to. For instance if the datebase is Sony when I click on the report it only grabs the records with Sony on it. For my next database when I open up Sharp's database it only makes a report for the Sharp records.

How would I do this? Is this something I have to code or something I can do in criteria or do I create a query?

View 2 Replies View Related

Queries :: Query To Select Part Or All Of A Table?

Nov 27, 2013

I need to give the user the ability to select either all of a table say

[tblStores].StoreNo

or only those stores where [tblStores].StoreNo Is Null

I cant for the life of me get my query to accept Null or Is Not Null in the parameter box.

What do I need to do.The end game is to produce a subform to allow users to maintain tblStores

eg

tblStore
StoreNo | StoreNm
000001 | StoreA
<Empty>| StoreB
000003 | StoreC

Null returns

Store B

Is Not Null returns

StoreA
StoreB
StoreC

View 2 Replies View Related

Modules & VBA :: Table Relationships And Select Statements

May 8, 2015

I have coded a select statement that uses multiple tables for information used to populate a report. It returns the correct information, sort of, but it doesn't recognize the already existing table relationships so I get dupicate records. Is there a way to run the code so that it recognizes the table relationships?

View 1 Replies View Related

Select From Drop Down To Populate Three Fields In A Table?

Nov 6, 2012

I would like to be able to select a value from a drop-down and by doing so be able to populate three fields in a table.

View 12 Replies View Related

Form - Record Source - Select A Table

Nov 13, 2013

Form - record source - select a table that when the user enter a value in the field of the form it gets place into the designated table (select a table). my problem is that I have two fields in the form, one field from the data entry is suppose to go to the train table,and another field the data entry is suppose to go to the station table. if I have the form's record source have a designated table for the data entry.It doesn't have two tables for the data entry.

View 13 Replies View Related







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