Database issues, table relationships

[pd4life2000]

Hi All,

My name is Paul and I am new to this Forum, so I firstly wanted to say hello to all and glad to be here.

I need some assistance with a work related database. I am currently developing it as a project but have run into a wall with the design being my first time.

I have to create a customer service and invoicing database. I currently have the following tables:

Client (Customer No, User Profile, Password, Customer Name, Acc No, Policyholder, 100% Indemnity)

Contact (Acc No, Date Signed On, Company Name, Contact Name, Address 1, Address 2, Address 3, Address 4, Address 5, Post Code, Telephone No, Fax No, Email)

Product (Product ID, Product Name, Service Level, Product Code, Price, Subscription Details)

Client / Price (Customer No, Product ID)

Usage (Order Number, Day, Month, Year, Subject Name, Client Reference, Product Code, Company Name, Customer No)


I have managed to link Client and contact no problems at all. I have also managed to link Client to Product using the table Client / Price.
I have tested this with queries and can get all of the data I need extracted out.

However the problem occurrs when I try and link the Usage table to something or other. The Usage table is data that I need to import on a monthly basis from Excel. Once I get it, I need to load it into Access and then generate usage statements for each of the clients to show what products they have ordered and how much each report has cost them.

To make matters worse, each client has 14 different products to choose from, and all clients have different prices for each of the different products.

Getting this to connect properly is an absolute nightmare.

Can anyone assist me in how I might link the Usage (imported table) to the rest of the database? I have attached a copy of the relationships on a word document and wonder if anyone can put a finger on what I might be doing wrong.

Hope someone can help.

 

[CraigDolphin]

I think the problem here is that you need to do some reading about database normalization and some rethinking about which entities you have identified to base your tables on. The key here is that your data model should reflect the real world as closely as possible.

How constrained you are with the Excel-import approach vs entering the data natively into Access via a form (a much better approach if at all possible)? How do you account for quantities in your table?

I've had a crack at a rough example db to get you thinking. This example uses your non-normalized usage table (and an extra unit-quantity field), gives companies discounts based on which price type (or pricing band) that company is assigned to. (eg, charities get a 30% break, small businesses 20%, large 10% etc). Once you have done whatever you plan to do with the data in the usage table, you then archive it before importing new usage data.

This approach will still give you problems IMO. For starters, you need to be darned sure that any company/Product names in your import file already exist in your db (with all key fields filled in and no spelling errors). My point here is that you will need to ensure data rules and referential integrity are maintained between your spreadsheet and database. If you were doing all your data entry in Access forms then the db would do it for you, the way you have outlined makes your life potentially much more difficult. You can make it work, but you need systems in place to prevent trying to import data about a company or product that your db knows nothing about.

You need to assign a price band/type to each company in the db if you want anything other than the current full retail to apply.

Also, this example does not have forms setup for reviewing the company, client, or product information. You would need to make them.

Now, back to pricing. If you really meant that the discount for the company changes from product to product then you will need to extrapolate from this example. For example, you could do it by creating a table to link productID, company, and discount rather than using the pricetype table. Just remember that you will need to populate that table somehow and you will need to do this for each product/company combination.

Or you could create a ProductPriceGroup Table that matches a product with a PriceGroup, then create another table (CompanyPriceGroupDiscount) matching CompanyId with PriceGroup and setting the discount rate there. Then lookup the relevant discount depending on the company and the PriceGroup the Product belongs to. Remember, the db cannot tell you something that you haven't told it already.

If you can move to entering your data directly into the access db via forms rather than importing from Excel, I think you will make your life 1000 times easier in the long run.

 

[pd4life2000]

Many thanks for your help with this.

The main problem is obviously my Usage import data. Unfortunately as we operate a service for our customers through another company, they ultimately provide us with the data shown in usage, in that exact format. So without manually labouring through 400+ records monthly, it seems a little difficult.

With regards to the prices, each customer is not set a specific percentage off of an original price. All prices are allocated at a salespersons discretion and the prices are not necessarily based on any particular criteria for calculation. The only way I could think of doing it was to create a record for every product for every customer and use a Product Id to make them unique.

From this it meant that the only way to link customer details and product prices / details together was to have a link table and have a joint primary key. In the join table I could put the price and subscription details in there instead of in the price table. Would that make any difference?

I think basically Client and Contact are fine, but where the price is coming into play is the problem.

The usage table is actually generate from the records we have sent, so the names and details should be the same for the account numbers and the client names.

I apologise about this, it really is doing my head right in.

 

[pd4life2000]

OK This is as far as I have got with fresh data from the start. I have managed to get all of the links to adhere to referential integrity which is better than before.

Just need to work out how to get the usage statement in with or without a relationship.

 

[CraigDolphin]

A good part of my problem in assisting you is that you have never explained clearly how you intend to use this database and have only vaguely described the real world situation that you're attempting to model. The real world situation drives the design of your tables and the table relationships with each other. How you intend to use the db drives the implementation.

For example, you have never explained what the USAGE table actually represents. It may be obvious to you but it is not to me. If it is a record of 'sales' from an agent company then it should contain quantities and unit prices paid. It currently doesn't, so I'm baffled as to what it actually represents.

All prices are allocated at a salespersons discretion and the prices are not necessarily based on any particular criteria for calculation

Then your model is just not going to work. Remember, the db cannot tell you something that you haven't told it already. Think about what your statement above means: Salesperson One gives a 30% discount on an item.....Salesperson Two later gives a 35% discount for the same item to the same customer. So, for your model to work, you need to account for salesperson, product, and customer in your 'FigureOutThePrice' table. Then, what do you do if that blasted salesperson Two forgets what discount they gave last time and gives a different discount second time around? Think about how many possible permutations you have to deal with here and then add on some kind of date range for which that discount should be used. And if these salespeople are employed by an agent company, how do you get the sales prices unless they are provided in the usage table? Even if you can get the information in another way, you will still need to input it into the db. If they are employed by your company, you will need to enter the sale price into the db no matter what.

What you probably need to do is simplify: have a field to store the amount paid for each 'sale'. On your db form, you lookup the default price to populate that field initially and then let the user modify the amount for that sale if they want to provide a spot discount. Then store the unit price actually paid in a table that matches up product, customer, and order number. However, this requires entering in those actual prices for each sale in the database unless you can get them supplied to you in the USAGE table.

Have a look at the attached example. Add a new order and see how the default price is populated using the default and then can be modified to whatever the user wants. Have a look at the table structure to see how the data is stored. Your situation will be somewhat different since you want to use the usage table somehow, and you may not intend to use the db the same way that this was designed for, but it should show you the direction you need to go with your design.

Unless you can provide a clearer explanation of exactly what you are trying to do with this db, and what the real world constraints are, then I doubt I can help you further.

 

[pd4life2000]

OK, heres the scenario in full.

Our service is called the Infoline product, a credit check reporting facility. Although we sign clients up to the service and ultimately send them their bills, they actually access the service through another company in which we part own called Graydon.

E.g. Graydon provide the ultimate service overall.
Infoline sell the product as our own
Graydon therefore develop the usage
Infoline choose the costs for our clients

Therefore we can never change what information is provided in the Usage table. What comes in is as follows:

Order Number - This is just unique for each order
Day, Month, Year - The date of request
Subject Name - The name of the company they have obtained information
upon
Client Ref - Customer Reference for clients purposes
Customer Number - The account number referred to by Graydon

We do not have any way to change this information, this is what we get and this is what we have to deal with. What I have been trying to do is get this data into a format that can be put into an access database without typing it in manually.

The problem we have is that Graydon have an account number for each account and then we have an account number for each account.

Our account numbers start with INFO and then a number. Graydons account numbers are 5 digits long and are allocated as they see fit.

This is not me trying to complicate things, this is the information I have to work with. To be fair my boss is an idiot and don't think he understands how complex it is.

What I really want is a database that can allow me to enter clients and their details, give them a product price for each product and then when the usage data comes in from Graydon, import it in and match up the prices to the orders.

Then put all of the requests for each client for that month into a readable and presentable format that I can send out to our finance department for distribution.

It just seems complex to me, and I thought there might be an easier answer.

 

[CraigDolphin]

Ok. I'm not asking details just to be nosey...it helps me to understand your data, and how you plan to use it, which will hopefully allow my advice to be better targeted and more useful.

So, let me paraphrase my understanding of your process to see if I have the right picture so far.

Your company has clients that purchase a service from another company through you. Your employees quote prices for these services to the clients and 'sign them up'. (I'm assuming that signing them up means you somehow tell graydon to provide service to the client or somesuch?). You want to be able to enter this information into the db for later retrieval. During this process you issue an Infoline account number for the client.

Graydon then provides the services to the client and later reports to you what services were rendered to the client; using Graydon's account number ([Customer No]) as a reference. The [Subject Name] field is NOT the name of the client but is some entity that the client is interested in obtaining information about. The [Customer Ref] field contains some text chosen by the client but does NOT contain Infoline's Acount Number.

You want to be able to use the graydon information together with your intial quotes to generate billing details that Infoline can then use to invoice the client?

Assuming that the above is correct, then I think you can readily accomplish what you want.

However, the key to this messy situation comes down to this: how will you match up records in the Usage table with clients that you have already entered into your database? Now it looks as if the [Customer Name] field in the Usage table contains the name of the client. I assume that you will also keep the Client's name somewhere in your db. Provided that you use the exact same convention to store the name that Graydon uses then you ought to be able to use this as a key field to link up your data to Graydon's data. However, you need to be wary of this because a key field MUST only contain unique values. What if two different clients with the same name sign up? How will you differentiate them using their names alone? Ordinarily, you avoid this trap by using something like your Account number to reference the client. But since Graydon does not use your account number in their records you are going to be vulnerable to this issue unless you can force Graydon to also include your Account Number in their reports.

So, am I on the right track so far?

 

[pd4life2000]

Wow and theres me thinking that I haden't explained it correctly again.

Thats exactly how it is. In the database attached (I have attached it once already but just in case) within the table called 'Client', the Customer No is actually the number Graydon uses for generating the Usage. There is another field in the same table called Coface Acc No, which is our reference for the client and what we do the billing by.

Prior to importing that data, I could using Excel look up our account number and match it with theirs. Then the Usage Table would have an extra field for our Coface Acc Number, that is the number that starts with INFO once it is imported into the Access table.

Hopefully that makes sense, if not then keep drilling me and i'll keep typing, hehe.

 

[pd4life2000]

Wow and theres me thinking that I haden't explained it correctly again.

Thats exactly how it is. In the database attached (I have attached it once already but just in case) within the table called 'Client', the Customer No is actually the number Graydon uses for generating the Usage. There is another field in the same table called Coface Acc No, which is our reference for the client and what we do the billing by.

Prior to importing that data, I could using Excel look up our account number and match it with theirs. Then the Usage Table would have an extra field for our Coface Acc Number, that is the number that starts with INFO once it is imported into the Access table.

Hopefully that makes sense, if not then keep drilling me and i'll keep typing, hehe.

 

[pd4life2000]

Wow and theres me thinking that I haden't explained it correctly again.

Thats exactly how it is. In the database attached (I have attached it once already but just in case) within the table called 'Client', the Customer No is actually the number Graydon uses for generating the Usage. There is another field in the same table called Coface Acc No, which is our reference for the client and what we do the billing by.

Prior to importing that data, I could using Excel look up our account number and match it with theirs. Then the Usage Table would have an extra field for our Coface Acc Number, that is the number that starts with INFO once it is imported into the Access table.

Hopefully that makes sense, if not then keep drilling me and i'll keep typing, hehe.

 

[CraigDolphin]

Ok...we're making some progress then.

I've thrown together another db based on our discussion so far. Hopefully it will get you started. The general concept is that you will have to 'import' your usage table and use an append query (not created yet) to add the new records to the 'GraydonServicesRendered' table. Then use the main form to enter the start date of the period of interest and you should see the results in the subform. You could modify this to also use an end date for the period too if you like. I've also tried to clean up some of the (to me) confusing terminology. I couldn't tell how you were using the word 'contacts' so I've used 'ServiceEvents' to denote an interaction between a sales rep and a client.

This db will also require forms for entering client information, ServiceEvents and quotations for products, sales rep details, and Infoline account details (including the graydon customer number etc). In this model, an Infoline 'account' can have more than one client (eg a company has an account but several people may work for that company and interact with your reps) but a client can only ever be linked to one account.

I've also made some assumptions:
1. You want the most recent price quote that was given prior to the actual service date to be used.
2. The GraydonCustomerNo field will always be available in the InfolineAccounts table prior to import of new data from Graydon.
3. If no quote was given for a particular product to a client prior to the service then the price used will be $0 with a note explaining the lack of a quote. (You could also include a field of default prices in your product table and use that value instead of 0)
4. The usage table delivered to you contains batches of data that do not contain records previously provided to you (ie new data only)

Some other issues that arose:
In your version of the products table you had several 'products' that shared the same 'Product Code'. Since Graydon is only reporting to you with the 'Product Code' field there is no way to differentiate between your products that share the same 'Product Code' field. Consequently, your product and quote tables need to match this.

You also have something called 'service levels' which we have not talked about yet. I do not know how they play in the grand scheme of things. I have assumed that service level is associated with an account. If this is incorrect, you may need to rethink the relationships to best deal with the real world situation.

Have a look at the db and how the queries work to provide you the pricing information etc.

 

[pd4life2000]

Apologies for the contact issue. Contact was actually a table to detail contact details for the client. If we needed to speak to a particular client in some way shape or form then we would have used those contact details.

Our sales people do not actually speak to the clients once the product has been sold, as once signed up, all of the prices they have been quoted for each type of product remain the same, unless they are renegociated.

The service received for all products is a pay as you go service. So the client would just order their product through graydon and then this order would get added to the usage statement which we then receive and have to import into access.

I am now thinking that perhaps some of the details from the InfolineAccounts table could be transferred to the Client table. Then the InfolineAccounts table would contain only the contact details used to contact the client if necessary.

The ServiceLevels table is basically just how the data is accessed and is really only necessary for our own reference. We actually know how each of the products are accessed as each product has only one way of being accessed, from one of the options listed in the table ServiceLevels.

Would I be right in thinking that the GraydonServicesRendered table are orders that have already been incorporated into the database. And what you mean by appending the new data would be for when the new usage statement comes in and the new orders are listed, I would add them to the database under the GraydonServicesRendered table using some sort of append query?

I think were almost there, i've had a look at append queries and they seem pretty easy to do.

 

[CraigDolphin]

I am now thinking that perhaps some of the details from the InfolineAccounts table could be transferred to the Client table. Then the InfolineAccounts table would contain only the contact details used to contact the client if necessary.

I think you still haven't quite grasped why there are two tables here. A client is a single person. Details pertaining to (depending on) that person belong in the same table as the person identifier. Thus, 'contact' details for a person belong in the Client table. An Infoline Account is, essentially, a chargeline. Each account is a single entity. Now, if you can say that you will NEVER EVER, in a million years, have a situation where two people might share an account number, then you could merge the two tables. I strongly doubt that is the case, however.

Let's say a company called ACME Noseyness Ltd sets up an account with you and the 'person' who calls to setup the account is called Willy X from a regional office in Seattle,WA (Client#85, ph 555-1234). All is fine until Willy's boss from head office in Houston,TX Sylvester (Client#206, ph 555-9876) calls up two years later and says, hey, you guys are too darned expensive. Then, your sales rep has to renegotiate the price or lose the custom.

So, now you have a situation where the same account is being shared by two different people (clients) AND you have different pricing before and after the renegotiation.

Which of the two phone numbers and addresses do you want to keep? You can only store one per account the way you have suggested. Moreover, if the only thing in your infoline accounts table are contact details for one person, then how will you handle staff turnover at a customer's company? [You would need to take the data from one client record and copy it to a new client record, or alter the information in the existing record ( which would mean then that your historical data will show you talked to 'Billy Bob' 3 years before Billy Bob replaced the original person 'Sally Mae' who actually made a call three years ago). If your records confuse something so simple it will be a poor reflection on your company's ability to keep tabs on what is going on.]

The data structure I suggested explicitly allows for this situation. I think perhaps the only thing you might want to do different in the infoline accounts table is to store the ClientID of the current primary contact in the account holder's company. This then will allow you to have a way to get hold of the account holder and preserve your data integrity without a lot of work.

And what you mean by appending the new data would be for when the new usage statement comes in and the new orders are listed, I would add them to the database under the GraydonServicesRendered table using some sort of append query?

Yes, the general idea is that Graydon sends you a usage table of new data since the last usage table. You import it to your db and then check to make sure all of the [Customer No] values in the usage table are already present in your InfolineAccounts table's [GraydonCustomerNo] field. If not, you need to add them to the appropriate Infoline account before proceeding other wise you will get a key violation error when you run the append query.

Finally, you run an append query on the new usage table and add it the records to the GraydonServicesRendered table. Then you can run your query for the new dates and do whatever you want with the query results (export to excel, print a report, whatever).

 

[pd4life2000]

Ok I understand what you mean by the client and Infoline Account tables, that makes sense.

Shouldn't I then need to change these tables around to reflect the real world? Client should contain details of a client within a company who deals with our company and include their contsct information, such as address and telephone number etc. Then InfolineAccounts would be the table holding the account information such as the account numbers, login details etc?

I also wanted to check something about the ServiceEvents table. The Client ID in this case is the company that has been quoted a price for a product. The Rep ID field is the sales rep who has made that quote for the client. The ServiceEventDateTime is the time in which the quote was made for the client.
Have I understood that correctly?

I am also struggling to understand how Client and Infoline Accounts are linked. I thought that the relationship here would be the other way around as 1 client, in the database attached, can have many InfolineAccounts associated. Or have I misunderstood that one as well.

I think i'm beginning to understand how it all works, just taking a little time to sink in.

 

[CraigDolphin]

Shouldn't I then need to change these tables around to reflect the real world? Client should contain details of a client within a company who deals with our company and include their contact information, such as address and telephone number etc. Then InfolineAccounts would be the table holding the account information such as the account numbers, login details etc?

Yes. All fields in the client table depend soley on the client. All fields in the infolineAccount table should depend solely on the Infoline Account.

about the ServiceEvents table. The Client ID in this case is the company that has been quoted a price for a product. The Rep ID field is the sales rep who has made that quote for the client. The ServiceEventDateTime is the time in which the quote was made for the client.

Almost. The ClientID identifies the person/client who talks with your rep. This person may represent a company, or be an individual, but they will be associated with a single Infoline Account number. Because you quoted prices to this person, and the db knows which account they represent, then the quote also applies to the account. You have the RepID and SalesEventDateTime fields figured out.

I am also struggling to understand how Client and Infoline Accounts are linked. I thought that the relationship here would be the other way around as 1 client, in the database attached, can have many InfolineAccounts associated. Or have I misunderstood that one as well.

As I stated in post #11, this model allows for several people to be linked to one account but each person can only be linked to one account. I was proceeding on the assumption that a company who signed up with you would be assigned one, and only one, account number. If your real world situation could have one person being linked to several account numbers then you need to redesign the db to explicitly allow for that possibility.

Try thinking of it like this. The InfolineAccount Table is basically equivalent to a Customer_Companies table, where your account numbers are unique to each company. The Client table is equivalent to a CustomerCompanyEmployees table. However, if you need to have several accounts for each company, then you'll need to create a companies table and build a junction table between the Companies and Infoline Accounts tables. Employees/Clients would need to be associated with the Company table. You might also need a three way junction table between InfolineAccounts, Companies, and Employees/Clients to specify which employees are linked to particular accounts. You will also need to redesign the queries majorly, and maybe need to add an Infoline Account number field to your SalesEvents table since you can no longer depend on the one account per client/employee assumption to identify the relevant Infoline Account number from the ClientID.

As you can imagine, this situation rapidly becomes much messier than the example I provided and you may be better off hiring a professional db developer to develop the database.

 

[CraigDolphin]

Also, I should mention that this model is far from complete. You need to consider things like which mailing address to use for an account. You could approach this by having 'accountaddress' fields in the InfolineAccounts table, or by identifying a primary contact in your Client table (will require an additional field) so that the db looks up the primary contact for each account and uses their address information when developing a mailing list for each account.

 

[pd4life2000]

I'll be absolutely honest, im having trouble just adjusting the tables around in the database. I tried to change the name of Client to Infoline and the InfolineAccounts table to Client. The whole thing went a little wrong, even when I changed the fields around, such as putting the client ID in the client table and the InfolineAccountNum in the Infoline table.

I linked them all up and changed the names shown in the queries but for some reason, I keep getting prompted for parameters in some of the queries when I load up the database.

The queries you have designed are really clever, but I really can't get my head around the coding and structure of some of the stuff in the design view.

Could you explain how the queries work together, if I can get a better understanding I might be able to see what angle you have come from with the design etc.

 

[CraigDolphin]

Hmm. Well, the reason I try to post example dbs is that it's usually much clearer to see a working example than it is to try and explain it all in english. (And it avoids me saying X will work if X really doesn't! )

There are four queries in the example I sent.

The first (GraydonServicesRenderedByDate) simply lists all the usage in the GraydonServicesRendered table and also lists the InfolineAccountNumber associated with that customer. This lists is restricted to services rendered after a user-specified date (this parameter is set by the user in the text box on the form).

The second (qryPricesQuotedByGraydonIDAndDate) simply lists all price quotes, broken down by product and date, given to customers, using their GraydonCustomerNo to identify the customer.

The third query (qryServicesRenderedVersusMostRecentQuote) lists everything from the first query add substitues the product name instead of the product code, and adds two more fields. One which uses a combination of the Dlookup and Dmax functions to find the last quote given to this customer for this product prior to the date the service was rendered (using the information in query 2 as its data source).

The other field looks up the ServiceEventID corresponding to the quoted price.

The final query (qryResults) re-lists everything from query 3 but using Dlookup and Dmax to find the ServiceEventID from query 2 and then using that ID to get the quote date from the ServiceEvent table and thence the rep name from the Rep tables.

I'm not sure how helpful this will be to you but your question is so broad...

If there is a specific function or item you have a question about then post back and I, or someone else, may be able to assist you. Before doing so, though, make sure to use access help and search the forum for assistance on the topic. There are many tutorials and explanations for table design(normalization), join types, parameter queries, and functions like Nz, Dlookup, DMax etc already on the web. Everything I know about Access I learned by reading web-based tutorials and searching (primarily on this forum) or trial and error. The preceding terms would be a good list of search words to try if you have questions.

Rather than trying to change your existing database, why not simply use the model db that I gave you, remove the fake data in the tables, and repopulate it with information from your db? Just remember that you need to fill in all the tables that are on the 'one' side of the one-to-many relationship before you can populate the 'many' side tables. Then you can add whatever forms/additional queries you need without needing to reinvent the wheel.

If you really can't move forward from here, using google/forum-search/access-help together with a working example db, then maybe you need to re-evaluate whether this is a project that you should be attempting without involving a local, paid, professional developer. I'm happy to help but we've gotten well beyond the scope of the original question re:table design. After all, your company will be using the db to generate billing information so you need to be confident in the output.

Also, bear in mind that I'm not a professional db developer. The queries I constructed work AFAIK, but it is entirely possible that a professional can come up with something much more efficient. Still, the underlaying, normalized table structure will be a solid foundation for whatever method that you use to extract the data you want to report (given the limitations of the Usage table that you must use).