H
HelenE
Guest
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).]
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).]