show only the first Entry from Field1 but all from Field2

Hi there I hope one of you guys can help me!

I have a Table or Query and it contains data as below

BBBB____| 5555
BBBB____| 9999

How in a form can I display the data as below not using cascading combi boxes but using Text Boxes, the one for FIELD_1 will show only one entry per record but the Text Box for FIELD_2 will show up to 20 entries.

1st Record

2nd Record

I hope you understand what I mean, I just do not want the data in FIELD_1 repeated for every entry in FIELD_2


Post your answer or comment

comments powered by Disqus
How can I get only the first page of a long report of data to print out?

Is there anyway to make a query that will only show the first 300 records from a record of 3000 ?

So right now I only have 1 unique no which is the item no

Help is trully appreciated

I have many reports that my bosses want to see the details for, but when they click the printer icon, they only ever want the first page.

What can I do to display all data, but force only the first page to print?

Thanks so much,


I want to calculate the total number of records in a report and place the value in a label (or text box) in only the first of five group footers in the report. In other words, I want to have a line in the first group footer that says "GRAND TOTAL: 100" and have the line invisible in the subsequent group footers. Piece of cake if I place the line in the report footer, but that won't fit my needs. I am successfully placing this line in all group footers, but don't know how to suppress them in group footers subsequent to the first one.

Any ideas??

Dear Access Guru's

I have a little table of records, I wish to display the last entry recieved from each supplier. The table has four fields:


how do I create a query where the last entries are shown but not any nulls.


I have a report which pulls the current form's policy number off and sends the data to the report. Sometimes this form will bring several instances of a policy number with, so the report will want to print 4 pages. I only the first 2 pages of the report. So is there anyway to tell command to only print the first two pages? Let me know if this doesn't make sense. Help is always appreciated...


Hello everyone

I am wanting to pull out only the month part from a date field, 11/10/2005.

In the query I have made an expression to pull the month, eg 10 for October.

I need to convert this number of the month to the Title of the Month.

I made a combo box on the report based on the Expression and in the row source put

1,"January", 2,"February",3, "March", 4,"Apri", 5,"May", 6,"June", 7,"July", 8,"August", 9, "September", 10, "October", 11, "November", 12, "December"

Unfortunately it still comes out with the number of the month.

Can anyone tell me please where I am going wrong.

Thank You in advance.

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?

I have an add form which is supposed to take the info input into a form and then add it to an existing table.

Here is the code that I've used:

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("tbl06ButamerAllLines", dbOpenDynaset, dbSeeChanges)

With rs
rs!UniqueID = Me.txtID
rs!EquipmentNo = Me.txtEquipment
rs!Service = Me.txtService
rs!HydrocarbonsPresent = Me.txtHC
rs!AdditionalAccess = Me.txtAccess
rs!InstrumentType = Me.txtInstrument
rs!ImpulseLineMaterial = Me.txtMaterial
rs!Lagged = Me.txtLag
rs!Traced = Me.txtTraced
rs!DateOfInspection = Me.txtDate
rs!DueDate = Me.txtNewDate
rs!Comments = Me.txtComments
rs!MatActionDueDate = Me.txtMatDate
rs!InsActionDueDate = Me.txtInsDate
rs!TransActionDueDate = Me.txtTransDate
rs!TraceActionDueDate = Me.txtTraceDate
rs!AdditionalAccessToBeInstalled = Me.txtAccess

'Tells the program to add the action dependent on the material
If Me.txtMaterial = "Carbon Steel" Then
rs!MaterialAction = Me.txtCMatRef
rs!MaterialAction = Me.txtSMatRef
End If

If Me.txtMaterial = "Carbon Steel" Then
rs!InsulationAction = Me.txtCInsRef
rs!InsulationAction = Me.txtSInsRef
End If

If Me.txtMaterial = "Carbon Steel" Then
rs!TransitionAction = Me.txtCTransRef
rs!TransitionAction = Me.txtSTransRef
End If

If Me.txtMaterial = "Carbon Steel" Then
rs!TracingAction = Me.txtCTraceRef
rs!TracingAction = Me.txtSTraceRef
End If


End With

What's happening is only the first record on my form is being edited. How do I get it to only update at a certain point, this case being "Unique ID", essentially overwriting all previous info at a certain common point?

I have a query which is using groupings and I need to show the first but for 2 fields and then show the rest of the query result.

The only way I can think so far is to concatenate the 2 fields then use first one the expression, is there another way? as I am not so sure this is a good solution.

Field names are:

Policy Ref and MI Ref

All data comes from 4 different systems these 2 fields will have the common data which I want to show only the first.

Any suggestions would greatly recieved.


Each month i get several text files which i want to load into a table in access via VB. Atm this is a slow process so would like to automate this with VB, by combining the text files together then exporting into the database. I know the DOS command to combine the files

copy filename1.txt +filename2.txt +filename3.txt file.txt

but this includes all the headers and i only wish to keep the headers of the first text document. Anyone know a method of deleting the first line from all but the first text file?

Many Thanks

I have some data which has multiple entries where all but one or two columns are the same, so my columns are for example ProductNo,Price, and Category, there are multiple entries for a single ProductNo where the only difference is the category. I want to write a query which returns all of the data for a given ProductNo but only returns the first occurence for each ProductNo.

Hi all,

I'm pretty new in Access 2007 and I've just created a table that needs to get data from a form (That I also created).

So, I open the form for the first time, entered a few data (I've used the "record Operations" --> "Add new Record" & "Save Record" buttons) and saved/stored them in the table with no troubles....

But that was only the first time... the second time on, When I open the form, it shows me the first record. I need that not to happen because when I write the data I need to put in the form and save it, it saves it as the first record... It always overwrites it... the other data that I enter, get into the table with no troubles, but this first record after reponening a form always deletes/overwirtes the first table record.

Could you tell me how to avoid this?

Thanks in advance


I have a form where I want to show all the selected values from a listbox bound to a query.

I have one table (tListboxValues) containing the values of the listbox with the fields ID and Values

I have another table (tVendors) in which there is a multiselect listbox linked to the the table tListboxValues (only listbox IDs is stored in tVendors).

Now on my form I want to put a listbox showing only the checked values form the table. How is this possible?

Is there a standard way to setup access to do this or do I have to write a macro to solve the problem?

I have tried a lot of different queries for this but cant seem to get it right. Is there anyone who can help me with this one?

Best regards

Hi there Everyone! Althought I still did not get any answer to my previous question (post: I am already having another problem.

I have a list from which I can select the records. With the help of the VBA the fields fields on the form populate different data belonging to that record.

My problem is when there are multiple data belonging to the same record. Please see the file attached. I have three tables: Risk, Assessment and Controls. There is a list box on the left which contains the risks. When I click on one of the risks the Assessment tab displays the impact and likelihood amounts belonging to that record (VBA code). But one risk can have many controls. And here is my problem. On the Controls tab I have a query displayed which shows the control points. This subform shows all the Controls, however I would like to show only those which are belonging to the selected risk on the listbox. How could I do that?
Thank you in advance and have a nice weekend.

Morning ladies & gents..

Got a pretty simple question. I think.
I have a report that shows status entries from the last 7 days. The idea when this was made is that it SHOULD show 1 entry per project assuming the employee entered the status on the right day. Which doesn't always happen. So I want to change the report to show the last status entered for a specific project - regardless of the date. Attached are some pictures of the query that generates the report. As you can see, most of the data is from one table, and only one field has a condition on it. Not sure which field would be the best to filter to show only the most recent entry. Each status entry has it's own unique ID.

too long, didn't read version:
I need to change a query
from: show entry from last 7 days
to: show only the most recent entry per project Attached Thumbnails     Reply With Quote 10-13-2010, 08:23 AM #2 weekend00 I may not be right Windows XP Access 2003 Join Date Aug 2010 Posts 1,296 create a sub query to get most recentely project id, the show the project with this id. something like:
query1: maxdate
select projectID,max(pdate) as maxpDate from project group by projectID
select project.* from project inner join maxdate on project.projectID=maxdate.projectID and project.pdate=maxdate.maxpdate

Hello, we have an access application (quote maintenance forms and a SQL database) that we developed and have been using for approximately 9 years. At this point in time, the main/parent file has 25,781 records in it. Until recently, everything was fine. On the form we have a "goto quote" field to allow the user to position them at the quote that they wish to work with. The first time you open the form and use the "goto quote" number field, if a quote number is entered that is really a record number in the file greater than 25,600 - the findfirst displays an arbitray record that is in the group of records from 1 thru 25,600 (not quote number, but a record that is in the first 25,600 records in the file). Second and subsequent use of the "goto quote" field, using the same findfirst processing, takes you to the CORRECT, desired quote number. This is driving me crazy. Is there some limitation to the number of records that are in a recordset at one time or something. I put in a message box for the condition of nomatch, but I'm not getting a message saying it cannot find the record, it just shows the wrong record. Below is my code. Any help with this would really be appreciated. I also tried adding a whole new "goto quote" field in the form header, and that was working, but then changes that users would make to the data would SOMETIMES not be saved (similar to the other gentleman's post today in regard to findfirst).

Private Sub goto_quote_AfterUpdate()
' Find the record that matches the control.
On Error GoTo ProcError
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 3, , acMenuVer70
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[quote_ID] = " & str(Me![goto_quote])
If rs.nomatch Then
MsgBox "Record Not Found"
Me.Bookmark = rs.Bookmark
End If
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
Exit Sub
MsgBox "Error: " & Err.Number & ". " & Err.description
Resume ExitProc
End Sub

Fred Attached Thumbnails   Reply With Quote 03-01-2012, 01:48 PM #2 RuralGuy Administrator Windows 7 64bit Access 2010 32bit Join Date Mar 2007 Location 8300' in the Colorado Rocky Mountains Posts 9,484 I'm sorry but can you convert the DoCmd.DoMenuItem commands to the new DoCmd.RunCommand format?


I have build a simple 3 table database at my work for us to replace our ancient logbooks. We are using it and it works pretty good, that is my colleagues and I are happy with the end result.

but I am by no means an expert in this field and we have a small problem: when the form for logging is initially started it shows the first record in the table and my colleague overwrote that accidentally.

my question:

How to make a form default to new record rather then showing the first record from the table each time the form is initially opened?

in my search for the answer here I found a new term (for me) but its name already makes me think of a modification for my purpose: "switchboard"

I just made another form with buttons to start a form to fill in the appropriate table, that I would almost want call a switchboard

so perhaps my second question would be:

what is a switchboard?

Thanks in advance

I am working on a report that is going to be emailed and transferred electronically to other people. The user has requested that the first page of the report must have our company letterhead on the page. I created the report and placed the image of the top of our letterhead in the report header. The image of the bottom of the letterhead has been placed in the page footer. The following code has been placed in the on format of the page footer so that only the first page will show the bottom of the letterhead. Me.Section(4).Visible = Me.[Page] = 1 The print preview shows this correctly, however when I export this to .pdf or print it, the page footer does not show up when printing multiple pages. Has anyone run into this situation? Any help would be greatly appreciated!


I am using the following code to import data from a closed XL spreadsheet and import it into a table in my database.

	Sub GetDataFromClosedWorkbook()
Dim wb As Workbook
Dim ws As Worksheet
Dim db As Database, rec As Recordset
Dim Filename As Object
Dim Selection As String

Set db = CurrentDb
Set rec = db.OpenRecordset("VIP")
Set Filename = Application.FileDialog(msoFileDialogFilePicker)
    With Filename
    .InitialView = msoFileDialogViewDetails:
    .InitialFileName = "C:test":
    .Filters.Add "Pick .xls File", "*.xls", 1:
    .ButtonName = "Import file":
    .Title = "Search for Database_Import.xls file"

    ' If no file is selected, close the sub, else keep going
        If .Show = -1 Then
            Selection = .SelectedItems(1)
            Exit Sub
        End If
    End With
Set wb = Workbooks.Open(Selection, True, True)
Set ws = wb.Worksheets("VIP") ' define which worksheet shall be imported
    ' open the source workbook, read only
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "VIP2", Selection, True
    ' close the source workbook without saving any changes
    wb.Close False
    Set wb = Nothing ' free memory
End Sub

the problem with the code is that it imports from the .xls as wished, but it creates me around 300 empty entries before showing me the first entry which is entered in row 2 on the xl spreadsheet.
Solution to this would be a way to check for empty fields and if yes, delete the whole line. (Eg. if field "Profile ID" is empty, delete whole line)

Further, I later want to import the data directly into an existing table, but i would need Access to understand that if the entry in the field "Profile ID" exists already, that it shall replace the existing one. However, no double existing entries may exist in the database (measured by the Profile ID).

Maybe someone can give me a hand on this since I dont know how to do this, respectively where to start.
Thanks for the help!

I Have Created A Client Reference Allocation Screen Which Makes The Clients Reference From Details In A Number Of Text Boxes.

For Example:

Client Number: 0010, Forename: Andrew, Surname: Bloggs, Year: 2007,
Occupation: Driver.

Client Reference = 0010ab-dr07

I Have Copied The Text Boxes So The Information Is Automatically Displayed To Create The Client Reference, But Rather Than Shortening The Copied Text Boxes To Show Only The First Few Characters Is There A Way Of Limiting The Number Of Characters Shown In The Copied Text Boxes??


I know how to get excel to show only the last four digits of ID numbers. At first I thought I can just import the excel file with the last four digits and hide the column with the full ID numbers but all got imported to Access. If I am stuck with this, how do I get Access field to display only the last 4 digits? I do not want to show "***-**-****", just the last 4 digits only.

In excel I would use the RIGHT function, "=RIGHT(A1,4)." Can I do this type of function, "=RIGHT([field name],4)? I have not attempted to try it yet. Thought to ask first.

Access 2010

I have a Table that has a Startdate column and another column with Prices in it. I need to show only the prices that changed from last month and this month. What I have done so far is I created two queries to show the July Date prices and another to show the August Date Prices. This works perfectly. Now I am trying to get a query that will only show the August Prices if they are different then the July price. This is the SQL that I created so far but it does not work:

	SELECT [August].[Price][July].[PC] AS Changed
WHERE ((([August].[Price][July].[PC])=True));

I have uploaded a tiny database which illustrates the question. I have a series of welds, in the example there are 6, numbered 1 - 6. I want a report which only shows the first and last weld details. I made two queries, one with MinofWeldNo and one of MaxOfWeldNo. Then a Union query to bring the first and last together.

But it does not work.

Please help.


Not finding an answer? Try a Google search.