Form Filtering Problems Please Help!!

[jzwp11]

The first thing I would recommend is that you get a better understanding of normalization. This site gives an overview. The table structure (the tables and the relationships between them) is the most critical aspect of a successful application. It is basically the foundation of the database and must follows the rules of normalization.

Just some other general recommendations. It is best not to have spaces or special characters in your table and field names. I actually do not have spaces or special characters in any of my table, field, query, form, module, macro or report names. Also, although Access has the capability, it is generally not recommended to have lookup (combo or list box) fields at the table level; they are best left for forms. This site gives more details as to why the lookups at the table level are not a good idea. Also, there a reserved words in Access that should not be used as table or field names. This site has a link to those.



Just from what I have seen of your database, it looks like you are trying to catalog some kind of business opportunities. Can you provide some details as to what process you are trying to model with respect to these opportunities?

[emilyrogers]

Thank You for the links I will have a look through them!
I am not sure I understand your last question, the aim of the database is to keep track of business opportunities, so that the database can be used to see any outstanding work that has to be done on certain projects.

Re-Starting is going to be a big job isnt it?

Thank you so much for your help!

[jzwp11]

At this point it is hard to tell whether it is going to be a big job.

So some questions to try to flush out your table structure.

Can you have many contact people at a company or only 1?

Can there be many opportunities at a company/customer?

Do companies have multiple locations at which opportunities can occur?
Do you have separate contacts at a location?

Is an opportunity the same as a project or does an opportunity turn into a project at some point?

What triggers the change from opportunity to project?

What type of work with respect to the project will you be tracking?

Can many employees be involved with an/a opportunity/project or only 1?

Is a project associated with only 1 market segment or multiple?

[emilyrogers]

a) Contacts at a compnay is irrelevant because we do not make note of that here. It is just listed by company.
b) There can be more than one opportunity at one company/customer
c) Yes companies can have multiple locations at which the opportunities occur.
d) An opportunity will turn into a project if sucessful for example one of my fields is status- so the status can be quoted(means we got the contract and that will turn into a project) or enquired(still in the pre-contract stages) or dead(not gained)
e) The main everyday use for the form will be withthe comments and folllow-up section - for example the employee in charge of that opportunity will see when they next need to talk to that company, what outstanding tasks there are, and the results of any meetings or telephone conversations.
f) only one employee is in charge of that project and therefore the "sales responsibility" field will always remain the same for the duration of the opportunity
g) there is only ever 1 market sector per- opportunity.

I hope this answers your questions! Again, thank you so much for the help you have given me!

[jzwp11]

We have enough to get started laying out the tables, but we will probably encounter additional questions as we proceed.

I generally recommend having an autonumber, primary key field in every table. This field should have no significance to the users of the database; it is just a way for Access to uniquely identify each record. We will use this field to join two tables together.

I also tend to use prefixes in my field names that help me identify the data type of the field.

For example pk denotes primary key which I always have as an autonumber datatype which is also a long number, fk denotes foreign key and is a long number datatype. I generally use more descriptive names other than just ID for the key fields. I use txt for text fields, dte for date/time fields, long for other long number fields, cur for currency fields, sp and dp for single and double precision number fields and log for logical (yes/no) fields.

I will provide just the core structure of each table. We'll fill in the other fields later.

tblCustomers
-pkCustomerID primary key, autonumber field
-txtCustomerName

Since a customer can have many locations, that describes a one-to-many relationship which is handled with a separate but related table

tblCustomerLocations
-pkCustLocID primary key, autonumber field
-fkCustomerID foreign key relating the location back to the customer in tblCustomers
-txtLocationName
-txtAddress
-txtCity
-txtState
-fkCountryID foreign key to you table that holds the country names (from your original database)

tblCountry
-pkCountryID primary key, autonumber
-txtCountryName

A table to hold people involved (employees and others as necessary)

tblPeople
-pkPeopleID primary key, autonumber
-txtFName
-txtLName

A table to hold all projects

tblProjects
-pkProjectID primary key, autonumber
-txtProjectName
-fkCustLocID foreign key to tblCustomerLocations (joins the project to a specific customer location)
-fkPeopleID foreign key to tblPeople (identifies the responsible employee)

A table to hold the various statuses that any project can go through as you mentioned (quoted,enquired,dead,awarded etc.)

tblStatus
-pkStatusID primary key, autonumber
-txtStatus

Now a table to track the status of a project, assuming that a project goes through many statuses (one-to-many relationship) over time.

tblProjectStatus
-pkProjStatusID primary key, autonumber
-fkProjectID foreign key to tblProjects
-dteProjectStatus (date field to tell when the project status becomes effective)
-fkStatusID foreign key to tblStatus

Now a table to track the tasks related to a project. Now if you use the same tasks over and over again, then I would recommend having a table that holds all of the possible tasks and then relate them to the project. If the actual tasks are vary substantially then I would not put them in a table unto themselves. You'll have to make the call on that.

tblProjectTasks
-pkProjTaskID primary key, autonumber
-fkProjectID foreign key to tblProjects
-fkTaskID (foreign key to tblTask if you go that route) or txtTask (just a field to hold the description of the task)

Now assuming that you want to track the progress of a task as to when it is initiated through completed, you would have a related table to do that

tblProjectTaskTracking
-pkProjTaskTrackID primary key, autonumber
-fkProjTaskID foreign key to tblProjectTasks
-dteProjTask
-txtProjTaskAction or a foreign key to a table that holds predefined actions.


I was not sure about the market sectors. How do you define a market sector? In your original database, it looked like a country. Can a project relate to more than 1 market sector or only 1? Or does a customer relate to a market sector or many market sectors?

[emilyrogers]

I have attached the database that I was working on which could hopefully explain the uses of the different fields better than I can.

thank you again!

[jzwp11]

Within your market sector table you had a main category (compressors) with subcategories (Carbon Capture & Storage,Pipeline,Seal-less (canned)). To structure this properly in Access, you would have a table that holds all categories (main and sub) and then a table that relates them. I have included those in the attached database as tblMarketCategory and tblMarketSectors respectively. To view the main and subcategories, you would need a query, so I created a query called qryMarketSectors in the attached database. I have populated some of the other info from the database you posted into the attached database. I have also established the relationships so that you can see how all the tables join together.

[emilyrogers]

Thank you so much! You are a lifesaver!
So now I need to create forms and things??
Any suggestion as to how I would get the multiple filter(that I was trying to use before) to work??

Thank You Again!!

[jzwp11]

We're not done with the tables yet, so it is not time for forms. You'll have to fill in additional fields, but we have to make sure that those additional fields follow normalization rules. For example, in your old employees table you had several fields for phone numbers. If a person has many phone numbers or in general many contact methods, that describes a one-to-many relationship. As such you would need a table to hold the various phone numbers/contact methods as records

tblContactType
-pkContactTypeID primary key, autonumber
-txtContactType

The above table will hold just a list of all possible contact types: home phone, fax, mobile phone, e-mail address

Now we need to relate each applicable contact type with the person

tblPeopleContactMethods
-pkPeopleContactMethodID primary key, autonumber
-fkPeopleID foreign key to tblPeople
-fkContactTypeID foreign key to tblContactType
-txtContactMethod (the actual phone #, fax #, e-mail address etc.)


I saw the filters table. Can you explain how you intended to use it?

[emilyrogers]

The filters table was in the database that I recycled the code from because I thought I would need it.
Really the only fields I need are -
Customer-dropdown
Country-drop down
City-text
WMB Enquiry ID-autonumber(hopefully)
Sales Responsibility-drop down
Customer Enquiry Reference-text
Enquiry Date-date
Bid Required By-date
Customer has order to place-Y/N
Project-memo
End User-text
Status-Drop down
Market Sector- Drop down
Complete AMB sets- Y/N
Cabinet only- Y/N
Comm.g-Y/N
Eng.g-Y/N
Maintenance Contract- Y/N
Remote tuning and monitoring- Y/N
Spares - Y/N
Number of sets that would be ordered- number
Quoted value total- currency
bid date- date
validity expires- date
order won date- date
order won value- currency
sales comments- memo
followup date - date
followup action- text
followup person- dropdown


all of these fields will complete one record and each record will be unique.
I dont need any information for the sales responsibility or company other than their name.
thank you

[jzwp11]

As I mentioned earlier, having combo/list boxes i.e. dropdowns at the table level is not recommended. The foreign key will generally handle the relationship of one item to another (i.e. a customer to a project). You would do the dropdowns on forms.



Customer-dropdown handled via fkCustLocID in tblProjects
Country-drop down handled via fkCountryID in tblCustomerLocations
City-text in tblCustomerLocations
WMB Enquiry ID-autonumber(hopefully) You'll have to explain what this is and what it relates to.
Sales Responsibility-drop down handled via fkPeopleID in tblProjects (your employee responsible for the project I assume)
Customer Enquiry Reference-text You'll have to explain what this is
Enquiry Date-date handled along with status item Enquiry in tblProjectStatus
Bid Required By-date Placing a bid is a task; therefore it goes in tblProjectTask as a record
Customer has order to place-Y/N This can only occur for an awarded project, I am thinking that it belongs as a task. ie. customer order needs to be taken.
Project-memo Need more detail on this field; if it is just a description of the project, add a field in tblProjects
End User-text Need more detail on this field
Status-Drop down Handled via fkStatusID in tblProjectStatus
Market Sector- Drop down handled via fkMarketSectorID in tblProjects
bid date- date this is a status item and thus is handled via tblProjectStatus
validity expires- date need more detail on this item
order won date- date this is a status item and thus is handled via tblProjectStatus

order won value- currency you'll have to explain further
Quoted value total- currency you'll have to explain further
Number of sets that would be ordered- number you'll have to explain further


sales comments- memo you'll have to explain further
followup date - date This relates to a task and is handled as a task (a record) in tblProjectTasks
followup action- text This is a task and is handled as a task (a record) in tblProjectTasks

followup person- dropdown if the follow up task is handled by someone other than the original employee responsible for the project, then we would need a fkEmployeeID in tblProjectTasks


Complete AMB sets- Y/N
Cabinet only- Y/N
Comm.g-Y/N
Eng.g-Y/N
Maintenance Contract- Y/N
Remote tuning and monitoring- Y/N
Spares - Y/N

The above series of y/n fields should probably be records in a table and then related to the project. What attribute of the project do these items describe?

[emilyrogers]

Complete AMB sets- Y/N
Cabinet only- Y/N
Comm.g-Y/N
Eng.g-Y/N
Maintenance Contract- Y/N
Remote tuning and monitoring- Y/N
Spares - Y/N

The above series of y/n fields should probably be records in a table and then related to the project. What attribute of the project do these items describe?

OK!
Right.........these refer to things that are going to be included in a project eg. wether or not the customer is paying for cabinet only or amb sets etc.

I am sorry but I am so confused how all of these table s are going to pull together in the end. but i really appreciatre your help

[jzwp11]

I would recommend that you put these items into a table.

tblItems
-pkItemID primary key, autonumber
-txtItemName

...and then relate the ones the customer is willing to pay for to the project

tblProjectItems
-pkProjectItemID primary key, autonumber
-fkProjectID foreign key to tblProjects
-fkItemID foreign key to tblItems


As to how you bring this all together, perhaps it might help to see some tutorials that go through the whole process of designing tables, forms and reports. This site is very good.

[emilyrogers]

Thank You So Much for your help! I dont know what I would have done without you. I sorted my filtering problem. I have another database to do now- for which i will follow the rules or normalization and read through the sites you reccomended!!

[jzwp11]

You're welcome.