Return Only One Record From Table With Repeating Values In Fields

Nov 7, 2006

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


ADVERTISEMENT

Newbie Question - Repeating Fields In Tables

Mar 1, 2006

Hello hello. I'm new here, and new to Access... I've just started taking Microsoft's Office Online training tutorials and I was wonder about planning tables and repetitive data.

I had transferred some stuff from Excel to Access but now I'm looking at redesigning my whole database using relationships, etc.

I'm mostly working with a mailing list, so my fields are:

Mr./Ms.
FirstName
LastName
Title
Company
Address
City
State
Zip

Okay. There is also a second list of contacts that will link to the Company field. But my question is, most of the contacts in this list have the same title (be it President or CEO or whatever...) -- there's only like four variations.

Would I make a separate table with just those four titles, so I don't have to repeatedly type "President" in the title field?

And then if so, what about things like Mr./Ms.? It's only two variations and every record needs one. And what about State? Again, only two states ever. These don't get their own little "related" table, do they? And if not, how do I avoid typing it every time??

Thank you so much for reading this, and thanks in advance for your help.

~Jas/LadyGrey

P.S. I have another major question too, actually. As I said, I'll have two different lists hinging on the Company name. Except I have some companies with multiple contacts... so I don't think I can make the Company the primary key, since it may be repeated for a few records. Help, please! Thanks!

View 2 Replies View Related

Repeating Information On New Record

May 25, 2007

hello,

I have a form, and I am on record "A". When clicking to add a new record "B" I want all the same information on record "A" to show on the new record "B".
How can I do this without using default?

Thanks

View 8 Replies View Related

Queries :: Return Last Record In A Table

Sep 23, 2014

My setup is simple, it consists of two tables:

Employee (one)
Position (many)

So an employee in this database can have many different positions. The tables are linked using an EmployeeID field.

I want to construct a query that will list each employee and the last job entered for them in the database. Right now my query simply returns all the positions held (where there are more than one)?

View 10 Replies View Related

Queries :: Return Records Between Dates Based On 2 Date Fields In A Table

Apr 24, 2013

I have a table which includes a start date field and completion date field for housebuilding.

I am trying to extract all records that have either a started date or a completed date between 2 dates supplied by the user. I have tried to use Between on both fields but that doesn't return results between the fields.

It workd if I just do it on EITHER the start date field OR the completion date field so that implies to me that I need to break it into 2 queries, one returning start date recrods and the other returning completion date records but then I would need to have somthing that removes records that appear in both the start date and the completion date results.

View 7 Replies View Related

Repeating Data From Prior Record

Mar 16, 2005

I have users who use a form to enter data. One of the fields is "DateRange". The users tend to enter records in groups. The group of 10 records will all have the same "DateRange" As they start a new record is there a way that the "DateRange" field can copy from the prior record?
Any help would be great!

View 8 Replies View Related

Repeating Macro Needs To Stop After Last Record

Nov 1, 2006

I am basically setting up a macro that will change the value for a field from "N" to "Y" for all records from a query. The macro works fine except for it does not know when to stop and gives an error message of "You can't assign a value to this object" after it has gone through all of the records and there are none left to change. The macro is set up like this:

OpenForm Action : This form is populated by the query and I set the Where condition to open records that don't already have a value of "Y".

SetValue Action : I use this to set the value of the field to "Y"

Close Action : I use this to close the form and save the changes

RunMacro Action : This is where the problem is. I use this to repeat the macro so it can go through the rest of the records that still have the field set to "N". I need to set the Condition or Repeat Expression so that it checks if all of the records have been changed. It either needs to check that all records have the field = "Y" or check that the macro is on the last record. Basically something that will cause the macro to not open itself again when there are no more records to be changed.

View 2 Replies View Related

Reports :: Chart Is Repeating For Every Record

Sep 22, 2014

My query has the fields setup ID, Date, Session, Value.In a report I am trying to create a bar chart that shows session names on the y axis and values along the x axis. I want to show 1 chart per ID.However, when I run chart wizard and create my chart I am getting charts for each ID repeating by the number of sessions (number of records in my query).So for example with 3 sessions im getting:

ID: 1 - Chart
ID: 1 - Chart
ID: 1 - Chart
ID: 2 - Chart
ID: 2 - Chart
ID: 2 - Chart
ID: 3 - Chart
etc.

Where the chart is setup the way I want it, but it repeats for each session.how I can get the report to show 1 chart per ID?I tried grouping by ID but didn't seem to work

View 3 Replies View Related

Modules & VBA :: Return Name Of A Record Source Table

Feb 19, 2014

I'm new to Access and VB but I managed to write parts of what I want access to do.By tweaking a code I found on the internet, I managed to write a small Subroutine that allows the user to click on one of the fields of a record in a report and then Access opens the form on that specific record.*My code goes something like this:*

Code:

Private Sub Edit_Click()
* Dim strWhere As String
* Dim DocName As String
* DocName = "FormName"
* strWhere = "[Field Name]='" & Field & "'"
* DoCmd.OpenForm DocName, acNormal, , strWhere
End Sub

Now I want to get the name of the Table where the record exists.So, let's say when I click on the "Field" it gets the name of the table where the record with that field exists and sets it in a variable.OR even better would be, get the name of the Form where that record exists but I guess that's a little more complicated since the record is directly linked to the table...*

View 14 Replies View Related

Queries :: Return Latest Record In Table

Feb 23, 2014

I'm trying to create in access2010

(1) a query that returns the latest record (newest) in a table called 'Invoices' and then

(2) places this value in a form called 'FrmInputInvoices' as the default value when the form opens. Newest record is by Autonumber and the table defaults this to top of table as views newest down to oldest.

Re (1) Query is called 'QInvoices'; the values I want to return in my query is ID (my autonumber) and Invoice_No . Must be a simple answer to put in the criteria, but I can't find this.

Re(2) What code do I use in my Form field named 'Invoice_No when the curser defaults there on opening?

View 4 Replies View Related

Tables :: Search Through Every Record And Return Result In Table

Jan 21, 2014

My boss has a form based on a rather large table with a lot of records/fields and she wants to be able to have a field where she can enter something and it will seach every record in the table and return the results in a table. How do I do this?

View 9 Replies View Related

Queries :: Correlated Query Returning Only 1 Record And Repeating

Aug 10, 2014

I have SQL query/dual sub-query in MS Access that is returning data from the left side of the query FROM correctly, but is only returning one record from the right side of the query FROM. Furthermore, it repeats the display of the one record and it repeats the entire results set with a different one record each time until all the records have been displayed. I expect that problems described as "Furthermore" will not exist by fixing the one record issue. I have tried using all the join types available in MS Access, but none change the result.

The desired output is:
Yellow Blue
11/23/2013 11/19/2013
11/19/2103 10/01/2012
10/01/2102 10/08/2010
10/08/2010 12/14/2007

The actual output is:
Yellow Blue
11/23/2013 11/19/2013
11/19/2103 11/19/2013
10/01/2102 11/19/2013
10/08/2010 11/19/2013
11/23/2013 10/01/2102
11/19/2103 10/01/2102
10/01/2102 10/01/2102
10/08/2010 10/01/2102

The same pattern is repeated 2 more times with Blue values of 10/08/2010 and then 12/14/2007.

Here is the SQL:

SELECT Long_List.Yellow,Short_List.Blue
FROM
(
SELECT DISTINCT BirthDate AS Blue
FROM (
SELECT DISTINCT BirthDate FROM citizens
UNION

[Code] ....

View 11 Replies View Related

Reference A Fields Values In The Record Source Query

Dec 30, 2007

I have a query on a form that is providing all of the information I need for everything on my form.

The problem is I don't know how to refer to a field's value in the query in VBA without having a hidden text field on the form.

I know I can reference a combo box's query to include criteria such as [Forms]![cboSelectStudent] if the control is actually on the form, but how would I refer to a value in the form's query that doesn't have a control on the form.

I've been searching for everything I can think to call this but haven't been able to come up with anything.

Thanks.

View 6 Replies View Related

Reports :: Several Fields In Report - Counting Record Values

May 10, 2014

Suppose we have a report that outlines several fields, one field shows the City. In the report, this week, we see 10 records "New York", 8 records "Houston", 7 records "London", 3 records "Paris" and so on.

Next week's report outlines different cities and different number of records. I need to have in the report footer a "recapitulation" , a field that would say :

New York 10
Houston 8
London 7
Paris 3

Total 28

Next week cities and number of records might not be the same, we may have

Tokyo 12
Singapore 14
New York 6
London 7

Can i make my report in that way that it will count the values without using VBA ? I tried the count values option but it counts the overall report, does not take into consideration the different values.

View 2 Replies View Related

Copying Values Of Fields From One Table To Another.

Dec 14, 2005

Ok so here is the problem I need help with,
I have a prices table that stores all the different price changes for each part I have in my Parts table what I need to figure out how to do is to copy the "Last" price for that part from the price table into the Price field of the Parts table or have some way of updating it automatically either by macro or what not - example:

Price Table:

PriceID (PKey)
PartID (Connected to Part Number ID)
Price
Price Added( Date & Time when price was added so I can groupby "Last" or "Max" in the Price Query I have made)

------------------------

Parts Table:

PartID (Pkey)
Part Number
Price

-------------

Now how do I take the value my Price query gives me for "Last" or "Max" Price using the last date for that parts price and have it automatically get copied into the Parts Table price?

Thanx

View 3 Replies View Related

Compare 2 Values From Fields On A Table

Mar 6, 2006

Hi there

This is probably quite easy but I cannot make it work so would appreciate some help. I have a table containing vehicle information. I have 2 fields, Current Mileage and Max Mileage. I want to do a comparison of the 2 fields and should the Current Mileage be greater than the Max Mileage, the query returns the Vehicle Registration. Test data has been entered in the table I am querying and I was expecting 1 value to be returned. However the query as I wrote returned either all the vehicle Registrations or none of them. Can someone help with this please.

Thanks in advance for the assistance
Craig:)

View 2 Replies View Related

Adding Value In Table Based On Values In Two Other Fields

Dec 3, 2007

Hello: Is there (simple) way that I can populate a field based on the values of two other fields? For example, in a list of new employees, if a new employee starts on Nov 30 and works in the Administration Department, his human resources contact is Mary. If a new employee starts on Nov 30 and works in Sales, his contact would be Fred. I hope this is clear! Many thanks!

View 12 Replies View Related

How Do I Read Input Values From Fields In Another Table?

Dec 10, 2007

Hi,

I have a query which uses manual input on two fields. I would like to automate this query by pulling the input values from another table.

How do I read the input values from the other table instead of having the users type them in ?

Al

View 1 Replies View Related

Help With Linking Calculated Fields To Table Values

Aug 28, 2006

I need to display a value from a table in a text field on a form based on another text field on the same form.

The table has records as follows:

Month/Year xx Month No xx Year No xx Month ID (xx shows break in columns)
Jan-06 xx 1 xx 2006 xx 24
Feb-06 xx 2 xx 2006 xx 25
Mar-06 xx 3 xx 2006 xx 26
Apr-06 xx 4 xx 2006 xx 27
May-06 xx 5 xx 2006 xx 28
Jun-06 xx 6 xx 2006 xx 29
Jul-06 xx 7 xx 2006 xx 30

In one text field I use the DateSerial function to calculate the value of the previous month (e.g.Jul-06). I need to return the MonthID value to the second text field based on the value stored against it is the table, so in case of Jul-06 it would show 30 in text field 2.

View 1 Replies View Related

Modules & VBA :: How To Check If A Table Contains Blank Fields / Values

Feb 28, 2014

How can I check if a table contains blank fields/values.

If there are blank fields I want to replace them with 0.

View 4 Replies View Related

Forms :: Matching Values Of Fields With Data In The Table

Oct 20, 2014

I have created a login form and create user page form, the tblListofUsers has two columns, UserName and Password, on the log in form when a user enters username and password, it should be matched with the tbllistofusers if true then msgbox"Welcome" else Msgbox "wrong username or password", I have written following code but gives error

Private Sub Command5_Click()
If DLookup("UserName", "tblListOfUsers", "UserName = " & Forms![loginpageForm]!User) And DLookup("Password", "tblListOfUsers", "Password = " & Forms![loginpageForm]!passworduser) Then
MsgBox "You welcome"
Else
MsgBox "Wrong username or password"
End If
End Sub

View 8 Replies View Related

Return Different Values......

Jan 29, 2007

Hi.
I have 2 queries, one that returns all the project numbers in the db (say 50 projects), and one that returns some of the project numbers (say 30 projects)

I can make a query that combines the 2 queries, and it returns project numbers that are in both queries (30 projects), but I would like the query to return the 20 projects that are in first query, but NOT in the second query.......does this make sense?

I am sure this is fairly easy.......it has just got me stumped!!

Many Thanks.

Frank.

View 2 Replies View Related

Query To Return Values That Are Not Equal

May 25, 2006

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

How To Return Values From Multiple IDs On The Same Line

Sep 12, 2007

I'm working on a freebie for a day care center.

I can not seem to work this out in a single query.

I have:
tblChildren
tblParents
tblGuardians

Because so many familes that use day care are fractured, it is possible for 1 parent to have multiple kids but some of them to have a different second parent.

Therefore each parent and each guardian has thier own line and own unique ID in their respective tables.

each child in tblChildren can have up to two Parent IDs (Parent_ID_1 & Parent_ID_2) and up to two Guardian IDs (Guardian_ID_1 & Guardian_ID_2)
I've set the data entry form to require at least one parent for each child, but there may not be any 2nd parent or any guardians.

So what I now need to do is create a query, or series of queries, that results with each child on it's own line, with the name of each parent, and each guardian in thier respective fields.

Has anybody got any guidance for me please?

View 1 Replies View Related

Query To Return Intermediate Values

Dec 10, 2007

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

Queries :: Return Values From A List

Feb 12, 2014

I have two tables, TBL_Students and TBL_Email. Each of these tables have a field called Category, which allows multiple values from a list.

Is there a way to return records from TBL_Students where at least one value in TBL_Students!Category = at least one value in TBL_Email!Category.

E.g. If Student A has categories Maths, Physics and Computing;Student B has categories Maths, English and History;Student C has categories Physics and Geography

I would expect:
when TBL_Email!Category = Maths,Physics : Students A,B,C to be returned
when TBL_Email!Category = Maths : Students A,B to be returned
when TBL_Email!Category = Physics,Geography : Students A,C to be returned

View 2 Replies View Related







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