DB Design questions

[speakers_86]

I am trying to build a db for the office I work at. And I have a few questions about how I should design it.

1. My vendor number should be an autonumber, but it has a relationship to the vendor name on a report. I cant create a relationship btwn text and autonumber.

2. Right now I have 3 main tables. Vendor, builder, and job. All of this is for the marketing aspect of the business. But I want to include other aspects, like collections and payroll. Should I add columns to the jobs table, or create a new table? For example, on the jobs table, I already have job info like details on the job and who referred it to us. Should I add invoiced amount, amount collected, etc., or create a table called collections to store all of this info? Im really confused about this.

 

[Fifty2One]

Perhaps this will lead you in the right direction...
http://www.access-programmers.co.uk/forums/showthread.php?t=121117

 

[speakers_86]

thanks-but thats not really what i need.

how can I change the vendor number to autonumber? It tells me something like data types are inconsistent. but if i delete the relationship, my report is wrong.

 

[The_Doc_Man]

Your problem is that if the number has meaning it should not be an autonumber. With a few exceptions (for instance, look in the forum's FAQ section for a post of mine), autonumbers are meaningless. If your previous numbers had meaning or an imposed pattern, they will not be suitable for autonumber conversion.

 

[KKilfoil]

If you use a field of type number/integer, then you can match with a pre-existing vendor number (VN). You then have to ensure that you are inputting a valid VN in your new tables.

Making a join in the relationship window with Referential Integrity (RI) enabled should ensure a match.