MS ACCESS :: Need Query To Return Same Record Twice

Query To Return Record Set Where 1 Column Is Greater Than Another
Creating a query in Design View, how does one create a query in the "Condition" field that tells the Access to return the record set where the contents of one column is greater than the other?

Table Name=SURVEY_RAW_DATA
Column Name=TOTAL_EXP
Column Name=TOTAL_NX

There are obviously many more columns than those listed above in the table, but these are the two I want to compare. Also the two columns contain numbers.

In SQL I would write it like this:

Select * FROM SURVEY_RAW_DATA
WHERE TOTAL_EXP > TOTAL NX;

Thanks in advance,
Ken

View Replies ! View Related

Return New Record Id
In SQL Server :- if using the SQL INSERT INTOTbl VALUES ()
... the ID of the new record is returned . Is there a command in VBA to do the same?

I am using an SQL string as a parameter to Database_obj.Execute()

theObj.Execute "INSERT INTO myTable __ VALUES( ____ )

Any ideas please?

View Replies ! View Related

Need To Return Only The Maximum Record
My quiery is supposed to search through a table of names, and find the latest timesheet entry date for each name, as follows:

ID: 1
Name: John
Timesheetdate: 1/1/2005

ID is specific to each name.

I want the result to be a single row for each ID/name, and to return ONLY the latest timesheetdate (this could either be the maximum value or the latest entry).

I have a feeling that this is a simple problem, but i just dont have the knowledge to do it, any help would be greatly appreciated!

View Replies ! View Related

Requery And Return To The Same Record
I have a form in spreadsheet view. Basically all I want is to update the information which is viewed, but Form.ReCalc doesn't requery the database. So I tried Form.Requery. But after doing Form.Requery, the first record of the form becomes the active record, and in some cases I have to scroll to see the record that was active before the requery. Is there any way to avoid this?

One possible I solution might be to requery that form and return to the record which was active before the requery. Here comes another problem. The form has a subform in spreadsheet view (which is viewed by clicking + to the left of any record in the first form). If I expand an record to view the sub form, it is also expanded after the requery, but I also want the main form to scroll down so the subform is fully visible (or as visible as it was before the requery), or alternatively, scroll down so the top most record in the main form that is visible also is the top most visible record after the requery. If this is not possible, maybe there is a way to make the record which was the active record before the requery to be the top most visible record after the requery.

Could someone point me in the right direction? Give any suggestion?
Any help is appreciated.

View Replies ! View Related

Find A Specific Record To Return A Value
Having problems with the following function to goto a specific record and return a value from a field in that record.

Code:'Public Function intFieldZConv(strICAO As String) As IntegerPrivate Function intFieldZConv() As Integer 'TEST PURPOSE ONLYDim strICAO As String 'TEST PURPOSE ONLYstrICAO = "KTCM" 'TEST PURPOSE ONLYDim cnCurrent As ADODB.ConnectionDim rsFieldInfo As ADODB.RecordsetSet cnCurrent = CurrentProject.ConnectionSet rsFieldInfo = New ADODB.RecordsetrsFieldInfo.Open "tblFieldInfo", cnCurrent, , , adCmdTable'rsFieldInfo.Index = "FieldICAO"'rsFieldInfo.Seek "=", strICAOintFieldZConv = rsFieldInfo!FieldZConvSTMsgBox intFieldZConv 'TEST PURPOSE ONLYrsFieldInfo.ClosecnCurrent.CloseSet rsFieldInfo = NothingSet cnCurrent = NothingEnd Function

strICAO is received from another function which is to be used to determine the specific record in the table "tblFieldInfo". Once that record is referenced, the value from the field "FieldZConVST" is returned.

My problem area is highlighed (index and seek) which I receive an error stating "method or data member not found". Any help would be appreciated on how to finding a specific record.

Thanks

Jeff

View Replies ! View Related

Return Only One Record From Table With Repeating Values In Fields
I have a table like this

Stuff.
ItemNo Name ImageName
123 Foo 00123.jog
123 Foo 00FOO.jpg
123 FOO FOO123.jpg
456 bar 00456.jog
456 BAR 00BAR.jpg
456 Bar BAR456.jpg
...

I want to do a query to return just one row per unique ItemNo

So the query would return
ItemNo Name ImageName
123 Foo 00123.jog
456 bar 00456.jog

I don't care which one it grabs, the first is as good as the last, as they are essentially different names for the same thing coming from different systems.

I know there's gotta be an easy way to do this, but I've tried things like TOP, DISTINCT, etc and none of them work for me.

View Replies ! View Related

Trimming Query Return Value
HI, i have a field in a query called [cost_type], these typically contains the values "principle works" or "additional works" how can i set the query up so that it doesn't return the "works" part of the record? Could i just return the first 10 characters of the result?
thanks

View Replies ! View Related

Time Return Query
Hi Everyone,
I have a database that I enter time for in the form of military time, I would like to make a query to count the number of entrys in the PM,AM, And NIGHT. For instance if a record has a time of anywhere between 700 or 2299 I would like it to be in the AM catagory, then I would like to total up the number of AM records. Any help would be great. Thanks.

AM=700-1399 PM=1400-2299 NIGHT=2300-699

View Replies ! View Related

Query To Return Table Name As A Value?
Hello all,

Is there a SQL function in Access that will allow it to return (as a field value) the name of the table where a particular record was found?

The reason that I want to do this is because I have a single table (Table A) with building address records counted and broken down by state and by month - but some of the records belong to smaller groups (e.g. Zone 1, Zone 2, etc.) that aren't states, but that I want to count in the crosstab as if they were. There are building addresses on Table A that belong to each of these four groups, so I need to somehow match the records on Table A with the appropriate Zone, and place the name of that Zone in the "State" column of the query results instead of the actual state so that I can then count the results in the same crosstab.

I had the intention of resolving this by creating three tables (with the listings of address information for each Zone #), then matching records in Table A to each Zone table in a query by the address field to get each record's appropriate Zone no.(if available), then using the results of this query in my crosstab - :confused: but I can't figure out how to get Access to create a calculated field that lists from which table a resulting query record came from, so I can't continue.:confused:

Any suggestions, if able, would be appreciated!

View Replies ! View Related

Wrong Query Return
I placed ="G*" in a text field to get all names that begin with G and a blank return was issued. Maybe the manual I have has the wrong info. I have Access 2003 and a Access 2000 manual. Please help.

View Replies ! View Related

Query - Weird Decimals Return
I enter values into a table with 2 decimal places.

I have a query that applies percents to the values - I have set the results to have 2 decimal places.

I have another query that summarises data from the previous query, however the results I get are not right, the decimals differ from the actual result, if I make a sum of the values by hand.

It seems as if the query I have made to apply percents only DISPLAYS data with 2 decimals instead of actually rounding it, so that the other query summarises data with more than 2 decimals, and thats where the difference int the end result comes from. I don't know how to fix this however.

Can someone please help.

Thanks!

View Replies ! View Related

Query That Return No. Of Records In Run Time
Hi all,

May I know some to construct any functions or query string that can track the no. of records that mean certain criteria in the run time.

Basically I want to track the records in a subform (in datasheet view). Should the no. of records is 0, then I can disallow user from saving it onto the table for better record keeping.

Thanks !!

View Replies ! View Related

Blank Or Duplicate Return From Query?
I have searched for an answer and spent some hours but still not getting it.

I have played about with the query joins and still nothing?

I have never had this problem with my database in 5 years (well not that i have noticed).
Some records are fine others are triplicated and quite a lot are blank?

This is the SQL for the query.

SELECT Employee.EmployeeID, Employee.FirstName, Employee.Surname, Employee.Address, Employee.[Town/City], Employee.Region, Employee.DateofBirth, Employee.Nationality, Employee.MaritalStatus, Employee.PassportNo, Employee.ExpiryDate, Employee.Nextofkin, Employee.Relationship, Employee.NOKAddress, Employee.ContactNo, Certificate.CertName, EmployeeCert.Level, Trade.TradeName
FROM Certificate INNER JOIN (Trade INNER JOIN ((Employee INNER JOIN EmployeeCert ON Employee.EmployeeID = EmployeeCert.EmployeeID) INNER JOIN EmpTrade ON Employee.EmployeeID = EmpTrade.EmployeeID) ON Trade.TradeID = EmpTrade.TradeID) ON Certificate.CertID = EmployeeCert.CertID
WHERE (((Employee.EmployeeID)=[Forms]![Employee]![EmployeeID]) AND ((Certificate.CertName)="IRATA"));

Basically i have to solve this problem.

I have a report that is based on this query and if the employee is not displayed correctly instead of the employees Name and Surname i am getting "#Error" ?

Many thanks in advance

View Replies ! View Related

Query To Return Values That Are Not Equal
I have a query that uses a linked table from an external database and joins it with a table from my database to return records that contain the same values.

Is there a way to return only the records that are present in the external database and not in my db?

Thanks

View Replies ! View Related

Query Doesn't Return Any Fields
I created a database that contains 2 tables. I then created a query that simply groups the data and calculates a row total. And it works as expected. However, when I try to create a report, using the query, I get, "The wizard was unable to generate fields from the record source you chose. Perhaps you chose a query that doesn't return any fields."

What am I doing wrong?

Screencaps attached.

Thanks.

View Replies ! View Related

Query To Return Computers Without Allocation
I have 2 tables tblComputers & tblComputerAllocations
tblcomputers has 85 records & tblComputerAllocations has 71.

I would like a query that returns the 14 computers that have no allocations.

I tried a union query on field ComputerID but i must misunderstand its use!!
Thanks in advance,
Phil.

View Replies ! View Related

Query Parameter To Just Return Weekdays
I am trying to write a query that looks at groups of records for averages and maximums but the records are only from Mon-Fri (Trading Days). I have a query that runs everyday and compares today's close to see if it is greater than the maximum close of the last 20 trading days. I want to use between date()-21 and date()-1. BUT this will include weekends where there is no data.

QUESTION: Is there a way to say between the last 20 trading days(Mon-Fri)?

I researched dateadd() and found one place were it says that "w" is used for weekday. http://www.database-design-2000.com/dateadd.htm BUT when I did a simple query to see if it would exlude the weekends it doesn't. I will have no data for the weekends but if I am doing an average of the last three days and this is run on Monday, it will only include Monday and not the previous Thursday and Friday. Thanks for your help:)

View Replies ! View Related

Query To Return Intermediate Values
Hi All,

I need an Access query to return the rate at which a customer should be charged. A customer's rate can be set for a period of 1 to 6 months and the customer's rate table could look like this:

ACCOUNT_ID........START_DATE........END_DATE...... ..COST_PER_UNIT
31308.................01-Apr-07................30-Jun-07.................6.195
31308.................01-Jul-07.................31-Aug-07.................6.304
31308.................01-Sep-07................31-Mar-08.................5.422
31308.................01-Apr-08.................31-Aug-08.................6.304

I need a query (preferrably SQL) that will return the rate a customer should be charged each month, e.g.

31308 / Aug 07 / $6.304
31308 / Sep 07 / $5.422
31308 / Oct 07 / $5.422
etc, etc

I'm using Access 2003 and the rates table contains details of about 2500 customers.

Any ideas would be welcomed.

PS. Can someone tell me how to paste a table straight from Excel into the forum post body???

View Replies ! View Related

Return The Query Results On The Form
Hi,
I am not much familiar with VB Coding.Can any body please help me with this Please:When i click on submit button,it has to show the query results on the Form.Please find the below code:

Private Sub Command_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSql As String
Set db = CurrentDb
strSql = "SELECT Interface.[Interface ID], Interface.[Interface Name] from [Interface]"
Set rs = db.OpenRecordset(strSql)

Do While Not rs.EOF
Debug.Print ("Interface ID: " & rs![Interface ID] & "Interface Name: " & rs![Interface Name])
rs.MoveNext
Loop
rs.Close
db.Close
End Sub

#######################
It is not retrieving any thing.IF my code is wrong,Please advice me with the necessary steps to view the results on the form,when we clicl on the submit button.
Thanks,
Kanuri

View Replies ! View Related