Table Setup Suggestions?

[AC5FF]

I am tracking extreme useage on account numbers. I've set up a table to add the information on a daily basis, but I'm looking for suggestions on if I have things set up the best way possible...

Currently the table has the following Fields:
Report_Date / Account / Usage / Sent / Received / Comments / Notes
(the Sent and Received fields are Y/N)

I've gotten a report to send working as I want, but I am running like 6 queries to put all the data together correctly. For example; if an account was flagged today and sent today I do not want to send another notice for the next 5 days; but I still want to log the data in the table for historical reasons... Again, this all works. Just that it is not pretty..

The part that I keep fighting is the responses. When I receive a response I want to log that. Right now, I check the Received flag, and add in any comments the customer sends back. This is easy. But I also want to print that information out on the report - just the latest information. For example, if I send a notice out on 11/1/12, 11/18/12, 12/14/12, and 1/10/13 and I have responses back from all of these logged in the table, when this account number gets printed again - say today - I want to add to that report JUST the comments from the 1/10/13 report.

I have not gotten this part to work correctly yet. Still working on it, but this morning I got to wondering if I could set up my table differently - or use a seperate table for responses - to get this to work any easier.

Anyone have any good thoughts/comments?

Thx!

 

[Rx_]

So, you have one table - with six queries running against it.
A query might proved a result set - for a condition (or set of conditions).

Perhaps, you add a few more fields related to status at the end.
Run an Update query once. The update can call on either SQL and/or VBA Function to update the values (e.g. flag, 5 day notice, "Archived", ...)
Your many report(s) can now filter on the fields values to produce the expected output.

Just an idea.

 

[AC5FF]

If I am understanding you correctly::
I could add a new field - we will call it "5-Day" as a Y/N field.
With an update query, instead of my query that has 5 or 6 subqueries in it, I could filter off of this.

GENIOUS! I will have to work on that...

My attempts however at pulling out the latest reply - I.E. the one on 1/10/13 and flagging it have been very meager to plane not working. I will look at using this same type of idea - running an update query and using another flag field for what responses to use.

Thanks!! Gives me some good ideas on ways to move forward!

 

[AC5FF]

Testing taptalk access here...

Think this works. .. I did some editing today and I think I can make this work! Ive got to figure out the query between select and update, but it should work!

Thanks again!

 

[kayjenx]

Have you tried using another table for replies, linking to the account number, then have a query from most recent to oldest and show only top result? Replies table can contain fields acct/reply_date/ReplyComments.