Filter data into new field

  • Thread starter Thread starter Mary Cannon
  • Start date Start date
M

Mary Cannon

I would like to print a report that filters data from two
fields of the two linked tables.

Documents table Revision field represents a revision of a
document at the release stage.
Change Orders table Revision field tracks the changes of a
document and shows the latest revision.

How can I generate a report that would have a look up
function to go to Change Orders.Revision first, look for
records and display the latest revision letter in
Current_Rev field (there could be more than one record,
i.e., A,B,C, D), if there are any records in Change Orders
table go to Documents.Revision and take the value from the
Revision field of the Documents table.

This is a report that I generate now

Document_ No Documents.Revision Change
Orders.Revision
6000206 -
A
6000208 -

6003129 -
A
B
C

I would like to generate a report that has an added field
named Current_Rev containing filtered data from both
tables and display only one line for each record.

Document_No Current_Rev
6000206 A

6000208 -

6003129 C

Please help, I'm not very familiar with Visual programming
language, usually use wizards, your guidance is greatly
appreciated.
 
Please help! Since no one responded, I assume that I
didn't state my problem clearly.

I have a query that filters data from 2 tables: Documents
and Change Orders. I would like to use the query to
generate a report that would print a list of documents
with:
Document number, document title, document revision.

Is it possible to add a calculated field to report i.e.,
Current_Revision that would have a formula to check both
tables and select the record that has the latest revision
(Revision fields are text fields). I would like to avoid
adding additional field with data that is already
contained in one of the Tables.

Currently I print long reports, some documents have long
history of changes, for example it may start with
original release revision "-" and end on revison "AC".

Please, please help and thank you very much for looking
into it.
Mary
 
Mary

I'm unclear what underlying data you are trying to draw from. For instance,
you mentioned finding the latest revision. How would you (and Access) know
a certain revision was the latest (i.e., what field, in what table, tells
you what order)?

I am puzzled by your statement:
(Revision fields are text fields). I would like to avoid
adding additional field with data that is already
contained in one of the Tables.
Click to expand...

Are you attempting to show all revisions? All text fields?

Please provide a copy of the SQL statement you are using now, and a brief
description of your table structure.
 
Hello Jeff, thank you so much for your promt respond

I'm sorry for not making it clear.

- Field [Revision] in Documents table will represent the
latest revision if they are no records in [Revision] field
of [Change Orders] table.
- Yes, Access would have to be told that the highest alpha
character in both [Revision] fields represents the latest
revision of a document.
- Documents table [Revision] field: "-" or alpha
character, i.e., A, B, C.may be entered at the first stage
of a document release.
- Change Orders table [Revision] field: may have no
records if changes didn't occur or may have multiple
records starting with alpha character A, then B, C...AA, AB
Examples
1) Document number 6003129 was released as "_" revision.
Documents table would have "-" in [Revision] field. No
changes were made, there are no records in [Change Orders]
table. Access report prints one record

Document_No Revision
6003120 -

2) Document number 6003129 was revised 3 times. [Change
Orders] table would have 3 records, [Revision] field would
have A, B and C alpha characters. Access report prints 3
records

Document_No Revision
6003120 A
6003120 B
6003120 C
My goal is to have Access print just one record per
document, showing the latest revision (not all revisions)
of a document as follows:

Document_No Revision
6003120 C
I don't have SQL statement I would not know how to create
one. I was trying to enter IIf funtion in calculated field
in report design but nothing is working. I can attach a
shorter version of my database. I deleted most of the
records, since may be too large to E-mail. Can this be
accomplished some other way.

Thanks again
Mary
 
Mary

I'm still a little befuddled by your description, but did notice no mention
of "query".

A typical approach to building a report in Access is to first identify all
the data needed (and all the tables holding that data).

Then a query would be built to relate/connect those tables together, and
return the information needed.

Finally, the report would get created, based on the query.

How does this match with what you've already done?
 
Hello Jeff,
Yes, I do have a query named "F22 MK Test" that I work on
to generate this report. The query consists of 2 tables;
Documents and Change Orders. Those 2 tables are linked on
[Document_No] fields.

In Query I selected only 4 fields to be used by Report
(First 3 fields come from Documents table, only 1 comes
from Change Orders table):

Document_No.Documents
Document_Title.Documents
Revision.Documents
Revision.Change Orders

Thanks again
Mary
 
Jeff, earlier you asked me about SQL statement, I just
learned how to copy it. Here is my present SQL statement:

SELECT DISTINCTROW Documents.[Document_ No],
Documents.Document_Title, Documents.Drwg_Size,
Documents.Revision, [Change Orders].Revision,
Documents.No_of_Sheets
FROM Documents LEFT JOIN [Change Orders] ON Documents.
[Document_ No] = [Change Orders].Document_No
WHERE (((Documents.[Document_ No]) Not Like "10236*") AND
((Documents.Obsolete)<>Yes) AND ((Documents.Model)
="f22"));

2 existing fields Drwg_Size and No_of_Sheets I will
probably eliminate from report.

I just want to mention that I use Access 97.

Thanks for your patients.
Mary
 
Mary

Take a look at the Totals query -- it sounds like you are trying to find the
"maximum" value of [Revision] for each [Document_No].

Also make sure the join properties between the tables indicate all from the
tblDocuments and any that match from tblChangeOrder (by the way, consider
removing the space in the tablename).

You might also want to modify the field in the query that returns the
revision, to handle nulls by displaying your "-" symbol -- you can use the
IIF() function, but I don't recall if A'97 has the Nz() function.
 
Jeff,
Thank you very much for your very valuable suggestions.
The Max value worked for me. There are probably better
ways but here what I did:

SELECT DISTINCTROW Documents.[Document_ No],
Documents.Document_Title, Max(Documents.Revision) AS
MaxOfRevision, Max([Change Orders].Revision) AS
MaxOfRevision1, ([MaxOfRevision] & [MaxOfRevision1]) AS
AllRev, IIf([AllRev] Like "0*",Right([AllRev],4),Right
([AllRev],1)) AS Current_Rev
FROM Documents LEFT JOIN [Change Orders] ON Documents.
[Document_ No] = [Change Orders].Document_No
GROUP BY Documents.[Document_ No],
Documents.Document_Title, ([MaxOfRevision] &
[MaxOfRevision1]), IIf([AllRev] Like "0*",Right
([AllRev],4),Right([AllRev],1)), Documents.Obsolete,
Documents.Model
HAVING (((Documents.[Document_ No]) Not Like "10236*") AND
((Documents.Obsolete)<>Yes) AND ((Documents.Model)="f22"));

Very confusing - I still have think of how to make Access
understand that if there is a rev AA or AB select 2
characters from right.

Thank again,
Mary
 
Mary (see below)
Very confusing - I still have think of how to make Access
understand that if there is a rev AA or AB select 2
characters from right.
Click to expand...

You could replace your alpha-sequence with a simple integer (1, 2, 3, ...)
and not have to evaluate your Rev AB vs Rev BA at all.

Just one person's opinion

Jeff Boyce
<Access MVP>
 
Jeff,
Thank you very much for your help. I was away on vacation
and didn't have access to the internet.
Yes it is very confusing.
Sounds good but I'm not shure if I understand how to
replace alpha-seq with integer (1,2,3,..). Will the
report show the true document's revision, i.e., -, A, B,
AA, AB, 0100, 0200, etc. (0100, 0200, 0300... are used
for software revision)

Thank you,
Mary
 
Back
Top