Hi Im back

I was basy trying to get my first access database right but I couldnt - in a way I suppose I gave up. Ive been trying to get it going on my own and have come to realise that I need help. This forum gave me alot of help in the past and Im turning to it for more help. If you look at my earlier posts you will see that my database is alot more advanced that the one Im posing here. Im busy working through the previous one to try and get the theories ,etc. to make sense in my head. Yes Access is a difficult thing to learn but Im determined that I will accomplish my goal - being that - I know how to design a database and to see it work. In actual fact Im designing this database to make my own work easier, currently I do far to much multiple entry of the same data which is a waste of time and the obvious errors that it can cause are causes for alarm. Im designing one section at a time because since there are different sections in my database trying to do them all at the same time confuses me alot - not good at all when you trying to learn something. Im currently making tables and relationships, and although it seems simple enough I have hit a snag.

I can understand why the 5 tables have been created (also their relationships) - Im following a previous design of my database. But where I get stuck in the tblAddresses ----- why do the two sub tables come off this table, surely in the main table (tblCompany) an entry for AddressID can exist and the 2 subtable coming off that can be tblAddressTypes and tblCities? Am I wrong, please explain?

I can understand tblContactDetails needing to be a separate table because it houses details about a seperate element of information (and it is linked via the CompanyID field) but when it links to the junction table I get completely confused.

I though the object of making separate tables was to stored separate types of information, but why are some tables given sub tables (I sort of understand that this is because of different types of information are needed (like a postal address and a street address but wouldnt the same purpose be served by just adding an AddressID field could be added (renamed to PostalAddressID and also another StreetAddressID - do you understand my point - I also realise that there might be several addresses (like multiple postal addresses, etc) Addresses are somewhat finite - we have a street address, we may have several postal addresses and several delivery addresses - why the need for a table with subtables, help I cant figure this one out.

Ok, just took a quick look at your database. To answer some of your questions.



You have the "tblAddressTypes" table. This would hold your different address types. In "tblAddresses", you would have a drop-down for the "AddressTypeID" that would contain the "Addresstypes" that are in "tblAddressTypes" table. This way, you would simply select it instead of having to type it each time.

For "tblCities", it's the same exact concept. Except this would be using the "CityID" field. Again, this would be a drop-down, so you're not having to type the city name each time.



"tblJunctionContactDetails" is to allow you to assign multiple contact types for a single contact. This table is also tied to "tblContactTypes". This would be the different types you have. So with " tblJunctionContactDetails" You would be assigning a contact and a contact type for each record, so John Smith might appear like this.



A bit confusing I know. Once you work with it for a little while though, it gets easier and starts making sense. If you start putting forms or queries together with this, I believe that will really help you understand the logic.

Your structure indicates that a company can have many addresses. If you put the address in tblCompany, you would basically say that a company only had 1 and only 1 address. So what is correct for your application? If you a providing products to a company and you send the products to one address but send the invoice to another, then you need the design that you have. As AccessHawaii explains, having the address type and cities joined off of the address table helps to eliminate retyping information which makes your data more consistent and allows for more accurate searching. What happens if you left the user to enter the city name and they misspell the name? Your searches may not pick up all of the information you are looking for.

I am thinking your tblJunctionContactDetails table might be incorrect. I am guessing that the contact types should be phone, fax, cell phone and tblJunctionContactDetails should look like this:

tblJunctionContactsDetails
-CDTid
-ContactDetailsID (defines the person)
-ContactTypeID (defines the method of contact cell phone, fax, email etc.)
-txtContactInfo (holds the actual value of the cell phone number or fax number or email address)

With the above structure, you would eliminate the fields: phone, fax, cellphone,email from the tblContactDetails

I could be wrong but I think the junction table for contact details is correct. I have desinged a number of databases similar to this structure and normally the purpose of that table is to record the means they used to contact the client as you said e.g. phone, e-mail, fax, etc. They're not concerned with the actual phone # or e-mail they used, that's usually stored in the main contacts table. They utilize this to typically determine which contact means are proving the most effective, so they know where to focus their efforts.

OK, sorry, I misinterpreted its purpose.

Thanks for your input guys. Im going to bow down to your superior knowledge that the junction table is correct. Thanks for explaining the tblAddressTypes - I think I better understand it now.

Natrually this is only the beginning of my database, in the future when making tables, what should I keep in mind when doing a table/subtable/etc. scenario. I see why we tabulate different related bits of information - actually it is all related to the main table but we further organise it in say ADDRESSES, etc.

I have been through the data I wish to store - obviously I need to do that regularly and incessantly until I have all the fields I need to have. But in your opinion is my table structure "complete" - I want to force myself to complete a section of the database before moving onto the next section - I think Ill learn faster that way. Trying to think and plan for every possibilty is impossible to wrap my head around at this stage and I have experienced myself that it is very confusing and leads to "I cant do it so Ill leave it" - not the attitude I need. I want to get this done and learn something in the process, further elements of the database will present new challenges and pitfalls but hopefully the more I progress the more ill know and the less Ill get stuck.

It's hard to say if you're table structures are setup correctly without knowing the purpose. If what I outlined with how the tables are setup is how the logic is supposed to be then "Yes" the tables are setup correctly.

This stage of my database is to keep customers details, eventually it will include stock sent and a few other things, I havnt reallt decided if it will include something to track inventory - what I do know is that I want to enter a list of spares that we have on a accounting system and then pick from it the items sent to a customer.

The way that it is setup now with the tracking of how customers are contacted is common in marketing type databases where they want to determine which means is the most successful for contacting customers. I'm not sure if that's what you're after. If you're trying to track a history of items sent to customers then you would want another table that would tie to the customers table, which might contain information such as date sent, stock type, date customer responded etc.

Thanks

I do want a history of what customers have ordered - for stock planning purposes and maybe other uses. Also if would be helpful to know how customers were contacted last and whether it was successful - but isnt keeping track of that a bit out of the scope of just the section im doing now?

The way that it is setup now is that it is tracking how customers are contacted. What is the end result that you're after? This almost sounds like an order placement type database where you would have an invoice and associated line items (products ordered). With this each customer could have multiple invoices and each invoice can have multiple items ordered, so all of this would tie together, so you can see exactly what each customer is ordering.

My database:
I want to keep track of customer details (addresses, phone numbers, etc.)
We also have suppliersand I need to know their details aswell
Those customers order parcels for me to send
The suppliers make our spares, some send our orders to us, some we have to arrange collection of
I need to keep track of what customer has ordered what (and their history of orders)
I need to keep track of what has been ordered from who (and the history of orders)
I havnt quite decided if I need to incorporate inventory yet - seems like a bit of a mamoth task, but our accounting program (pastel accounting) does keep a list of items to compose invoices out of - the accounts lady here does not want the inventory on pastel and there is no way to keep records on parcels sent. I am going to have to make a sales order on the accounting program and enter the parcels details on the database, I will not be allowed to try and get the database and the accounting program to be joined. All I can do is manually update the 'parts' list on the accounting program on the invetory section of the database. So basically it is going to be a bit of double entry (on the database and on the accounting program) with an inventory list of just the items on the accounting program list of parts.
Therefore I want to create a database to track customer details and parcels sent and collected. I had help previously from this forum and that is how I got the table structure Im using now.
I think seeing that Im keeping track of the customer details I need not worry to much about a 'working' contact detail, but what is important is the history of parcels I have sent to a particular customer.
Part of my job to to maintain the customer details and to keep the records as up to date as possible, also to sent parcels, arrange parcel collections, it seems to involve more and more of order placement - then receiving the order and checking. This is now all done manually and on excel with alot of double and triple entry - I often see mistakes that cause later headaches.
I think its very important that the table structure is correct from the beginning, it is no use (well little use anyway) to build forms, queries, etc. on structure that isnt correct.
Any questions, just ask? It would be nice if someone built my database for me but I am capable of doing that, besides if someone builds it for me what will I learn? - nothing.

If you're getting your feet wet in Access, this is going in full-force, LOL. What you are doing will definitely be challenging. You're going to be needing a fair amount of tables. Once you do this though, you should have a very good understanding of tables and relationships. I'll try looking at what you're doing a little later and see if I can offer any suggestions.

Thanks for the support. I post and upload when I next get stuck.

Hi, I have discovered something that my databse is missing. The postal address(es) of the companies. In tblAddresses are fields for Street address etc. do I add P.O. Box address fields to this table - I think this is the right thing to do. But it also got me thinking, perhaps tables should exist for each of the types of addresses (Physical addresses, postal addresses and delivery addresses). I dont know if this is neccessary since Im sure the existing table for addresses (with a few extra fields can handle all the types of addresses and the addresses themselves. If it is neccessary for the extra tables I am going to need help with those tables and relationships. My last upload contains my lastest database. I am also trying to populate my tables so I can see the results of queries. If I have a company with an international address but I still want to save the postal address - how do I go about that - I would assume an extra table.

This post has been edited by nickynoo: Feb 24 2012, 04:54 AM

This depends. If it's a matter where each customer will have a shipping address, physical address, and postal address then those fields could be added to your existing table. The physical address might be what you always use for correspondence, the shipping address might be what is always used for deliveries, etc.

Just got your reply now.

I have added the relevant fields to my tblAddresses.

Good luck with your project.

Hi

I havnt had much free time lately, but have managed to get a form sortof done (it stills needs quite a few things). Im uploading my database so it can be looked at.

There is a problem I face: you will see there is a lookup table for Open and Blacklisted, this is so we can see at a glance if a company is "open" or "blacklisted" - the problem is a company may be blacklisted for several reasons (I cant create a lookup table because there will not be common reason for blacklisting. The company may owe us money therefore we need to know not to send a parcel to the company, there are also other reasons - all different. My thinking, a lookup table with OWE MONEY or OTHER with a subtable coming off it A MEMO FIELD for the reason and a number field for the amount owed. The amount owed will be filled in manually since this is not an accounting application and very few of our customers owe us money. How do I do this if it is correct?

Another thing I want to do is "highlight" a field for easy indentification, I would think I do this with conditional formating - correct or not? How do
I do this? Say if the particular company is international I want that field saying INTERNATIONAL is appear with a red background and white writing. (just an example)

Thankyou

I forgot to upload my database.

Nick