Access 2k Query Problem (print Last Record ?)

Jun 19, 2007

I'm running access 2k, WinXP Pro SP2 - both are current with latest updates. I'm having a problem getting the select query behind a report to give me the results I need. The query works with two tables (Client List and Atty_Pmt). Atty_Pmt is related to Client List on a one-many relationship, using ClientList_ID primary key.
The payments table structure is as follows:
Atty_PmtID
Date
PayAmt
PayNotes
ClientList_ID

The Client List table has fields like 'lname', 'fname', 'dob', etc, etc.
The ClientList table structure is as follows:

ID
Lname
Fname
dob
status

Im trying to structure a query that will list each client, and ONLY the last payment they made. I tried using a select query Top n, making 'n' value 1, and it only lists one client, one payment record, which isnt even the last payment they made.
This is the query that I currently have....

SELECT TOP 1 [Client List].ID, [Client List].Lname, [Client List].Fname, Atty_Pmt.Atty_PmtID, Atty_Pmt.Date, Atty_Pmt.PayAmt, Atty_Pmt.PayNotes FROM [Client List] INNER JOIN Atty_Pmt ON [Client List].ID = Atty_Pmt.ClientList_ID;

Can someone tell me how to single out the most recent payment (either by greatest date - maybe closest date to current date - or by the 'Atty_PmtID' field). It could possibly be based on the 'Atty_PmtID' field because the payments are entered in order as they come in, so the most recent payment would be the higher 'Atty_PmtID' autonumber, but I just dont know.
Thanks in advance for your suggestions and help
JR
Semper-Fi!

View Replies


ADVERTISEMENT

Print ONE Record In A Form - Access 2007

Jun 25, 2013

When printing a form (record) in Access 2007??????? When I go to print preview the form displays (and wants to print) every record!!!! How do i get only ONE record to display and print..

View 1 Replies View Related

Query / Report Question (print A Patient Record)

Jun 28, 2005

Hi again.

I've a database with patient information. The database is structured in the following way:

tblPatients - patient information
tblVisits - visit information (one patient can have many visits)
tblInfo - extra information related to a visit
tblTreatment1 - treatment1 (linked to a visit)
tblTreatment2 - treatment2 (linked to a visit)
+ a couple of dozen minor tables and subtables

What I need is to be able to print out a patient record with all the information related to the patient in question. A patient may have one or more visits, and one visit may include one or more treatments or several instances of one treatment. A visit may or may not have some extra information on tblInfo (etc.)

Tables are linked to each other via key fields in a normal way. (= tblVisits is linked to tblPatients via patientID field, and tblTreatment1 is linked to tblVisits via visitID field etc.) All keys of the main tables are auto numbers.

My question is: how to gather this information from the tables? This seems to be too complex for Access's report wizard to accomplish. My Access Bible -book advices to create a query to collect all the information first, but I'm having problems with this, too. One problem is that not all tables have information related to a certain patient, or one patient can have more than one record in (eg.) tblVisits.

What do you suggest? I'm starting to think it would be best to write a query directly in SQL and base a report on that query. Could you give me some advice as to where to start and how to create the SQL statements? I hope I can work out the details myself, but a general advice would be most helpful.

Thanks,
garcanrya

View 8 Replies View Related

Queries :: How To Print A Label For Each Record Returned By Query

Dec 27, 2013

I have a query that returns several records, typically 1 to 5. I need to print a label for each record returned by the query. Printing a label is no problem, but how do I print sequentally for each record in the query?

View 2 Replies View Related

Auto Print Report For Only Latest Record On Add New Record Event??

Jun 21, 2005

Hi All,
I am looking for some help with a project I am working on where I need to automatically print a report from my database every time a new record is added to the table. The table contains 13 fields and the report needs to display 12 of them, the other being the index which is set to Autonumber.

The table is being updated solely by ODBC, this is working OK.

There is the potential for records to be added to the table very quickly via the ODBC link, so I need to safeguard that the report is being populated with correct information from the record that triggered the print event. Also, should multiple records be added in close succession, a report needs to be correctly generated/printed for each of one. The DB is to have no user intervention, and will just run on the PC at startup with all access menus/controls locked out.

Any information on how you think I should structure this, or any examples of helpful code that you might have would be very much appreciated.


Thanks in advance, :)
Jon.

View 7 Replies View Related

Print Current Record From DAP

Jan 18, 2005

Hi,

Is there anyway i can print the current record using a button or something ,which i am viewing using Data Access Pages , i have searched a lot of forums but have got nowhere , now i just wanna know how the hell can i use it to print a record or print any report for that matter using a DAP!!! can anyone give me a suggestion or just point me to some reference for using these DAP...

thankx,
solig

View 2 Replies View Related

Print Record From Form...

Mar 9, 2005

I've got an unbound form that view records indvidually and I'd like to create a report that when the end-user selects the Print Record Command button it will print all the employees information for his review and if changes my admin's will make the changes. What code would i use to do that with a message prompting them are they sure? I also would like a cmd button to print all records for annual review. Any input will be greatly appreciated...

View 13 Replies View Related

Print Several Reports Of Same Record

Jun 7, 2005

How do I print several reports of the same record in a row and only prompting once the printer properties?

Thanks in advance.

View 2 Replies View Related

How Do I Print Off Just ONE Record From A Form?

Jul 8, 2004

I have tried lots of examples but getting nowhere. Please help!

I use a form to keep a record of employees training. Form name: "ITD Data Entry"

Individuals are identified by a primary key called "SERVICE NUMBER"

I have a button on my form. When I scroll to the right record I click this button to go and preview a report, called "ITD CERTIFICATE".

My aim is to be able to just print off the record I am viewing, but at present if I select print I end up printing off all the records, which would involve a lot of paper

At present, to get around this, I have to select a print range. OK for me, but not great for a stranger to the database.

Is there a way to control this button to print preview the current record only?

Many thanks for your help in advance.

Fallyhag

View 7 Replies View Related

General :: Print Record With Yes Against Them

Jan 29, 2015

I am trying to design a form or report where I can print the records marked with Y/N, but not sure how to do it?

View 5 Replies View Related

Print Only Report For Current Record

Aug 20, 2007

I have a report that is tied to a table. On my table form I have a button that prints the report. Is there a way to only print the report for the current record showing on my form?

View 1 Replies View Related

Print Current Record To Report

May 20, 2005

I'm having hard trouble doing print preview current record from form with subform to a report.

When using the code below I'm getting an error for:

The specified field [PolicyNum] could refer to more than 1 table listed in the from clause of your sql statement.

Dim strDocName As String
Dim strLinkCriteria As String

strDocName = "rptCurrentInfo"
strLinkCriteria = "[PolicyNum] = Forms![frmAdjusterMod]![PolicyNum]"

DoCmd.OpenReport strDocName, acViewPreview, , strLinkCriteria

Command button is in the main form frmadjustermod - sbfm is sbfmMoreMod

foreign key is PolicyNum

Thanks,
Michael

View 3 Replies View Related

Print Preview Without Saving Record

May 15, 2006

I'm posting this question to Forms forum because I'm sure I can't do this with a report--though a report is really what I need to be using.

My database has buttons on the forms to preview a "page printout" report based on the data in the record displayed. The reports - formatted to closely resemble printed questionnaires provided by the client - are based on queries, run with criteria conditions to match the record on the screen. So, to run the report, you have to have queriable data -- i.e. the data shown in the form has to have been saved to table. So the print preview buttons include a Save command.

The client would like to be able to view the printout of a potential new (or changed) record without saving. (There is an audit trail that tracks changes and deletions once a record has been saved.) The only way I can think of that this might be done would be to print directly off of the form--which would produce un-pretty results at best, I'm afraid, and seems like a headache to set up. But would it even work? Using the file menu to preview printout of a form seems to save the record.

So, my questions:
1. Is there something I'm missing such that I could run the reports without saving the data?
2. If I DID print out the form instead of the report, can even THAT be done without saving the record? (They would only need to preview without saving, not actually print.)

View 3 Replies View Related

Reports :: How To Get A Form To Print One Record

Jun 5, 2013

I'm trying to get a form to print just one record on a report - and using this code:

Private Sub Command24_Click()

Dim strReportName As String
Dim strCriteria As String

strReportName = "Badge"
strCriteria = "[ID]='" & Me![ID] & "'"
DoCmd.OpenReport strReportName, acViewPreview, , strCriteria

End Sub

It won't work though - says there's a 'data type mis-match in criteria expression'

View 4 Replies View Related

Print Current Record To A Report?

Dec 23, 2011

Private Sub Command30_Click()
DoCmd.RunCommand acCmdSaveRecord
Dim strDocName As String
Dim strWhere As String
strDocName = "Civil Process"
strWhere = "[FormID]=" & Me!FormID
DoCmd.OpenReport strDocName, acPreview, , strWhere
DoCmd.RunCommand acCmdPrint
End Sub

I can get this code to print only one record but then the button will always print this record. It will not change and print the current record.

FormID is an autonumber to give each record it own id.

View 2 Replies View Related

How To Print Object For Current Record

Dec 4, 2013

I put a button on a form for printing the object but it prints every record. How can I print the object with the current record?

View 3 Replies View Related

Print Out Single Record Onto A Report

Jan 7, 2015

I have a form NoWorkOrder, i have attached a command button to Print Report. The report is named No Work Order. When I'm viewing that a record in Form View and click the command button i want only that record to print out onto the report.

I'm using Ms Access 2010.

View 14 Replies View Related

How To Print Each Record Of A Group On A Separate Page

Oct 23, 2004

I have a report with field "Plot" as Group Header. I need to print the report
which lists the owners of each Plot. When the number of owners is large, it takes more
than one page for the listing. How can I force a new page for each new Plot ?

View 2 Replies View Related

Reports :: Getting Report To Print Only Selected Record Instead Of All

Aug 10, 2015

I am trying to get a report to print out one one record that I want.. I want it to be able to be sorted by date and the user just picks a date and one record is printed out on page. Right now every record that is stored is being printed on a different page and over time that is going to be a lot of paper... I want to be able to pick which record I want printed...

The Form is called "Bread Mold"
The report is called "Bread Mold Report"
The table is called "Bread Mold"
What it is sorted by is called "Swab Date"

View 13 Replies View Related

Modules & VBA :: Print Current Record From Recordset?

Aug 7, 2015

Is there a way to print the current record from a dao.recordset?

This is an exercise to compare data content.

I have a table with 30 fields and thousands of rows (rs1).

I'm comparing it with a copy of the same table (rs2) that has the same number of fields and the same rows and almost the same content.

I can loop through the recordsets and get the cursor to stop on a field whose values don't match, lets say on row #x

and the programmed message will say something like:

"ROW: 699 Field: [RequestStatus] rs1.VALUE: Closed, DOES NOT MATCH rs2.Value: VOID, in the comparison recordset"Then I'd like to print the entire Row, Row #699.

I thought I could use rs1.getrows but I'm not sure how to make that work.

View 4 Replies View Related

Modules & VBA :: Print Last Record Via Simple Report

Jun 20, 2014

I am trying to add a record through a form, and then get that last record and print it via a simple report.

I keep getting debug when I run it. My add record sub works fine, but the dlookup doesn't work when I put the add record sub in.

Private Sub addRecord()
On Error GoTo errHandling
'INSERT CODE
Dim dbOpenTrades As DAO.Database
Dim rstTrades As DAO.Recordset

[Code] .....

View 1 Replies View Related

Report Says No Current Record When In Print Preview

Aug 25, 2015

Report says 'No Current Record' when viewing with print preview, even though it works fine in report view?What did I do?

View 10 Replies View Related

Print Single Record Based Upon Current Form

Apr 6, 2006

I need to Print a single record from a form but using a report layout.

I have created the form and created a report. A button on the Form kciks off the print but I get all records rather than just the one shown on the Form. Whats a really simple way of just selecting the Current record.

Where do I put an instruction saying print only this record:confused:

View 9 Replies View Related

Print Current Record - In Form View Not Report

Aug 14, 2006

hi
i have 3 queries brought together in 1 form. i do not want a report to be printed out just the current record in form view. this is because i have a number of images per record.
if i print out the current record and it only has 1 photo in a sub-form attached to the main form record, then the main form will print on 1 page which is what i want [screenshot 1]. But if there are 2 or more photos attached to the main record then the print command button prints out 2 pages for 2 photos, 3 pages for 3 photos and so on [screenshot 2].
any ideas as to how to limit the print command button to print out only 1 page per record regardless of how many photos their are attached to each record??
thanks

View 1 Replies View Related

Forms :: Unable To Print Current Record In The Form

Jun 4, 2014

I tried to follow the Allen Brown print the current record in the form and its not coming up. I can print all of the records but not the current.

View 3 Replies View Related

Forms :: Set The Size And Print Current Record With Subforms

May 30, 2014

I have a form that has 2 subforms. When I try to print the current file there are a few problems,

1) When you use the print option in File menu, and select "Selected Records" , it will always print the second record, no the one selected.

2) No matter how often I change the printer properties , it will not keep the paper size I request.

Is there a script I could use to set the size and print the current record ?

View 1 Replies View Related







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