Need data to auto fill from another table based on relationship

[amorphous]

I appreciate all the help & patience.

[June7]

I understand properties have their own address and customers have their own address. You want an address associated with work order.

The Address field in WorkOrders is set as text type. Should be number if want to save autonumber ID as foreign key. Same for Customer field in Properties. If you are not going to save autonumber ID values as foreign key in related table, don't need the ID field and certainly should not be the primary key.

Sounds like you want to associate either a property or a customer with a work order. This is not easily handled because two tables can be the source of info.

Seems to me a property should always be associated with a work order regardless of who is responsible for the payment of work. Maybe have PropertyID field in WorkOrders and a checkbox for BillTenant?

Surely you get a signed agreement from whomever is responsible for payment?

[amorphous]

There are multiple Work Orders for each property. I want the ID to be the WO #. I think that having property ID on the WO table is ok, but I won't need it on forms or reports necessarily. The property has to be part of the WO. I just want the customer because it helps me give a little input on who's house I'm sending people to. Each customer has their own preferences of cost & quality. I like the idea for the checkbox for billing tenant. I want the WO table to work in such a way that each time I feed in the data, I will have a WO form to print out & hand to a vendor. They will have portions that are blank to write in by hand to give back upon completion. I reference each WO#(s) in my invoices that are built on QuickBooks in case of need to go back & check who did what & when. Some invoices have several WOs because I send more than one vendor depending on what's needed to be done. Most of my customers are paying me weekly on numerous invoices. I only need signed agreements on big jobs or new customers. Also, I have tenants & vendors sign WOs because they sometimes make up stories when they get behind on rent. Hopefully, this gives you a better idea of what I'm working to create. I'm open for any suggestions. I'm actually trying to mimic a system that a programmer designed online for my previous property management company that worked great. I like the idea of being able to change it to my needs though. He's too busy these days to help me make a new one, so here I am trying out access.

[June7]

Don't see anything that changes my previous advice.

Might want a field in WorkOrders for InvoiceNo.

Or a table for Invoices and a related table for InvoiceDetails.

Advise no spaces or special characters/punctuation (underscore is exception) in naming convention.

[amorphous]

So should I set the addresses themselves as the primary key for the property table & the same for the customers for the customer table?

You said that it is difficult to have 2 tables as the source of info. I have to have the property being worked on in the WO table, but I still would like to show who the customer is on the WO as well.

It wouldn't make sense to make the address a primary key in case the customer changes. I sometimes do a remodel for an owner & then suggest the property manager to them to manage the property. Once they are managing it, they become the customer for all maintenance related work. Therefore, there will be some properties with 2 customers. It would be nice to make properties or customers inactive so that 2 of the same property addresses are never in a combo box at the same time. It's rare to have overlapping customers on a property at the same time.

The customer address is for billing sake only. I don't necessarily need it in this data base right now unless I'm able to expand it to do more in the future, such as invoicing. The customer billing address can be a property as well if they request work for their own office or personal home. I'm wondering if I should simplify the database by doing away with the customer table & just having a dropdown list only for customers in the property table. Or is that a bad idea?

For now, if I can just figure out how to make my WO table work properly when I add new properties or customers, I'll be in business. I can then put together some printable forms or reports & start keeping track of what's pending & complete. Lew me know if you think having the customer name on the WO is going to work or what's the best way to approach it. I know you've explained it to me at the beginning of the thread, but I haven't figured out how to apply what you've told me.

[June7]

No, I don't think the address should be the primary key, I am just alerting you that is the way you have designed the db because Address fields in Properties and WorkOrders tables are text type.

The customer is available to the work order though the Customer field in Properties table, which by the way is also a text field and therefore cannot save Customers ID value but must be the text Customer field value.

[amorphous]

I'm not understanding why an address would not be best as a text data type. You said that it should be a number but I don't see why. This confuses me. Also, I here you mentioning that the ID values aren't being used, but I'm not understanding if that is a problem or not. If I have to have ID values for these to work properly, OK what do I need to do? If I don't need them, I don't want them because they would serve no purpose on the WO table. Please tell me where I'm off.

How do I get the WO table to properly pull up the needed Address from a comobox & automatically fill in the customer field?

[June7]

If you want to be able to retrieve customer info, this means linking tables in a query. If you store customer name in Properties then the joining will be on the corresponding customer name fields. This is a poor PK/FK. It would be best to use the ID number as PK/FK to link tables.

Same goes for the address saved into WorkOrders. Address is poor PK/FK. Save Property ID into WorkOrders and link on corresponding number fields.

[amorphous]

I finally gave up. I can't figure out what you guys are trying to tell me to do or change. So I'm just doing away with showing the customer on the WO until later when it becomes clear how to do this correctly. I do however have new questions as I go on to the next parts of building this system, which I'll post in a new thread.

[cscura]

June7
You may have not been able to help this person out but you sure did help me out. Thank you so much for your information.

[June7]

Glad the info helped someone. Understanding use of PK/FK is crucial to relational database design.