how do i create this relationship

jhahes

Registered User.
Local time
Yesterday, 16:46
Joined
Jul 13, 2005
Messages
20
Could someone please help me with the following problem.


I have a report that downloads into excel from a loan software program.

The report has lots of data for each individual loan number


What I have done is link this report to access as a table.

The problem I am having is how do I create a relationship with that table.

I want to create a status report that does the following

There are about 15 steps in the loan process, and I want to add 3 fields to link to every loan number (possibly a subform)

description of event
is it completed(y or n)
date completed

can someone please help me with any ideas

thanks

Josh
 
First things first...

If you want to form a relationship to a spreadsheet you might have trouble because you can't make a spreadsheet have an index. (Unless that feature has been added recently and I missed it, which I admit is possible.)

Second,

If the spreadsheet is external to the DB, that means that if you DID have a relationship, it is subject to the whims of someone else - the person who accidentally deletes the xls file or replaces it. Because if Excel is running and Access isn't, poor little dumb Access won't know the file is gone until someone opens the mdb file and tries to use that table.

I would import the spreadsheet from the .xls file to a temporary table in the mdb, then filter it to remove duplicate entries, then append what's left to the "real" table in my mdb, then populate the other tables with which I had built the relationships. If it is critical to my business operation, I would NEVER allow the data to be externally contained (with respect to my mdb file) on a long-term basis.
 
i like that idea better

because the loan application generates its reports to excel.

if the excel data will always have the same number of fields, just more or less records,

can i just import the excel sheet into the existing table or will it duplicate the records?


Josh
 
If this database or result is critical to your business, I would not stay with your existing design. I (personally) would consider it WAY too risky.

You can generate spreadsheets as output from a table or query so it should be possible to replace the spreadsheet if whoever wants it just HAS to have it in spreadsheet form. But for reasons stated earlier, I consider it incredibly risky to leave it in that form for processing.
 
need more help

I am a beginner in access, have lots of experience in excel.

We input data into this loan software program...

BorrowerName
Address
City
State
Zip
Email
CoBorrowerName
MarriageStatus
LoanAmount
TitleCompany
AppraisalCompany
etc...

The loan software allows you to generate reports with specific fields, and it automatically dumps this data in excel as book1


What I am trying to do is get this data so it doesn't have to be re-entered

the unique key would be the loan number which is generated by the loan software

Then I want to be able to incorporate a status report with access

for example

for every loan

there are about 10-15 steps

1. pre-approval
2. 72 hour disclosure
3. prelim conditions
4. order title
5. order appraisal
etc...


and I want to be able to say if each step is done (yes or no)

and

the date it was done

and be able to do this for each loan (record)


could you show me something simple to get started in designing this


thank you for your help and patience


Josh
 
If this is business-critical, it is worth paying a consultant to build this for you. I cannot take the task on because my time is strictly limited right now. I would have a lot of trouble coming up with a proper design because of your comments about having complex business steps for loan processing.

The only person who can do this is you. Start by doing an on-paper data flow and processing analysis. If you can't describe the process to be done you will not be able to get Access to do it. That is because of the old programmer's rule: A computer won't tell you anything it wasn't told ahead of time. If you can't give it the rules and it is YOUR business, there is no way I will be able to do better.

That is not an attempt to brush you off. I'm pointing out that you have asked a class of question that is ill-suited to this forum's format. Perhaps another responder can offer you some advice. At the moment I cannot.
 

Users who are viewing this thread

Back
Top Bottom