Created a form from a table, have I done this the right way around

[jzwp11]

It looks like the sales prospect and sales prospect notes tables are properly joined. You would need to bring both tables into a query in order to show both the prospect & their respective notes.

The company detail and contact tables are not joined, but it does look like you have the foreign key field (Company_Unique_ID) in the contact table to make that join.

In general terms, I see some issues with the database. First, you have a number of lookups (combo/list boxes) in your tables. Although Access has this capability, it is generally recommended to not have lookups in the tables; they are best left for forms. This site explains the problems the table level lookups can cause.

Also it is recommended to not have spaces or special characters ($,#,&, parentheses, brackets etc.) in your table and field names because otherwise you will have to enclose them in square brackets in queries and in code. Changing them at this point will impact the forms and queries you have already created, so I will leave it up to you whether you want to do it. The spaces and special characters issue is not critical but the lookups mentioned previously is an issue.

In your company details table, you did not require the company ID. Since you are using this as the primary key, it has to be a required field, so you will need to take care of that.

I noticed in the sales prospect table that you have repeating field names: Service1, Service2..., quantity1, quantity2...

These repeating fields indicate that your table is not normalized. If a sales prospect can have many services, that describes a one-to-many relationship which requires a separate table. Similarly if a prospect has many software/hardware items.

Also, the software and hardware tables have similar structures, why not combine them into one table (Items) and just have a field that distinguishes the type (hardware or software). Also, I do not see the need for a quantity field in the software (or hardware) tables. I'm not sure how you handle services, but you might include various services as items in the item table with the hardware and software items. You would then have a third type: services in addition to the hardware and software types.

[Carla C]

Hello jzwp11,

thanks very much for checking this out for me. I will put all your thoughts into practice and test them out but I get the feeling that I am doing this the wrong way. Am I right in thinking that I should be creating the database using tables and queries and my boss should be using forms and reports to view the data put into it?? He doesn't like the way the queries are named, thinks it is stupid to have the words joined up etc but I guess you can name them what you like in the report??

What is a quick fix on this?

[Carla C]

I will come back to your comments asap too. I am working on other things today so struggling to find the time to sort this out.. typical.. all or nothing here!

Thanks again

[jzwp11]

The best way is to tie the query to a form. The users should be interacting with the database only through the forms. They should not see your tables (or queries). I typically create a main form with a bunch of buttons that open the applicable form based on what function the user wants to accomplish. The users only work with the data through those forms.

[Carla C]

The best way is to tie the query to a form. The users should be interacting with the database only through the forms. They should not see your tables (or queries). I typically create a main form with a bunch of buttons that open the applicable form based on what function the user wants to accomplish. The users only work with the data through those forms.

So the query I have that the boss likes selecting specific details from etc (Sales Prospect Query), I just need to create a form so he can do the same in that?

I have responses to you last thread, just need to find time to put fingers to keyboard. I am going to work on this over the weekend and get it set up properly hopefully!!

[jzwp11]

...boss likes selecting specific details from etc

I'm not sure what you mean by "selecting" in the above, but just as you can bind a form to a table, you can also bind a form to a query.

Try to enjoy some of your weekend!

[Carla C]

Sorry he would use the drop downs a lot from the query page to filter information he needs. e.g. how much hardware a specific company has bought etc

[Carla C]

[QUOTE=jzwp11;128504]It looks like the sales prospect and sales prospect notes tables are properly joined. You would need to bring both tables into a query in order to show both the prospect & their respective notes. I did this but it only picked up the SPN's with a note on, it would be good to display all SPN's and just have the last note appear.

The company detail and contact tables are not joined, but it does look like you have the foreign key field (Company_Unique_ID) in the contact table to make that join. Yes, I need to create a company unique ID as this will marry up with another system we have in place(eventually, we will have a system to do it all)

In general terms, I see some issues with the database. First, you have a number of lookups (combo/list boxes) in your tables. Although Access has this capability, it is generally recommended to not have lookups in the tables; they are best left for forms. This site explains the problems the table level lookups can cause.Is this specifically in the SPN record itself?

Also it is recommended to not have spaces or special characters ($,#,&, parentheses, brackets etc.) in your table and field names because otherwise you will have to enclose them in square brackets in queries and in code. Changing them at this point will impact the forms and queries you have already created, so I will leave it up to you whether you want to do it. The spaces and special characters issue is not critical but the lookups mentioned previously is an issues. Ok, I can change these to suit. Can I keep the forms as they are though with different names etc?

In your company details table, you did not require the company ID. Since you are using this as the primary key, it has to be a required field, so you will need to take care of that. Shall I take it out then?

I noticed in the sales prospect table that you have repeating field names: Service1, Service2..., quantity1, quantity2...

These repeating fields indicate that your table is not normalized. If a sales prospect can have many services, that describes a one-to-many relationship which requires a separate table. Similarly if a prospect has many software/hardware items.

Also, the software and hardware tables have similar structures, why not combine them into one table (Items) and just have a field that distinguishes the type (hardware or software). Also, I do not see the need for a quantity field in the software (or hardware) tables. I'm not sure how you handle services, but you might include various services as items in the item table with the hardware and software items. You would then have a third type: services in addition to the hardware and software types.[/QUOTE WE may have a customer who buys a number of different services/products. Shall I just create a table for them all then and keep it simple? Thanks again for all of this, I really do appreciate it

[jzwp11]

I go into some detail below about the queries modifications you will need, but at this point it would be better to read the last 3 paragraphs of this post regarding your tables since changing the tables will have a major impact on everything else.

I did this but it only picked up the SPN's with a note on, it would be good to display all SPN's and just have the last note appear.

To do show all SPN's with or without a note, you will have to adjust the join type between the two tables in the query. If you double click the join line in the upper pane of the design grid view, you will see 3 options for the join. You want to select the type that says to "Include all records from "Sales Prospect Record" and only those records from "Sales Prospect Notes" where the joined fields are equal. This is typically called a LEFT JOIN.

Taking your Sales Prospect Record Query and adding the notes table with the LEFT JOIN, the SQL text of the query would look like this:

SELECT [Sales Prospect Record].SPN, [Sales Prospect Record].Date, [Sales Prospect Record].Company_ID, [Sales Prospect Record].Company_Name, [Sales Prospect Record].Sector, [Sales Prospect Record].Description, [Sales Prospect Record].Region, [Sales Prospect Record].Country, [Sales Prospect Record].Motive, [Sales Prospect Record].Source, [Sales Prospect Record].Distributor, [Sales Prospect Record].Pursuit_Leader, [Sales Prospect Record].[Value_at_Sale(US$)], [Sales Prospect Record].[Positioning Profile], [Sales Prospect Record].Hardware_Product, [Sales Prospect Record].Quantity, [Sales Prospect Record].[Price (US$)], [Sales Prospect Record].Software_Product, [Sales Prospect Record].Quantity1, [Sales Prospect Record].[Price (US$)1], [Sales Prospect Record].[Service 1], [Sales Prospect Record].Quantity2, [Sales Prospect Record].Price, [Sales Prospect Record].[Service 2], [Sales Prospect Record].Quantity3, [Sales Prospect Record].Price2, [Sales Prospect Record].[Additional_1st_Year_Value(US$)], [Sales Prospect Record].Probability_Status, [Sales Prospect Record].Incumbent_Systems, [Sales Prospect Record].Order_Expected_Date, [Sales Prospect Record].Order_Delivery_Date, [Sales Prospect Record].Key_Contact, [Sales Prospect Record].[E-mail], [Sales Prospect Notes].Date_of_Note, [Sales Prospect Notes].Date_of_Note, [Sales Prospect Notes].Sales_Prospect_Record_ID
FROM [Sales Prospect Record] LEFT JOIN [Sales Prospect Notes] ON [Sales Prospect Record].SPN = [Sales Prospect Notes].Sales_Prospect_Record_ID;


Now if you want to show only the most recent note for those prospect records with notes along with those with no notes, that is slightly different. You will first have to create an aggregate query that finds the most recent note for each prospect record. The query would look something like this:

query name: qryMostRecentNoteDate

SELECT [Sales Prospect Notes].Sales_Prospect_Record_ID, Max([Sales Prospect Notes].Date_of_Note) AS MaxOfDate_of_Note
FROM [Sales Prospect Notes]
GROUP BY [Sales Prospect Notes].Sales_Prospect_Record_ID;


You will then have to join the above query back to the notes table. You will have to join by two fields, the sales_prospect_Record_ID of the query to the corresponding field in the table AND the MaxDate field of the query to the date field of the table. That query would look like this:

query name:qryMostRecentNoteByProspect

SELECT [Sales Prospect Notes].Date_of_Note, [Sales Prospect Notes].Sales_Prospect_Record_ID, [Sales Prospect Notes].Notes
FROM [Sales Prospect Notes] INNER JOIN qryMostRecentNoteDate ON ([Sales Prospect Notes].Date_of_Note = qryMostRecentNoteDate.MaxOfDate_of_Note) AND ([Sales Prospect Notes].Sales_Prospect_Record_ID = qryMostRecentNoteDate.Sales_Prospect_Record_ID);

Now to show this in the most recent note in your sales prospect record query, you would bring in the Sales Prospect Record table and the qryMostRecentNoteByProspect query. You again would make a LEFT JOIN between the two.

When you start doing queries like this, your users will probably not be able to edit any of the fields brought in by the query. They still should be able to edit fields that come in from the table (with the exception of the primary & foreign key fields of course).

In general terms, I see some issues with the database. First, you have a number of lookups (combo/list boxes) in your tables. Although Access has this capability, it is generally recommended to not have lookups in the tables; they are best left for forms. This site explains the problems the table level lookups can cause.Is this specifically in the SPN record itself?

The lookups are set up in the table. For example, if you open the Sales Prospect Record table in design view and click in the Company_ID field, look at the lower pane under the Lookup tab. The display control is set up as a combo box. To remove the combo box, change the Display control to a textbox. Also, if you have the companyID field in the table, having the company name field (also from the company details table) is unnecessary and is a violation of normalization rules. Further, having the company name in more than 1 table could impact your data integrity (someone might change the name in one table but not the other)

You also have lookups for several other fields including: sector, region, country, motive, source, distributor, pursuit_leader etc. These should all be change to text boxes.

In your company details table, you did not require the company ID. Since you are using this as the primary key, it has to be a required field, so you will need to take care of that. Shall I take it out then?

No, you need the field since it is the primary key, just change the required property from No to Yes (see lower pane in the table design view for the properties)

Also, the software and hardware tables have similar structures, why not combine them into one table (Items) and just have a field that distinguishes the type (hardware or software). Also, I do not see the need for a quantity field in the software (or hardware) tables. I'm not sure how you handle services, but you might include various services as items in the item table with the hardware and software items. You would then have a third type: services in addition to the hardware and software types. WE may have a customer who buys a number of different services/products. Shall I just create a table for them all then and keep it simple? Thanks again for all of this, I really do appreciate it

What I would recommend is to have one table that holds all items (hardware, software, services) you are selling AND another table that joins the prospect record table & item tables. Additionally, I would also have a table that holds the possible item types (hardware, software, service)

Like this:


tblItems
-pkItemID primary key, autonumber
-txtItemName
-fkItemTypeID foreign key to tblItemTypes (long number integer datatype field)
-currPrice (currency field)

tblItemTypes
-pkItemTypesID primary key, autonumber
-txtItemType

tblSalesProspectItems
-pkSalesProspectItemID primary key, autonumber
-fkSPNID foreign key to Sales Prospect Record table (long number integer datatype field)
-fkItemID foreign key to tblItems (long number integer datatype field)
-Qty
-price (this field is necessary if the list price in tblItems can be changed over time; in other words, having this field preserves the price at the time of the sale/quote)




The key to any successful relational database is the table structure (tables & relationships), so that should be properly set up before you worry about forms, queries or reports. So for now, that is where I would recommend you focus your efforts. You may also want to consider 1 table for companies whether they are prospective clients or distributors (which I assume are also companies) because the rule is: like data should be in 1 table. Similarly all basic information about people whether they are key contacts, salespeople, employees etc. should also be in 1 table. There is obviously a relationship between people and companies that will need to be defined. I would also guess that there is a relationship between countries and regions which you have not defined.

There might be other problems with your design but since I do not know the details of your application, it is hard for me to help without more detailed information from you, but perhaps reading about the rules of normalization will help. This site has a lot of information, but it is quite dry & technical.

BTW, here is the bad news. If you restructure your tables as I have recommended, most if not all of your current forms, queries and reports will not work properly and will probably have to be rebuilt from scratch.

[Carla C]

As part of the design, I was asked to create a contact list and a company list, should I combine them?. What is the quickest way out of this, do I just start from scratch? I have read your comments but the last 3 para's are worrying me!!

[jzwp11]

I assume a contact list refers to people and the company list refers to companies. Technically speaking you could combine people and companies (collections of people) in 1 table as follows, but some designers prefer to keep people in one table and organizations in another. But in either case, you will still have to relate people to companies (i.e. another table)

tblEntity
-pkEntityID primary key, autonumber
-txtPrimaryName (this would hold the company name or the last name if an individual)
-txtSecondaryName (this would hold the first name of an individual)
-fkTypeID (you could have a field to distinguish the type: supplier, customer, customer contact etc.)

To relate people to companies, you would have a table like this

tblRelatedPeople
-pkRelatedEntitiesID primary key, autonumber
-fkPEntityID foreign key to tblEntity (the primary entity like a company)
-fkSEntityID foreign key to tblEntity (the secondary entity--the person or contact at the specific company)


If you want to go with separate tables for people and companies, you can accomplish the same as above as follows

tblPeople
-pkPeopleID primary key, autonumber
-txtFName
-txtLName

tblCompany
-pkCompanyID primary key, autonumber
-txtCompanyName

tblCompanyPeople
-pkCoPeopleID primary key, autonumber
-fkCompanyID foreign key to tblCompany
-fkPeopleID foreign key to tblPeople

Which way you decide to go on the above with is up to you. I have seen both approaches used.

What is the quickest way out of this, do I just start from scratch? I have read your comments but the last 3 para's are worrying me!!

I think you should take a step back and get your tables fixed up. You do not have to get rid of them, just modify them & you will want to add some additional ones. As to the forms, queries and reports, it is probably best to start fresh on those since trying to fix them would probably take longer than just creating new ones.

[Carla C]

I think you should take a step back and get your tables fixed up. You do not have to get rid of them, just modify them & you will want to add some additional ones. As to the forms, queries and reports, it is probably best to start fresh on those since trying to fix them would probably take longer than just creating new ones.[/QUOTE]

This I will do, I plan to work on it over the weekend to get it fixed up properly and hand it over to the boss on Monday...

Have a great weekend

[Carla C]

I think you should take a step back and get your tables fixed up. You do not have to get rid of them, just modify them & you will want to add some additional ones. As to the forms, queries and reports, it is probably best to start fresh on those since trying to fix them would probably take longer than just creating new ones.

This I will do, I plan to work on it over the weekend to get it fixed up properly and hand it over to the boss on Monday...

Have a great weekend[/QUOTE]

I dont suppose you are working yourself this weekend? If I get stuck can I contact you?

[jzwp11]

I usually hop on the forum as time allows on weekends, but it is hit or miss depending on what is going on at home.

[Carla C]

Well, I haven't gotten very far with this, I have had a really busy weekend (painting and decorating!!) I only managed to pick it up
after 7 this evening so struggling a little with it. I need to sit down and go through all of your notes again. I dont really get why I have to create another table to marry to customer and company when primary keys and ID's are already set up for them... Getting late, will look again tomorrow.