Too many tbls to allow data entry?

[HelenE]

I taught myself how to use Access and have done some simple designs. I'm using Access 2000 to help a friend out with her struggling home repair business by designing a way for her to track the different contractors she uses for different repairs. The problem is that these contractors will be in business today as (an example) "Nifty Drywall Specialists", and next month they change their name to "Drywall Specialist, Inc." She was using an excel file to store an alphanumeric id ("Nifty Drywall" was N100), but then had to re-enter them as "Drywall Specialists", D100. aLSO, contractors who install drywall may also be a company who does painting. She was losing track of these companies in the excel file and had a hard time trying to find painters only or dry wall installers only. And some of these contractors have a physical location address and a billing address.

My proposed table structure is:

tblComp......CompPrimaryID

trelNamesToComp......CompPrimaryID
.....................NameID
.....................TypeOfCompany

tblNamesOfComp.......NameID
.....................NameOfComp
.....................DateNameEntered

tblAcctNo............AcctID
.....................AcctNo
.....................DateAcctNoEntered

trelAcctNoToComp.....AcctID
.....................CompPrimaryID

tblAddresses.........AddressID
.....................AddressType
.....................LineOne
.....................LineTwo
.....................City
.....................State
.....................Zip

trelAddressesToComp...AddressID
......................CompPrimaryID


I have researched my books, some newsgroups and Access Help, but can't find any examples that are this complicated. I've tried to have the tblComp with CompPrimaryID acting as the main form with subforms fro tblNamesofComp, but since both PK are autonumbers, I can't enter new data into the subform and because there's key filled in on the main form.

I'm afraid I will have to tell my friend to hire someone to do this! Does anyone have any suggestions for me? I appreciate any constructive suggestions. Thanks in advance


[This message has been edited by HelenE (edited 08-10-2001).]

 

[jimbrooking]

Helen,

Good for you for trying something this ambitious. I started this way around 6 years ago, and am definitely still learning after writing a dozen or more fairly complex DBs. If you would zip your database up and email it to me I'll massage it a bit and send it back. (Outlook 2000 Sr-2 will not let me look at an attached .mdb file). I think the area you feel you're "stuck" at is fairly common.

If you'd rather hack at it yourself, a couple of suggestions: For starters, I would let my forms be driven by queries rather than tables, so you can do some logic in the query, not in the form.

Next, you need to define the parent-child relationship between main form and subforms. Done properly, the autoindex for the main form will propagate into subform records when the subform records are stored.

HTH,
Jim

PS - Leaving town Tuesday 8/14 and out of touch with all technology until the week of 8/27. I may not be able to help until I return.

[This message has been edited by jimbrooking (edited 08-11-2001).]