473,320 Members | 1,993 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

PULL REPORT WITH LATEST DATA FOR INDIVIDUAL RECORDS

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
Nov 13 '05 #1
2 2026
On 28 Nov 2004 01:37:19 -0800, sc*********@japan.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.
Nov 13 '05 #2
"Scott Baird" <sc*********@japan.com> wrote in message
news:c6**************************@posting.google.c om...
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.
Nov 13 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
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...
6
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. ...
1
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...
8
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...
6
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...
1
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...
6
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...
1
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...
4
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...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 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 former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.