AutoNumber 1

[neszz]

I have a question regarding AutoNumbers. I have a couple of tables that have ID, that I want to be auto-increment. I do not really want to have it on the form but incremented with a value or decremented if the record is deleted. This ID field name is also a PK to relate with other tables. How do you increment auto and not include it on the form? I have like 4 IDS and they should not be shown on the form, maybe one field name but the rest, no. I tried to select Autonumber on Datatype, but when I relate one ID (with Autonumber) with a same ID on another Table(which is also Autonumber), I receive a box that says index incorrect invalid. Something like that.

Any advice will help. Thanks

neszz

 

[willir]

This question has been posed in several variations in this forum. Do a search for more info.

Basically, autonumber is not a record counter - it is a simple way of defining a unique field.

If you want to capture the record counter, then you would have to use some code. For example do a record count before inserting the record. But there is a risk in using this approach, especially if using it for the primary ID.

Now your deletion issue. Lets say you have 100 records, and you have numbered them sequentially. Now you delete record #2. Do you really want to renumber the remaining 98 records?

My advice is to treat the autonumber as a unique identifier and not to let the broken sequence bother you.

Richard

 

[lespaul]

In response to your other issue, to relate a PK Autonumber field in one table as a FOREIGN KEY in another, the FK field type should be long integer.

tblCustomer
CustomerID (PK - Autonumber)
CustomerName
CustomerAddress
etc.

tblOrders
OrderID (PK - Autonumber)
CustomerID (who placed the order FK to CustomerID in tblCustomer FieldType = long integer)
OrderDate

You cannot have two different Autonumber PKs relate to each other. Won't work.

Leslie

 

[neszz]

That makes sense. I have not put my PK ID's to autonumber. My three ID's act like autonumbers that they are just used for incrementing. I thing I do not want, is to have three text boxes (of these PK ID) on my form. I do not want the user to control the number sequence of the three ID's. What do you guys recommend. For example

CustomerID
InvoiceID
CostID,

All these ids plus the rest of the tables are on the form. I do not want the user to control the id or even see it. I tried to not include it to the form, but I receive errors since they are PK and need a value.

neszz

 

[lespaul]

If they are the PK of the table then you can make them autonumbers.

So, you want a new invoice, add a record to that table (the InvoiceID increments), now you have a new customer for that invoice, there should be a way to add a customer from the invoice form. Add a new customer (the CustomerID increments), the new customer's ID (newly created from the add customer) is entered into the CustomerID field of the "new" invoice that has been waiting.

No need for controls on the form!

leslie

 

[neszz]

I tried to relate one of my PK called Property ID to a link table, which has a FK called Property ID. I receive a message box called, "Invalid field definition 'PropertyId' in definition of index or relationship" the field properties are the same, contain increment and is index but do not allow duplicates. If both Id's are index and do not include duplicates then it is a one relationship, but if one is not index and the base table id is, then is is one to many.

Any advice?

neszz

 

[willir]

I am going to assume your data types differ. Make sure your PK and FK are defined exactly the same. For example long integer.

Next, how are you establishing your links or relationships between the tables? What are you linking? And how are you trying link them?

There are several ways to link tables. I feel the best and simplest is to use the graphical relationship tool. With the table view on top, select "Tools" -> "Relationships". Add the tables, and then join the tables by selecting either the PK and FK and dragging it to the other field. By using this mehtod, Access will enforce the relationships you define.

Moving on to what.... Modifying Leslie's example...

tblOwner
OwnerID (PK - Autonumber, usually defined as a 4 byte long integer, could also be defined as a 16 byte replication ID)
OwnerName
OwnerAddress
etc.

tblProperty
PropertyID (PK - Autonumber)
OwnerID (who owns the property FK to OwnerID in tblOwner FieldType = long integer )
PurchaseDate

... Moving on to linking the data in a form

I am going to assume that you either have a one to one or more likely one to many relationship. One owner can own several properties. Two typical ways of presenting this information is a) Owner in a main form with the properties displayed in a subform, or b) the property form displays the owner information, perhaps in a combo box.

I hope this helps. Once you get the process worked out, it becomes fairly straight forward.

Richard

 

[neszz]

I have many tables and I am using the graphical tool to link tables. Majority of my Pk's and Fk's should be autonumber because they are all Id's. For example, PropertyId, OwnerId, LoanId (Two or more loans), CostId, etc. I use these Id's to connect to my Property base table. The relationship are manily one to many. My whole objective is storing data and then uploading only one property. For example one form would be just used for one entry and then it closes and take you to the menu screen of my program. Even though, so far my form allows my user to enter another property. I guess in my button I put exit sub or something. Anyways, another form that I have is to find only 1 property to look at. So this form, will allow the user to first search through county, which is a drop down box, then search by city, which is also a drop down box. Then I believe the rest should be in a subform, or.. it does not really matter, just not alpha between property address, so the user can select one property to pull up. The end result, is selecting one property and then the whole form you will only see one property. This is just a brief description on what I want to do.

Back to my problem, I tried to change my PropertyId as PK and FK between two tables. I allow index to be yes in both and do not allow duplicates. I try to link the base PropertyId to the child table PropertyId(FK), but I receive, "Invalid field definition 'PropertyId' in definition of index or relationship." At this time, I just drag every single field name to my form, even though I have like over five ID's with mandatory textboxes. I did this because I was stuck but I needed to get this project finish as a.s.a.p. At the same time, hoping to figure this out when I finish up some other details of this project.

Thanks for your advice, and I will try this again.

neszz

 

[lespaul]

Ok, let's start with one table at a time. It would help if you would tell us a little more about your exact structure, but here's an example:

tblOwners
OwnerID (Autonumber - PK)
OwnerName
etc


tblProperties
PropertyID (Autonumber -PK)
OwnerID (Long Integer - FK)

In your relationship window you are going to select tblOwners.OwnerID and drag it to tblProperties.OwnerID. This will create the one to many relationship between Properties and thier owners. If the tblProperties.OwnerID field has an INDEX set to NO DUPLICATES, there is NO WAY that Access can set up a ONE to MANY relationship between the tables! That is why you are getting the error message about the index and relationship!

The Primary Key of any table in Access is already indexed and implicity declares no duplicates.

Likewise if you have a table of counties:

tblCounties
CountyID
CountyName

and you list properties by county:

tblProperties
PropertyID
OwnerID
CountyID (long integer - FK)

tblProperties.CountyID CANNOT have a no duplicate index declared or you will only be allowed each county in tblProperties ONE TIME!

Hopefully that will help you to figure out what you need to modify.

Leslie

 

[neszz]

That help a lot. It make it easier for me to understand. I am going to give you more info because my parent table only has one duplicate and that is PropertyId. Here is a link of my relationship table. This make it easier for me to express my situation.

http://www.eklectics.com/relationships.htm

Below is the info on the PK and FK

Property_tbl1 (Base Table)
PropertyId -PK Long Integer, Index: Yes (No duplicates)
County -PK Text (Lookup table to child table: County_tbl1)
Text, Index: NO
CostId: Single, Index: No

Child Tables:
Retail_tbl1
CostId -FK, Single, Index: Yes (No Duplicates)

tblEmployeeInfo
EmployeeId -PK, Index: Yes (No Duplicates), also has a Validation rule for names of Employee.

tblLoanProgram (Used to link tables, had problems w/o it)
PropertyId -Long Integer, Index: No
LoanTotId -Single, Index: No
LoanNumber -1st mortgage : Single, Index: No
LoanNumber -2nd mortgage : Single, Index: No

County_tbl1 (I have another table for this that has two columns, which one lists counties, and the other cities. In the future should replace this, so the form can have two combo boxs, one county and the other cities)

County -text, Index: Yes (No Duplicates)

Loaninfo_tbl1
LoanTotId -Single, Index: Yes (No Duplicates)

Loan_tbl1
LoanNumber -Single, Index: Yes (No Duplicates)

Loan_tbl2
LoanNumber2 -Single, Index: Yes (No Duplicates)

Thats it. If you need more info in understand my situation, let me know. One more thing, So for on my form, every single field name is on it except the table tbl_LoanProgram. This means, I have all PK-Id's on my form, which is a waste of space.

neszz

 

[willir]

neszz

Thanks for the graphic for your relations. It really helps.

I see a couple of problems which may lead to limitations later when you get more data. You may wish to address them now in the design stage, or accept the limitations.

Consider moving the tenant to a separate table. Why? Contact info may change. May have more than one contact. Contact status may change. For example, husband and wife assume tenantship; they split; one remains as tenant. The remaining tenant is in default and skips. Since you have the table setup with only one contact information, you will not be able to find the other partner as a way of finding the skipped tenant.

Tenant_tbl
ContactID - primary key
PropertyID - foreign key
First_name
Last_name
Phone_number
Work_number
Alt_address

Since a property may have one more than one tenant, you have a one-to-many relation which is why I included the PropertyID on the tenant table. (You can also set this up as a many-to-many relationship since a tenant may flip from property to property, enter into sublet situations, etc. A M:M would allow you to track this type of info, but not a 1:M)

The alternate address field(s) would allow you to track tenant of debtor's address.


Consider linking the loan(s) with the tenant and not the property. Property can be used for collaterol but it a person or company who who is responsible for the loan.

Moreover, since one person can have many loans (1st mortgage, 2nd mortgage, car loan, line of credit, etc) and a loan can have more than one debter, you actually have a many-to-many situation here. You attempted to acknowledge this by using two tables to track loans.

This would be my suggestion for tracking loans...


Loan_tbl
LoanID
Loan_Number
... much of what you have seems okay (another topic all together)

Assuming we will follow your line of thinking with the LoanInfo_tbl, how does this link the loan and the owner. From my perspective, it should link to the loan table itself - regardless of who payes, the total table still belongs to the loan. (Would this information be part of the loan table itself? I really don't know; I would have to ask questions.)

And why a Loan_Number? This is descrationary, but as with cheque numbers and invoice numbers, I assume you want to use a reference number for the loan and as discussed, using the autonumber may not be the best way to do this. You have various options, but could just come up with your own numbering system. A common approach would be to incorporate the date such as 4E07-xxxx 4=2004, E=May type of thing. Admitedly, it would be the loan number that would be used as primary key, but for simplicity, it may be easier to use the autonumber for linking tables. Your choice. It is possible to use the system to generate the loan number - it would take a bit of code to do it properly.

To prevent the number from being edited once assigned, make sure on the form, you lock the field.

This leaves the loan table as such...

Loan_tbl
LoanID - Primary key
Loan_Number - indexed, no duplicates
LoanTotID - foreign key to Loan Total, one-to-one
LoanType - type of loan
... plus your other fields

Using this approach, you can see the loan total for the loan, etc.

But we have not linked the loan(s) to the tenant(s). Since this is a M:M, you have to use a joining or intermediary table.

TenantLoan_tbl
TeanantID
LoanID
LoanResp - need this to track a co-signer situation

(Remeber the Alterate address field? You will now have the ability to track the address of a co-signer of a loan.)

...Moving on
It is not really necessary to have a country table. You can use a combo or list box and use a value list for the tenant and property addresses.

Consider adding more info to the employee table. Their name, start of service, etc.

...Other stuff
Some of your concerns can be addressed at the form level. For example, you can hide the ID fields by setting it's property.


Leslie star for you for your excellent deduction the index setting preventing a 1:M


To sumerize...
- Move tenant info to a separate table
- Create a M:M relationship for the loan and tenant
- Visible Loan_Number for reference

Richard

 

[neszz]

Thanks Richard for everything. Also I like to thank everybody else for posting. As a beginner in Access I have been running into a lot of problems but at the same time figuring them out with your guy's help. I am going to reread this post and revise my project. At the same time, I am also trying to learn how to retrieve this information from a form. Do you know of any post or link to learn how to retrieve specific records for example, by county, by city, etc.? I need to keep learning and reading. Of course, you will hear back from me about the relationship. I will be up all night again until I get this project look "good."

Thanks!

neszz, Niles

 

[willir]

There are several good books to consider purchasing. The hands-on are good for starters, but then you will move up to more advanced ones which deal with solutions and coding.

Run a search for books on this site for more info.

Per retrieving data from forms. An unbound combo box or list box is a common approach. Check out the forum on Access forms...

http://www.tek-tips.com/threadminder.cfm?spid=702

Richard

 

[lespaul]

At this point you don't even need to be thinking about the forms. Once you get a normalized table structure, then worry about the forms (the Access Wizard will make it very easy once your tables are all straightened out!).

Check out 'Fundamamentals of Relational Database Design', it will help A LOT!


Leslie

 

[neszz]

Leslie,

you are right. I read it once already, and thinking about reading it one more time. It is a good tutorial.

Niles