Query for all Journal entries per CaseID

  • Thread starter Thread starter Karen
  • Start date Start date
K

Karen

I have a main table with the values for a Case so we'll call it the Case table.

For every Case there can be multiple Journal entries so we'll call it the Journal table.

The Journal table stores the primary key of the Case as a foreign key.

I want a query that pulls all info from the Case table and all of the Journal entries for that Case.

What I keep getting is a query that lists the Case data for each journal entry so the Case data is duplicated every time.

This can't be as hard as I'm finding it to be, I've tried inner joins and group by (which really doesn't work)

I want to build this as a SQL query in VBA.
 
Dear Karen:

Yes, what you see is a repetition of the information from the Case
table for each row matched in the Journal table.

Obviously, then, your query has requested these columns.

What would you expect the query to put in these columns? What is the
effect you want?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
If you want a single query that pulls all Case data and
associated Journal data, then you will get all the case
data repeated on every associated Journal line. I am not
sure why this is a problem to you.

Hope This Helps
Gerald Stanley MCSD
-----Original Message-----
I have a main table with the values for a Case so we'll call it the Case table.

For every Case there can be multiple Journal entries so
Click to expand...
we'll call it the Journal table.
The Journal table stores the primary key of the Case as a foreign key.

I want a query that pulls all info from the Case table and
Click to expand...
all of the Journal entries for that Case.
What I keep getting is a query that lists the Case data
Click to expand...
for each journal entry so the Case data is duplicated every
time.
This can't be as hard as I'm finding it to be, I've tried
Click to expand...
inner joins and group by (which really doesn't work)
 
Hi Tom,

What I want is the Case info once and ALL of the associated journal entries;
very much like a subform on a main form or a subreport on a main report.
 
Dear Karen:

So, I think you are saying you want the functionality of forms and
reports, but in queries. I just so happens I recently decided that,
in some cases, this was best for me as well. The whole topic is about
book size (well a small book of 200 pages perhaps) and I haven't yet
developed all of it.

My idea was to avoid the lengthy formatting time of reports with
multiple subreports by making the query look just like what I want in
the finished data. Initial tests are very promising.

Well, the how is that you must rank the rows in each "sub-table" and
then show the values from the main table only when the rank value is 1
(actually, I use 0 for first row rank). As a query technique in Jet,
this will be moderately difficult and moderately slow. I use this
only with MSDE/SQL Server. Also, I doing this in many cases to write
for SQL Server Reporting Services, which to not have subreports.

Bottom line is, I recommend you create a form/subform setup or a
report/subreport to do this. The work of building the forms or
reports will likely be less than the work to make the query do it for
you.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Tom,

Well, at least I'm not completely incompetent! I'll just use the
report/subreport feature in Access, it'll be acceptable. Thanks for the
info.
 
Gerald,

I wanted to merge the data in Word and duplicating the Case info, which is a
full page column of info for every journal entry was not acceptable. Based
on Tom's suggestion, I'm just going to stick to the report feature in
Access.
 
Back
Top