HELP!!! I've got a DB with two tables (relationships with a
autonumber). The database works just fine, but I've got a report I
simply can't make work.
I've made a query that uses data from both tables. Table A contains
all the basic data about individual valves (such as valve number,
location, type, etc.). Table b contains a history of work performed
on the individual valves (such as date tested, test performed by,
etc). A basic "one to many" style relationship type database. I'd
like to generate a report that uses a bit of data from both tables,
but ONLY pulls the most recent data from table b (i.e, all valve data
from table A, but only the most recent test from table b or blanks if
there is no history for any particular valve) for each individual
valve. I can't get my report to show me all valves with the most
recent history for each valve. What I get when using dlast is all
history dates show as the most recent history date in table b and not
the specific valves history date. Confused? I certainly am!!!
I know I'm doing something wrong (obviously), but I don't know if it
is my use of the Dlast or what. Any ideas what might give me what I'm
after?
Very Respectfully,
Scott Baird
2  2041 
On 28 Nov 2004 01:37:19 -0800, sc*********@jap an.com (Scott Baird) wrote: HELP!!! I've got a DB with two tables (relationships with a
autonumber). The database works just fine, but I've got a report I
simply can't make work.
I've made a query that uses data from both tables. Table A contains
all the basic data about individual valves (such as valve number,
location, type, etc.). Table b contains a history of work performed
on the individual valves (such as date tested, test performed by,
etc). A basic "one to many" style relationship type database. I'd
like to generate a report that uses a bit of data from both tables,
but ONLY pulls the most recent data from table b (i.e, all valve data
from table A, but only the most recent test from table b or blanks if
there is no history for any particular valve) for each individual
valve. I can't get my report to show me all valves with the most
recent history for each valve. What I get when using dlast is all
history dates show as the most recent history date in table b and not
the specific valves history date. Confused? I certainly am!!!
I know I'm doing something wrong (obviously), but I don't know if it
is my use of the Dlast or what. Any ideas what might give me what I'm
after?
Very Respectfully,
Scott Baird
First, make a query of your primary table with a subquery that gets the id of
the most recent record in b by using TOP 1. Now, join that query back to the
history table to get the row having the ID found in the subquery.
"Scott Baird" <sc*********@ja pan.com> wrote in message
news:c6******** *************** ***@posting.goo gle.com... HELP!!! I've got a DB with two tables (relationships with a
autonumber). The database works just fine, but I've got a report I
simply can't make work.
Post your (simplified) table structure with some sample data and the output
you need and someone will be able to help you. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: MLH |
last post by:
I have a challenge...
In a table I call tblStuff4Letters with 3 fields:
, and . I have
a report called rptOutboundCorrespondence that feeds
off this table. Sorting & grouping is turned on in
that report - sorted first on (ascending)
then on (ascending). Of course,
there are more supporting fields in the table. The
above 3 are the significant fields for this discussion.
|
by: Richard Holliingsworth |
last post by:
Hello:
Thanks for reading this.
I would like to build a report (Access 2002) that uses SQL queries to
count records in specific groups.
I've tried several methods and so far no luck.
Could someone please point me in the right direction on this one. It
|
by: Mike Cooper |
last post by:
Hi everyone,
This is a tough one. I have a database full of solicitations,
identifying a customer and recording initial call, first followup,
second followup, etc.
My boss want to be able to generate a report showing a list of
customer who were call between two different dates that he types into
a form. That, I have done. My boss types in his two dates, the SQL
statement searches through the follow-up call fields for a date
|
by: Armando |
last post by:
Here's one that's going to leave me bald before long -
I have a report (no headers or footers, small detail section) that runs
normally when I open it from the database window. When I run it from code:
DoCmd.OpenReport "rpt_Name", acViewPreview
I get five records on the first page, and only one record on the second
|
by: DavidOwens |
last post by:
Sub PreviewReport_Click()
On Error GoTo Err_PreviewReport_Click
Dim stRepName As String: Rem Holds the Report name
Dim stDispId As String: Rem Holds the Dispenser ID
Dim stQuery As String: Rem Holds the Query name
Dim stWhere As String: Rem Holds the where clause
Dim stExtra As String:
Dim stMonthYear, stYear, stWeekCount As String
Dim intX As Integer, rst As Recordset
| | |
by: dfw1417 |
last post by:
I have used a query and report filter to return records related to a specific account id. I want to print a report including only the latest 6 records out of the set returned by the record filter. I have set the sort order for the report on descending thus returning the latest records at the top of the report. However since there are well over 6 records related to the account number the report contains all the related records.
I have been...
|
by: Dave |
last post by:
On my form I have combo boxes. These combo boxes, after updating
them, populate respective listboxes that are located below the combo
boxes on the same form. I am trying to use a "generate report" button
located on my form to print all of the list box values (that have been
updated via selection from combo boxes) from the form to the report.
I've tried using a macro with the code:
Macro Name: cmdGenerateReport : On Click
Action:...
|
by: teneesh |
last post by:
Okay. . . . I have a query from a view that should pull up multiple records. When I run it in sql server, it pulls up all records. But when I create the report for it, it only pulls up the first record of each described category of the query. For example
The query is:
select '200701','200712',eid,name,conftype,rate, rotation,confattendance, confoffered,sum(finalvalue)FinalValue from ViewConfAttnRate where period between '200701' and...
|
by: zufie |
last post by:
I have a report that return all the records (materials ordered) by all
callers each day.
I want the report to just show all the records (materials ordered) by
each individual caller each day
on a separate page.
I tried Hide Duplicates property, Force New Page all to no success.
Here is the code to my qry/record source for my report:
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| | |
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
