mastro78
asked on
comparing two tables based on two identical fields
Tables: Details, Index
Fields: FileName, FileDate
What I would like to do is step through each record in Details, when the FileName data is identical between the two tables, the word "Present" is stored in Index.FileStatus and moves onto the next record to check. When Details has a FileName that doesn't compare to Index, the word "Missing" is stored in Index.FileStatus and moves onto the next record to check. When Index has a FileName that doesn't compare to Details, then that FileName, FileDate, and AppDetails (query) is run and moves onto the next record to check.
I hope this is enough information. I appreciate any help you can provide. I have a deadline tomorrow and this is the last piece to implement into the rest of my code and test. Thank you.
Fields: FileName, FileDate
What I would like to do is step through each record in Details, when the FileName data is identical between the two tables, the word "Present" is stored in Index.FileStatus and moves onto the next record to check. When Details has a FileName that doesn't compare to Index, the word "Missing" is stored in Index.FileStatus and moves onto the next record to check. When Index has a FileName that doesn't compare to Details, then that FileName, FileDate, and AppDetails (query) is run and moves onto the next record to check.
I hope this is enough information. I appreciate any help you can provide. I have a deadline tomorrow and this is the last piece to implement into the rest of my code and test. Thank you.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I do have another question off topic, what is the best way to store data using a function? I currently have a function where I'm calculating and then calling it using a text box source (ie =GetCalc). What would be a better way of doing that so that when I open up my form, the calculation is already in place in the field. Just a general answer will do...or if you need my code I can provide that as well.
If you have a calculated control it should produce the answer as soon as you open the form.
It should recalculate as you navigate from record to record or if you change any value in the record used by the calculation. Is that not happening for your control?
It should recalculate as you navigate from record to record or if you change any value in the record used by the calculation. Is that not happening for your control?
ASKER
The calculation works, but I want to store it each time since the factors that effect that control don't change too often so I'm running unnecessary calculations.
If the calculation is correct at all times then you should probably leave it as a calculated value.
One possible reason for storing the value would be the need to know what the value was when it was previously calculated, because the value might be calculated differently now. Another might be that the calculation involves processing 10's of thousands of records.
Otherwise don't store it. If you do, you can have my personal guarantee that one day the saved answer will be wrong because one of the values used in the calculation has changed but the stored answer hasn't(:-)
One possible reason for storing the value would be the need to know what the value was when it was previously calculated, because the value might be calculated differently now. Another might be that the calculation involves processing 10's of thousands of records.
Otherwise don't store it. If you do, you can have my personal guarantee that one day the saved answer will be wrong because one of the values used in the calculation has changed but the stored answer hasn't(:-)
ASKER
True. I was debating on whether or not to.
ASKER
I've been messing with my application to call items in different places and have basically screwed it up. Would you mind taking a look at my code for me? Or should i open up another question?
I think you start another Q. Then you will get more people looking at it.
ASKER
Always very helpful
ASKER