Hi there
Just registered on here as I'm in rather desperate need of help. Firstly allow me to say I am not a database expert, I've had minimal training many many years ago in systems architecture & database principles but we're talking first year Uni stuff. I'm also in a position of "there's noone else to do it", as opposed to being the designated database chap at work.
In the attached file you'll see a rough layout of what I'm trying to achieve. First and foremost I want to pull all active jobs from the Jobs table (using an Is Null on the job records Date Out field, not relevant here to include on the diagram). Separate to this, each job has notes attached to it in a separate table, using the JobRef field as a key between the two, and NotesID as it's own local primary key.
What I am trying to do is create a weekly report, where I can hit a button and:
1 - it lists all open jobs
2 - it only includes notes that have been added in the last 7 days
2a/3 - if there have been no new notes in the last 7 days then I want either a blank box, or a "no new updates" response
I've had mixed success.
I can create a report of all open jobs that displays ALL notes. Not wanted by our clients.
I can create a report of open jobs with notes made in the last 7 days, but ONLY jobs with new notes. Open jobs without a new note aren't listed. Also not wanted by our clients.
I need to make a list of ALL open jobs, showing ONLY new notes IF there are any, and if no new notes then either a blank field or a message to say no new updates/notes.
So far I've been trying this mostly through queries, nested in different directions. I've tried filtering on notes age first then open records, followed by open records then notes age. I've tried appending notes + date into a single output to avoid staggering many empty notes boxes on the report, also to no avail. I even tried my hand at doing the entire thing in VBA and doing the note age checks there, also to no avail.
Just registered on here as I'm in rather desperate need of help. Firstly allow me to say I am not a database expert, I've had minimal training many many years ago in systems architecture & database principles but we're talking first year Uni stuff. I'm also in a position of "there's noone else to do it", as opposed to being the designated database chap at work.
In the attached file you'll see a rough layout of what I'm trying to achieve. First and foremost I want to pull all active jobs from the Jobs table (using an Is Null on the job records Date Out field, not relevant here to include on the diagram). Separate to this, each job has notes attached to it in a separate table, using the JobRef field as a key between the two, and NotesID as it's own local primary key.
What I am trying to do is create a weekly report, where I can hit a button and:
1 - it lists all open jobs
2 - it only includes notes that have been added in the last 7 days
2a/3 - if there have been no new notes in the last 7 days then I want either a blank box, or a "no new updates" response
I've had mixed success.
I can create a report of all open jobs that displays ALL notes. Not wanted by our clients.
I can create a report of open jobs with notes made in the last 7 days, but ONLY jobs with new notes. Open jobs without a new note aren't listed. Also not wanted by our clients.
I need to make a list of ALL open jobs, showing ONLY new notes IF there are any, and if no new notes then either a blank field or a message to say no new updates/notes.
So far I've been trying this mostly through queries, nested in different directions. I've tried filtering on notes age first then open records, followed by open records then notes age. I've tried appending notes + date into a single output to avoid staggering many empty notes boxes on the report, also to no avail. I even tried my hand at doing the entire thing in VBA and doing the note age checks there, also to no avail.
