Dynamic Form based on the value of two cells

[enigma1]

Hi all,

I'm pretty new to Access but have big plans. i'm learning as I'm going. I normally do Excel VBA.

I am trying to create a database that does the following:

Table 1 has columns: (UniqueID, Customer, Project Name, Projected Start Quarter, Est Proj Start Date, Project End, # of Hours)
Table 2 has columns: (UniqueID, Customer, Project Name, 7-Jan-12, 14-Jan-12, 21-Jan-12, 28-Jan-12, etc etc) The first 3 are linked to table 1, These are the dates of the end of each week for years 2012-2014 for now. The data in these columns will need to be filled with the number of hours we plan on spending each week on each project.

The problem is that I dont want to see 52, 104, 156 weeks in the table and have to scroll through all the columns just to find 3 weeks here or there that have the info I need.

SO....

I'm trying to figure out how I can create either a Form or a Query +Form that will let you select a Unique ID and it will bring back the "Customer, Project Name" and then based on the cells "Project Start Quarter, and Project End" it will only show the cells with the dates in that period.



Is this possible? if so, can I get a little direction as to how I would accomplish this?

Thanks so much in advance.

[jzwp11]

Welcome to the forum!

First and foremost, you do not store data the same way in Access as you do in Excel.

Can a customer have many projects? If so, then your first table is not structured properly.



A proper structure would look like this


A table to hold the basic customer information:
tblCustomers
-pkCustomerID primary key, autonumber
-txtCustomerName
-txtAddress
-txtCity
etc.


tblProjects
-pkProjectID primary key, autonumber
-fkCustomerID foreign key to tblCustomers (must be a long integer number datatype field)
-txtProjectName
-dteProjectStart (project start date)
-dteProjectEnd (project end date)

You would not store the # of hours for a project; you would calculate the value on the fly using a query. You do not need to store the starting quarter since that can be extracted from the start date.

To capture the hours for a project, you need a table like this

tblProjectHours
-pkProjectHoursID primary key, autonumber
-fkProjectID foreign key to tblProjects (must be a long integer number datatype field)
-dteWeekEnding (week ending date)
-spHours

Some general recommendations:
1. It is best not to have spaces or special characters in your table or field names.
2. Watch out for reserved words and symbols; they should not be used in/as table or field names. Here is a list.
3. Avoid using combo/list boxes in your tables. They are best left for forms. This site has more details as to why having table level lookups are not a good idea.

[enigma1]

Thanks for the tips and the great information. I now have to re-think how I want to do all of this.

As far as the hours worked per week, since this can vary depending on the week and the duration of the project, what would be the best way to input the hours per week, and then only view the entries based on the project start week through the project end week? instead of all 3 years

[John_G]

Hi -

Before you do anything in Access, you need to realize that MS Access is not Excel, and does not work the same way. Designing a table to emulate an Excel spreadsheet is almost certainly going to lead to problems.

First, I suggest you look up "database normalization" on the net. Database normalization is THE key concept you need to grasp in order to design an efficient database.

Then look at what the "entities" in your data are, and the "properties" (AKA attributes) that pertain to them. An "entity" is a real-world "object" (which does not have to be physical) - in your case two entities are Projects and Customers - and become Tables in the database. Properties become fields in those tables.

So, given the concepts of database normalization, your tables need to be re-worked.

- Table1 is actually not too bad; all the fields (NOT columns) in it relate to a specific project. "Customer" should be Customer_ID, and link to another table which has customer information.

- Table2 is where the problems will arise. The main problem is that it has a repeating group (the date), which is a normalization no-no. What I think you want in Table2 is the number of hours worked in each week on each project, so at its very simplest, it needs ony three fields: Project_ID, Week_Ending_Date and Planned_Hours. It doesn't need customer, because you already have that in Table1.

So, you now have three tables - Customers, Projects (Table1) and Hours_worked (Table2)

This should give you some ideas as to how to proceed - but do read up on Normalization. It will make life a lot easier.

John

[jzwp11]

As far as the hours worked per week, since this can vary depending on the week and the duration of the project, what would be the best way to input the hours per week,

The best way to enter data is through forms.

...and then only view the entries based on the project start week through the project end week? instead of all 3 years

I'm not quite sure what you mean by this, but you can use queries to pull the hours for specific time periods based on the end date. Say you want all project hours in the first half of 2012. That can be done easily with a query, you would just set the criteria as follows: WHERE dteWeekEnding BETWEEN #01/01/2012# AND #06/30/2012#

But more on queries after you have read up on normalization per John_G's reply and after your tables are set up properly.

[enigma1]

Thanks again guys. lots of stuff to read up on but its all very helpful.

[jzwp11]

You're welcome. Please post back with any questions.