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 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.
"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. 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...
|
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.
...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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....
|
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...
| |