Storing a single date field, and querying to identify if it is a Start or End Date in Access Forms

[skidawgs]

Hi!
I am having a problem with finding a way to identify whethera value stored in a “ModDate” column is a start date or an end date. This Date field is stored in the [“MOD”]table, which is connected to [“POSITION”] via a many-to-many relationship with [“POSITION_MOD”]in the middle. Within the [“MOD”] table,I just have the ID, a Mod_Number, and the ModDate. Within the [“POSITION_MOD”] Table I have boththe POSITION_ID and MOD_ID as Primary Keys and Foreign keys, along with aModAction (Added/ Removed). TheModAction identifies whether the Mod will be adding or removing the specific position. The ModAction field should be used to identifywhether the ModDate field should be the Added_Date or the Removed_Date for the correspondingMod.
Ideally, the Added_Dateand Removed_Date columns will be populated from the ModDate column, and will beidentified as the Added/Removed date from the Action. For example, if a ModDate is 1/1/2019 and theModAction value is “Added”, then the Added_Date within the POSITION_MODtable will be the “1/1/2019” value fromthe ModDate. I would like the value (ifpossible) to be calculated within a query, so then I can easily set controlscorresponding to the Added_Date and the Removed_Date on my main form.

• I am thinking that a SQL query to decide whichtable to put the data into could be best.. (maybe a CASE or a query criteria)but I am not sure how to go about doing so.

I know this may sound a little crazy, but [I think] it isthe best normalized structure for storing the Date and Action for a Mod, sincea Mod always has one date, but a single Mod can be both add and remove various different positions. Anyfeedback is greatly appreciated, as I have had no luck for quite some time.

Thank you for all responses!

[June7]

A single mod action affects multiple positions? Seems odd.

Do you mean 'SQL query to decide which field to put data into'?

So join tables then options:

1. calculate Added_Date and Removed_Date fields with IIf() conditionals and GROUP BY position

2. CROSSTAB

3. query for Added dates, query for Removed dates, join those queries to Position

[orange]

"..a single Mod can be both add and remove..."
Would a ModType or TransType field with possible values Add or Remove (or ???) clarify the situation?

[skidawgs]

Thank you for your response!
Yes, thank you for the clarification. I would like the query to decide which fieldto put the data into.

• If The ModAction value = “Added”, Then theModDate value should be in AddedDate, and vice versa for web the ModAction value = “Removed”, then the ModDate value should be put into the RemovedDate instead.
  

I do agree that it’s odd, yes. But for example:

Say aMod is created, Mod# 10-1
ThisMod can Modify multiple positions at once; adding 5 new positions andsimultaneously removing 3 others.
I’m going to take your advice and see what progress I canmake. Using IIF() along with GROUP BY may be able to offer just what I’mlooking for.
Thank you!

[skidawgs]

Hi Orange,

Thank you for your response!
I do have a ModAction field now which has the values “Added”or “Removed”, however I am not certain as to how to use this field in a queryto actually make the ModDate field go into its corresponding AddedDate orRemovedDate field. For example, if theModAction for a particular record is “Added”, then the ModDate value should besent to the AddedDate instead of being used in the RemovedDate.

[orange]

You may be missing a table or a concept where an Action or Transaction can apply to 1 or Many Positions.
I'd advise you to confirm your data structures with your business facts. Again, sitting way outside the details and environment, it's easy to "suggest".
It sounds like a Mod can do something or several somethings (eg. 1 add or 3 adds and 2 deletes) which seems to be what you are dealing with.
Good luck with your project.