Solved

comparing two tables based on two identical fields

Posted on 2007-12-04
10
439 Views
Last Modified: 2013-11-27
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.
0
Comment
Question by:mastro78
  • 6
  • 4
10 Comments
 
LVL 77

Accepted Solution

by:
peter57r earned 500 total points
ID: 20410354
Using a query is the simplest approach to the first part of this.
I don't understand the third bit and the second bit doesn't make sense.

QrySetPresent:

Update [Index] inner join [Details]
on Index.filename = Details.Filename
Set Index.Filestatus = "Present"

You then say 'When Details has a FileName that doesn't compare to Index, the word "Missing" is stored in Index.FileStatus'
So you want to set Index.FileStatus to 'Missing' if the record is not present in Index.  So which record in Index would you be setting to this value?

As I said I don't understand what you want to do in the third step.
0
 

Author Comment

by:mastro78
ID: 20410548
Actually I mistyped, there will be no need for that piece.
0
 

Author Comment

by:mastro78
ID: 20410563
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.
0
 
LVL 77

Expert Comment

by:peter57r
ID: 20410693
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?



0
 

Author Comment

by:mastro78
ID: 20410733
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.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 77

Expert Comment

by:peter57r
ID: 20410815
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(:-)



0
 

Author Comment

by:mastro78
ID: 20410883
True.  I was debating on whether or not to.
0
 

Author Comment

by:mastro78
ID: 20410899
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?
0
 
LVL 77

Expert Comment

by:peter57r
ID: 20410947
I think you start another Q.  Then you will get more people looking at it.
0
 

Author Closing Comment

by:mastro78
ID: 31412696
Always very helpful
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now