Query/Report for most recent journal entry

[jmaher196]

Hello all!

I have a rather large database for ongoing projects at my office. There is a form that is used to enter basic information about each project, and this form contains a subform to record journal entries for each project. The system assigns a random autonumber to each project, and the subform uses the same autonumber to associated each journal entry with the correct project.

The subform conatins the following fields: Autonum, Date, and Journal_Entry. Everything has been associating properly, but when I try to create a report to show the most recent journal entry for each project, I have a few problems.

The main problem: the system will show the correct date(the most recent date a journal entry was made) but will show the wrong journal entry. The journals appear to end up in alphabetical order. For some reason, the association between the date and its respective journal entry is lost. Any thoughts on how to fix this?

[jmaher196]

Sorry, I should also mention that I have been using the Group/sort feature to put the most recent date on top in the report.

[zmbd]

There might be an issue in how the relationships in the DB have been defined (ribbon, database tools, relationships).

Would be helpful to have the tables and their fields:

Table_One:
[tblone_pk] autonumber, primary key
[somefield] text(50),required,indexed
[yetanother] long, not required, not indexed
 
Table_Two:
[tbltwo_pk] autonumber, primary key
[tblone_fk] long, foriegn key to Table_One 1:M
[otherfileds] type and information.

and the SQL for your queries behind the reports:

SELECT
   Table1.ID_t1, Table1.date_t1, Table1.something_t1
FROM 
   Table1;

thnx
z

[jmaher196]

I am not allowed to show you the actual tables, as the information contained in them is highly confidential. I apologize for that, but I can give you the SQL for the In-Process Project query:

(The very large IIf statement is for another part of the query that works fine. Again, sorry if it is confusing)

SELECT Test_Table_for_sorting.Job, Test_Table_for_sorting.Type, Test_Table_for_sorting.Initiative, Test_Table_for_sorting.Project_Name, Test_Table_for_sorting.Customer, Test_Table_for_sorting.Project_MGR, Test_Table_for_sorting.Tech_Lead, Test_Table_for_sorting.[Est Completion], Test_Table_for_sorting.Status, JournalEntry.Date, JournalEntry.Journal_Entry, IIf([MS1AED] Is Null And [MS1EED] Is Not Null,[Milestone 1Type],IIf([MS2AED] Is Null And [MS2EED] Is Not Null,[Milestone 2 Type],IIf([MS3AED] Is Null And [MS3EED] Is Not Null,[Milestone 3 Type],IIf([MS4AED] Is Null And [MS4EED] Is Not Null,[Milestone 4 Type],IIf([MS5AED] Is Null And [MS5EED] Is Not Null,[Milestone 5 Type],IIf([MS6AED] Is Null And [MS6EED] Is Not Null,[Milestone 6 Type],IIf([MS7AED] Is Null And [MS7EED] Is Not Null,[Milestone 7 Type],"Pending"))))))) AS [Current Milestone], IIf([MS1AED] Is Null And [MS1ESD] Is Not Null,[MS1EED],IIf([MS2AED] Is Null And [MS2EED] Is Not Null,[MS2EED],IIf([MS3AED] Is Null And [MS3EED] Is Not Null,[MS3EED],IIf([MS4AED] Is Null And [MS4EED] Is Not Null,[MS4EED],IIf([MS5AED] Is Null And [MS5EED] Is Not Null,[MS5EED],IIf([MS6AED] Is Null And [MS6EED] Is Not Null,[MS6EED],IIf([MS7AED] Is Null And [MS7EED] Is Not Null,[MS7EED]))))))) AS [Est Milestone End Date]
FROM Test_Table_for_sorting INNER JOIN JournalEntry ON Test_Table_for_sorting.AutoNum = JournalEntry.AutoNum
WHERE (((Test_Table_for_sorting.Job) Is Not Null) AND ((Test_Table_for_sorting.Type)<>"Prog Mgmt" And (Test_Table_for_sorting.Type)<>"Alligator") AND ((Test_Table_for_sorting.Initiative) Is Not Null) AND ((Test_Table_for_sorting.Status)="In-Process"))
ORDER BY JournalEntry.Date DESC;
 

[zmbd]

We don't need the actual data...
What I'm after is the structure of your DB as shown in the generic example in my post.

Wow that's a nest of IIF... that'll take a second to read and see where and if there is any relationship between the records.

Once again... we don't need the data just the table structure and the actual assigned relationships between the tables.
-z

[zmbd]

From your SQL, here's what you given me to look at for a table structure:

Test_Table_for_sorting:
[AutoNum]  (PK: autonumber field, index, no duplicates)
[Job] (text)
[Type] (text - 1:M (?) with table "type")
[Initiative] (FK: text - 1:M (?) with table "Intiative")
[Project_name] (text)
[Customer] (FK: text - 1:M (?) with table "Customer")
[Project_Mgr] (FK: text - 1:M (?) with table "Employees")
[Tech_Lead] (FK: text - 1:M (?) with table "Employees")
[Est Compleation] (Date, short)
(.... then a whole series of fields named "Milestone... type)

JournalEntry:
{missing a primary key for this table}
[AutoNum] (FK: numeric, long - 1:M with table "Test_Table_for_sorting)
[Date]  (date, short)
[Journal_entry] (text)

How are these two tables related? Is this on the [AutoNum] field? If so, then I suspect they will not sync. When using autonumber fields, the field creates value that is independent from other tables. There needs to be a foreign key field somewhere and a relationship established.

Having the missing field properties would be helpful.

Having the DB table structure as I have shown in my first post and above will be helpful.

Once again... just as with the SQL... we don't need the actual data... we just need the skeleton and the tendons so to speak.

The SQL also gives the impression that the database may not be normalized enough; however, that is just a guess on my part.

Something to note: “Date” is a reserved word… using this as a field name may create problems for you in the future… you’d be surprised how many common words are reserved words and using them for field names and VBA variables will cause issues.
-z

[jmaher196]

To fill in the missing properties:

Test_table_for_Sorting:
Autonum: is a long integer autonumber, new values are random, Indexed with no duplicates.
Job: Is a text field that is not indexed. The basic outline for assigning Job# is SB-##-###. The first 2 #s represent the year the project was started, the last 3 #s represent the job number for that year. Ex SB-12-001
Type: A text field that is not indexed. The values are listed in a different table
Initiative: Same as TYPE
Project Name: A text field that is not indexed.
Customer: A text field taking data from another table. It is indexed. In the other table Customer is the primary key, linked to customer in the main table.
Project_MGR and TECH LEAD: Both are linked to a table listing all employees. Text fields that are not indexed.
Est. Completion: A Date/Time field, short date.

The milestone info within the IIF statement relates to a whole series of fields that track the current milestone and display it with the estimated end date for that milestone.

As far as the Journal Entry table goes:
Autonum: A number field linked to Autonum from Test_table_for_sorting. It is indexed with duplicates
Date: A date/time field using short date
Journal_Entry: A text field for the actual journal entry.

Thoughts?

[zmbd]

It will take me a few moments to think about this.

I edited my post in #6 to reflect the information proved in #7 for the tables.

From what you've stated, it does not appear that there is a primary key in the JournalEntry table. Therein may be your issue.

The muliple Milestone fields worry me a tad about the normalization of the DB you're working with.

-z

[ariful alam]

In your database system, the journal entry table entry each date like DD/MM/YYYY HH:MM:SS:xxx. when we enter date it basically takes the DD/MM/YYYY's value and other left like 00:00:00:000.

if you can entry with HH:MM:SS:xxx then, you can sort the data in descending order easily and get every Projects last journal entry.

[Rabbit]

You can't post the actual data but can you post some fake data and the results you would want from said data?

[zmbd]

[edit] using the long date format is [/edit] Certainly one solution and one I use myself for certain history logs in some of the databases we use inhouse; however, from the SQL provdided in #4 I get the impression that Jamaher is trying to relate the information between two or more tables and somewhere in the database there is either a missing relationship between the tables or some other logic error...

Once again... Jamaher having the basic table design and the relationships between the tables would be most helpful. I don't need actual data... just the table names, the field names, and any established relationships between the tables. Without that information I simply will not be able to provide anymore help with this question.

-z