TOP 5 QUERY
Is there a way to grab the top or 1st 5 records in a table? So, if I have a
table of salesperson, I want to sort by volume and grab the top X number of
On Jun 18, 10:36 am, Tim H <T...@discussions.microsoft.com> wrote:
> Is there a way to grab the top or 1st 5 records in a table? So, if I have a
> table of salesperson, I want to sort by volume and grab the top X number of
> records only.
Yup, just change the table and field names as appropriate.
SELECT TOP 5 *
ORDER BY sales_volume DESC
"Jason ...LINQ related query
I have a question regarding joining xml files on a key which is present in
all file, following is my code
docFTR is of type XDocument
xdList is List(Of XDocument)
Dim doc = From x In docFTR.Descendants("Data") From i In xdList.GetRange(0,
1) Join d In xdList(0).Descendants("Data") On x.Descendants("Guid").Value
i need to programmatically replace xdList(0) with the current XDcoument
which comes from "From i in xdList.getRange(0,2)"
GetRange(0, could be 2 to 10)
Any help is welcome...Database query won't open
I am trying to create a new database query from within Excel 2000. I was able
to do this successfully yesterday from the same PC. Today, when I go to Data,
Get External data, new database query, I get the hour glass and Excel then
locks up. Anyone have any ideas?
...how do I query the current Win logon id in a nonsecure Access db
I've seen this done. Querying for CurrentUser() returns Admin as expected.
But we need to capture the Windows logon id used by each MS Access user to
identify the records he is updating on a linked SQL Server database. I have
everything except how to capture his logon id as a field in my query. This
Access application does not need to be secured.
http://www.mvps.org/access/api/api0008.htm at "The Access Web"
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
"ann" <firstname.lastname@example.org...Query vs form with child & parent: Deleting rows
When I delete a row from a query involving both parent and child rows, what
determines which rows are deleted from the source tables?
When I display a joining query in datasheet mode and manually delete one of
the rows, only the record from the child table deleted, as I intended. But
when I display this query in a form and delete a row from there, it deletes
the source record from both the child and the parent tables. It doesn't seem
to make any difference whether the join is inner or outer, nor even whether I
display parent data in the form. I've been trying for a while to f...ODBC Failure In JOIN Query ??? (BUT....)
I have a query that is simply a combination of 3 other queries (joined by 1
common field in each).
Each of the 3 "sub queries" will execute fine, Individually....
But....that final query which simply combines them all will display an ODBC
I'd always assumed that if each individual query ran okay....the query which
combines them should not get such a data-access related error (since the core
data is already being successfully accessed).
Has anyone else experienced this and found a solution?
Thanks very much
Message posted via AccessMonster.com
http://www.a...Add sequential numbers in query
Is there a way to automatically add sequential numbers to a query where
there are multiple records for each primary key?
Cust# Name Phone# #in seq
Records: 1 test 5551212 1
1 test2 5552121 2
2 test3 5552222 1
Basically, I would like to automatically populate the #inseq field based on
the # of records per cust.
Many thatanks in advance.
Mary...stock quote query mal-function
i have been using the 'ms investor stock quotes' function in excel 2000 to
update my portfolio; it no longer functions rather gives me this message
"Unable to open http://investor.msn.com/external/excel/quotes.asp?SYMBOL=.
Cannot download the information you requested." was working fine before i
installed explorer 6 sp1 kb883939 security update - can anyone offer a
work-around to restore functionality, or a way to un-install the security
thanks for your assistance, jt.
See Jason's reply (first reply) in this thread
http://google.com/groups?threadm=1120...Previous Date Query
Hi everyone, I'm trying to create a SQL passthrough query or SQL view and
this ones driving me mad!
SQL Server 2000 for back end
Access 97 for front end
I have a table called Invoice, in which are the following fields
InvoiceNumber (Primary Key)
BookRef (indexed identifier for a client)
InvoiceDate (date of the Invoice)
InvoiceNumber, BookRef, InvoiceDate
10, 1, 31/08/2007
11, 2, 31/08/2007
12, 3, 31/08/2007
13, 1, 31/09/2007
14, 2, 31/09/2007
15, 3, 31/09/2007
16, 1, 31/10/2007
17, 2, 31/10/2007
18, 3, 31/10/2007
What I want to do is create a passthough query or view th...MS Query capabilities/regex
Hi all. New to Excel, but formerly a programmer. I've got Excel 2003
(?) and have begun using it to pull data from an external database. I
can't seem to find a good reference to the features/limitations of MS
Query. It appears a lot of material points to Access (which our IT guy
won't let me use). Also I'm stuck with using whatever s/w is on our
weird server - I can't add or upgrade any apps.
Currently I'm looking at a lot of ugly data stored as long strings,
pulling 'possible matches' with MS Query, and then using a VBscript in
a formula to regex the t...Query Formula (stones and pounds to kg's)
I have a table with 2 weight fields, stones and pounds.
Is there a formula that I could use to convert this into a single kg field.
I would be doing this in a query
There are many answers on google but they seem all to be for excel.
not perfectly clear but can't you just add the two converted values?
[Kg] field source
=[stone]* 6.35 + [Lbs] * 2.2
"Karren Lorr" wrote:
> I have a table with 2 weight fields, stones and pounds.
> Is there a formula that I could use to convert this into a single kg field.
> I would be doing this in a que...Report of sales by month based on a cross tab query
I have a cross tab query that will provide sales by month for 2008. Right
now it is sales for January08. As there are sales for future months, they
will appear as well. I want to create a table that has all of the months
listed out already. Right now, on the first day of the month, I go into the
report and add the new month. I want it so that all month are listed which I
have done. The problem is that when I run the report, I get an error that
says "the Microsoft Jet does not recognize February as a valid field name.
After today, there will be February data but no March ...Export Access Data Project Queries to old Access .mdb file
I want to copy Access Data Project queries from a project into a new .mdb
file. What is the best way to do this?
In the new .mdb go to the menu bar, click file, Get
external data, and Import.
Select the orignal .mdb file and then the items.
>I want to copy Access Data Project queries from a
project into a new .mdb
>file. What is the best way to do this?
...Getting External Data with Saved Query
I am using an excel 2000, and I set up the Microsoft's saved web query
"Microsoft Investor Stock Quotes.iqy" which ran smoothly, and I was able to
refresh the data on my worksheet until some months ago when it stopped
working. I have tried setting up a new one that still would not work.
Any idea how to get this working again? I have tried rebooting my machine,
still no luck.
Here is the message which I am getting each time I tried to refresh the
query: Unable to open
http://investor.msn.com/external/excel/quotes.asp?symbol=LU+ Cannot download
the information you re...Linking to exported tables and queries
I have office XP. The issue that I am having is when I
export a table from Access to Excel (and I'm usually
copying over an old file because it's a monthly macro). I
have an Excel file that links to the exported table. If I
try opening up the file with the links without opening the
exported table first I get an error "Unable to read file"
but if I open the file with the exported table first it's
okay. How do I fix that? Thanks.
Instead of using the File>Export command in Access, try using the
TransferSpreadsheet, to export the table to an existi...IIF stmt in query
I would like to return different results for different conditions more than
one field. here is my IIF stmt:
any help is greatly appreciated. Thanks
You can AND the conditions, but it would be preferable to use a table to
make the translations.
iif( type = "A" AND country = "USA", "FMYHF002",
iif( type = "A"...MS-Query code from EXCEL97 doesn't work in EXCEL2003 ??
I am using MS-Query to import data from SQLServer2000 to MS-EXCEL.
We recently upgraded to EXCEL2003 from EXCEL97 and now MS-Query don't
understand more "complicated" code
which worked without problems in EXCEL97.
The code below is an example of this problem. I understand that I can solve
this through building views in SQLServer but I would prefer not to because of
the number of views required.
What has happended to MS-Query in EXCEL2003 and is there an solution to this
available from Microsoft?
-----------------------------------------------------------------------------...DAO: query of multiple tables can not find
I try to use DAO to pull out two values from a query (qryTeacher). This query
is created by combining four tables that have inner join relationships. In
VBA, I wrote the codes as following (with all variables are already declared
strSQLTeacher = "SELECT StudID, email FROM qryTeacher WHERE
((qryTeacher.StudID)='" & varStudID & "');"
Set rstTeacher = Student_DB.OpenRecordset(strSQLTeacher)
I got the error message saying that "database engine cannot find the input
table or query, "qryTeacher". BTW, I use Windows Vista...Union Queries
To cut a long story short, i've got a union query that is a full outer join
of 2 tables.
Anyway, the results i have got back needed formatting to no decimal places.
How do i do that?
I've tried leaving it as it is and formatting it in the report, but it still
doesn't format and i can't total it up.
It's like it is showing as a text and not number.
Is there a way of converting the result to a number, particularly within a sql
Using the Format() function has the effect of turning the result into text.
Could you use the Round() function instead?
Allen Browne - M...Can't enter data in query datasheet
I have a query (involving multiple tables) that I want to execute and then
use to enter/change data via the datasheet which results from the query.
On most queries, I am able to do that. One this one, I can't. What is the
criteria for determining what queries will allow data entry and which queries
What do I have to do to this query to allow data entry?
SELECT [4GateMeetingTimes].Time, ActivityTbl.Activity_ActivityKey,
ActivityTbl.Activity_Pl...Microsoft Query #4
I receive message SQL0104 when changing a column heading
in Microsoft Query while using the Client Access ODBC
driver to connet to an iSeries 400 server. I have been
informed by IBM that this is a problem with Microsoft's
Query in that it incorrectly quotes the new Column Heading
name before sending it to the ODBC Driver . Is anyone
aware of a update or solution?
Thanks everyone, I just located the following Microsoft
Knowledge Base Article 298955, I followed the procedures
below, and the SQL0104 error went away.
1. Quit all Office programs..
2. Click Start, and ...Combo values from query based on form fields
I am setting the values for a combo box in a form(s) via a query that
'filters' the results with criteria based upon the values of other fields on
the form. The combo is a field that is bound. However, this is giving all
kinds of problems ranging from Access completely crashing to being asked for
the parameter values of those criteria fields when closing the form. I have
tried making the combo an unbound field and then setting the value of the
bound field to that unbound field after update, but that still leads to the
same issues. How can I do this?
As example - I have a form w...How to Run query based on txtbox value that is part of control source
I've created form using wizard.
Later on i created listbox where I want to show data based on my quiery
SELECT EmpEquipmentSkills.EmpNum, EquipmentTraining.Equipment,
FROM EquipmentTraining INNER JOIN EmpEquipmentSkills ON EquipmentTraining.
EqNo = EmpEquipmentSkills.EqNo
WHERE (((EmpEquipmentSkills.EmpNum)=[Forms]![Employee1]![txtEmpNum]) AND (
That is assigned to row source table/query type
And in VBA I have added Event :
Private Sub txtEmpNum_AfterUpdate()
But when I start for...BOM Query for multi-level BOMs
We've been trying to use the Basic BOM With Standard Cost report to get the
details needed to set standard costs on some of our finished goods. The BOMs
are several layers deep with some sub-assemblies set as Regular BOMs and some
set as Phantoms. The primary temp table used by the report doesn't provide
this information (whether the sub BOM is regular or phantom). I've tried
adding the BOM Line table but have not come up with a join that gets around
what the primary temp table shows.
So, is it possible to create a view/query that can essentially recreate an
exploded BO...reports and list box from table query
i created a report based on 3 tables. One of the tables is tied into a list
box/query to get the correct options. However, when i create the report...
the list box is also created on the report. With the correct selection
highlighted in black. This also shows the other possible options with a
I just want the selection to show up on the report.... would it make a
diffrence if it was a value list? the options hardly ever change, but there
are around 60 options.
Message posted via http://www.accessmonster.com
You should not use a listbox data-type in a tab...