General Help indeed!

[brookierook]

I'm trying to develop a user-friendly form for my team members to fill out. With the 'yes/no' boxes it shows up as a table when I make it a subform to a form. I know I can change it to make it look like a form as well... but when I do it doesn't work properly. Should I leave it as a table then?

[jzwp11]

Got it.

In looking at your data, you have 12 projects but your supplier project query only returns 1 project with the 3 suppliers for that project. This tells me that you have not associated at least 1 supplier with each of the other 11 projects. In order, to show ALL 12 project whether or not they have an associated supplier, you need to adjust the join type in the query. Of course, if you try to do this with the query as you have it now, you get an error about an ambiguous outer join. To handle that, I created a query that joins the project supplier table with the supplier table (qryProjectSupplierDetail). I then created a second query that joins the project table with a left join to the qryProjectSupplierDetail. I called this query qrySupplierProjects in the attached database (projectinfo1v2).

Now, I see some other general issues with your database. First, it is best not to have spaces or special characters in your table or field names. I also noticed that you used a composite primary key in a couple tables. Although this is definitely possible and many people use this approach, it forces you to carry both keys to any related tables which could get messy for large databases. For this reason, I added a primary key field but also created an index on the other two fields such that the combination was unique. The other thing I noticed is that you used lookup fields at the table level; although Access has this capability it is generally not recommended since it can cause many issues. This site details those issues. In the attached database (projectinfo1v3) I have made all of the changes I have indicated.

[brookierook]

Wow!!!! thank you!!!!! That was very kind of you. I can't tell you how much I appreicate that!!!!!!!!

[jzwp11]

You're welcome. But, there are still a few more issues with your tables that need to be addressed before you can move on to forms. In the project table you have fields for MRManager and PONumber but you have related tables for these as well. I am thinking that you do not need those two fields in the project table. Futher you have the costcenterID in both the projects & the Purchase orders tables. I think it should be in only 1 location; I'll leave it to you based on your business process to determine which table is most appropriate.

Also in the project table you have some fields such as RFPSent, KickOffMeeting, GroupDebriefDate. These all sound like events associated with a project. I would think that these project events (as well as any other ones) should be recorded as records in a related table.

First a table to hold the various event names

tblEvents (kick off meeting, RFP sent etc.)
-pkEventID primary key, autonumber
-txtEventName

Then a table to relate the events to the project

tblProjectEvents
-pkProjEventID primary key, autonumber
-fkProjectID foreign key to Projects table
-fkEventID foreign key to tblEvents
-dteEvent (date of the event)
other fields to capture details of the particular event

Also, I would think that keyfindings may actually encompass more than one particular item which would suggest a one-to-many relationship.


Back to your question about querying companies pitched/selected (or countries requested/confirmed). If you use just one field (for selected), I think you would be better off. You can filter on that field if necessary (companiesselected=-1 would correspond to those records where the field is checked)

[brookierook]

Ok, I have made ALL of the changes you suggested and I think you may be right about the pitched/selected. I think I will just keep it simple there and it will make it easier to just query the countries we acutally have projects in.

Am I ready for queries and forms? lol I'm desperate to get this done, it's been the bain of my life for too long now!

[jzwp11]

Two more questions... Regarding the methodology field in the project table, is there only 1 methodology associated with a project or can multiple methodologies apply? If only one, then you can leave the design as is. If more than one, then you will need a related table (projectmethodologies)

Regarding the projectstatus field. Are you only recording the current status in this field or do you plan on capturing the history of the status over time (many statuses for a project)?

[brookierook]

Regarding methodology, there can be more than one so I will make the related table.

For Project Status, we're not keeping record, we just want to know what stage each project is in. I will have to create a weekly report on active projects (projects that are not 'completed') in the future.

[jzwp11]

OK, it sounds like you have the tables straightened out. The forms would be next.

[brookierook]

Hello,

I'm sorry but may I bother you again? I just have some more questions. First, thank you for your help so far but I really hope you can tell me more. You gave me two DB's and I'm confused as to if/how they are related/connected to eachother... do I need the one for the other to function properly? Am I making any sense? It's completely unfamiliar to me (as I'm a novice).

Also, may I have some help with the queries and forms?

I would like the project managers on my team to be able to open the 'project manager' query, enter their name, and be able to see all of the projects that they are working on. Same for countries, clients, and suppliers. I would just like to make sure that I am setting them up properly.

I would really appreciate it.

Thanks again,

Brooke

[jzwp11]

I'm sorry but may I bother you again? I just have some more questions. First, thank you for your help so far but I really hope you can tell me more. You gave me two DB's and I'm confused as to if/how they are related/connected to eachother... do I need the one for the other to function properly? Am I making any sense? It's completely unfamiliar to me (as I'm a novice).

The two database versions I presented (v2 & v3) are different and thus are not intended to work together. The v2 was to illustrate the query I described in the post, but in looking at your database I saw some structural issues that I felt needed to be corrected. The v3 version corrects those issues, so that is the database I would use going forward. Now since I posted those databases, you have made other changes to the structure. Can you post that database? That way I can see exactly where you are with your structure.

[brookierook]

ProjectInfo1v3.zip

Hope that worked?

[jzwp11]

You still have some cleaning up to do with your projects table. Since you are now handling the project managers through the projectmanagers table you do not need the MRManager field in the project table. Similarly, you do not need the CostCenterID and fkMethodologyID fields in the project table either since they are handled with other tables now. Further, I think you can also eliminate the kickoffmeeting and groupdebriefdate fields since I think you will be tracking those as events via the ProjectEvents table, correct? Also you have a field in the projects table called RFPSent, is this also going to be handled via the events? If so, you can remove that field as well.

In the projects table you have a field for project status (projectstatusID). In having the field in the projects table, a project can have only one status at any particular time. Are you interested or do you have a requirement to track a project over time? If so, then tracking the status over time would be handled via a related table.

With that, only the following fields should remain in the projects table. The ones in shown in green I do not see any issue with, but what is the purpose of each of the other fields?

-projectID
-MRStudyTitleID
-ClientID
-RDEReference
-ShortDescription
-ProjectStatusID (see earlier discussion above)
-ApprovedBudget
-DiscountAchieved
-PONumber (is there only 1 PO for the entire project?)
-StudyReq
-Notes
-Theme
-KeyFindings
-LocalContacts
-MRType

[brookierook]

Ok, I deleted all of those bits from the project table.

For the other bits:

-MRStudyTitleID - It is the name of the Market Research Project being conducted. I need it for when I do my weekly reports on the status of our live projects. Also, when our project managers are updating their projects in this database (that they input via the form to be created). That way when they run the MR Manager query for their name they can see their projects and the names of the projects will be in the query.... yikes.

-RDEReference - This is the reference to the approved budget for each project i.e. this project was approved under RDE_67_419. 67 being the budget meeting number and 419 represents the project budget number. There will be only one reference per project so I figure we can just enter this manually as they change all of the time.

-ProjectStatusID (see earlier discussion above) - There are several stages in a project and our boss just likes to see what stage each project is in. There are several stages i.e. pre kick-off, kick-off, field work, analysis etc.. all of those status' are associated with live projects (which I report on weekly). The last status is 'Completed', which means it becomes an inactive project that I do not need to report anymore. I've been considering a query that separates the live from the inactive that automatically updates when a project has been completed but I think it's beyond my understanding.

-ApprovedBudget - This is just a visual for the project manager to see how much they have to spend. This will also be included in my weekly reports.

-DiscountAchieved - Sometimes suppliers will give us discounts so that we can stay within budget for the project. I'm
-PONumber (is there only 1 PO for the entire project?) - There can be more than one but it is rare (it means someone was not being careful with their budget OR we've decided to add an additional country to the project). Purchase Orders are raised to pay suppliers for the research they conduct etc...

For those last 3 - Approved Budget, Discount achieved and PO number... I have a few table set up (Purchase Orders, Cost Centre Budgets) that are connected to the Projects table. My market research team draws their funds from two cost centres, each with a budget and I've set it up so that projects that are related to a cost centre are deducted from the budget... at least I think I do... should I take them off the projects table then?

-StudyReq - We do research projects for different parts of our company i.e. Group Marketing, Devices, Digital Media... within those entities are VP's who request this project and give their input to our project managers.

-Theme

-KeyFindings - they will just input this manually. Its just the outcome of their projects and what they got out of it. Its a summary of what they report on to group marketing

-LocalContacts - A lot of times are research projects are in other countries that we have business in. For instance in Romania we have a marketing team and we might be running a research project in Romania. We shoot them the info and they tell us whether or not they are interested or would like to be involved. Its kind of courtesy.

Did that help or make it worse? lol

[brookierook]

LOL, and theme, there can only be one so I'm not too worried about that

[jzwp11]

-MRStudyTitleID - It is the name of the Market Research Project being conducted. I need it for when I do my weekly reports on the status of our live projects. Also, when our project managers are updating their projects in this database (that they input via the form to be created). That way when they run the MR Manager query for their name they can see their projects and the names of the projects will be in the query.... yikes.

OK, I was anticipating something different with the "ID" in the field name. This field should be OK.

-RDEReference - This is the reference to the approved budget for each project i.e. this project was approved under RDE_67_419. 67 being the budget meeting number and 419 represents the project budget number. There will be only one reference per project so I figure we can just enter this manually as they change all of the time.

This should be OK

-ProjectStatusID (see earlier discussion above) - There are several stages in a project and our boss just likes to see what stage each project is in. There are several stages i.e. pre kick-off, kick-off, field work, analysis etc.. all of those status' are associated with live projects (which I report on weekly). The last status is 'Completed', which means it becomes an inactive project that I do not need to report anymore. I've been considering a query that separates the live from the inactive that automatically updates when a project has been completed but I think it's beyond my understanding.

Leaving the projectstatusID as a field in the projects table is OK as long as you just need to track the current status of each project. If you are interested in how long it takes a project to move from one status to another, then it would require a different structure. This call is up to you and the way your company does business. So what approach is in line with your business process?

-DiscountAchieved - Sometimes suppliers will give us discounts so that we can stay within budget for the project. I'm

You did not finish your thought on this field. Do you want to track which suppliers gave discounts for a project and how much of a discount? If so, then your current structure is insufficient to track multiple suppliers each with discounts (one project to many supplier providing discounts).

-PONumber (is there only 1 PO for the entire project?) - There can be more than one but it is rare (it means someone was not being careful with their budget OR we've decided to add an additional country to the project). Purchase Orders are raised to pay suppliers for the research they conduct etc...

If the possibility exists and you need to track the PO #'s associated with the project, then you should take care of it in your structure now.

For those last 3 - Approved Budget, Discount achieved and PO number... I have a few table set up (Purchase Orders, Cost Centre Budgets) that are connected to the Projects table. My market research team draws their funds from two cost centres, each with a budget and I've set it up so that projects that are related to a cost centre are deducted from the budget... at least I think I do... should I take them off the projects table then?

If the budget amounts for a project are related to cost centers then the project table is not the appropriate place for the budget amount.

-StudyReq - We do research projects for different parts of our company i.e. Group Marketing, Devices, Digital Media... within those entities are VP's who request this project and give their input to our project managers.

Can multiple groups of your company be associated with 1 project? If so, then the current structure is insufficient because you would have a one-to-many relationship.

-KeyFindings - they will just input this manually. Its just the outcome of their projects and what they got out of it. Its a summary of what they report on to group marketing

Are there multiple key findings? If so, that describes a one-to-many relationship. Additionally, if it is a summary, a 255 character text field may not be sufficient.

-LocalContacts - A lot of times are research projects are in other countries that we have business in. For instance in Romania we have a marketing team and we might be running a research project in Romania. We shoot them the info and they tell us whether or not they are interested or would like to be involved. Its kind of courtesy.

Are these "Local contacts" people? If there are more than one, that describes a one-to-many relationship which would require a related table not just a field in the projects table.