Please Advise?

[Samantha]

I just don't know where to really start to accomplish what I would like. I am using 2010 and am a beginner. I have a database that holds all client information and jobs (for service industry). Currently we use Excel to track "work on hand" which is work that has been approved. I would like to incorporate this into Access before I spilt the database.

So, my thoughts are I would like a button (add to Work on Hand) on my "frmProposalDetails" which would take a few fields from the source table/current record "tblProposals" and insert them into "tblWOH". I attempted to set this up as a qry but it didn't work as I was expecting.

Info about my Tables:
tblProposals - JobNumber (PK), Company, ServiceAddress, ProjectDescription, ContractAmount, JobStatus (this is a combo box that gives two choices "Approved" or "Completed") Anything with an Approved status should be included on WOH and once it is changed to completed then it would come off.

In addition to the above fields I would like to add some additional fields, DateApproved, Scheduled? (Y/N), RemainingScheduledValue ($).

If someone can point me in the direction of accomplishing this I would be very greatful!

 

[jzwp22]

...table/current record "tblProposals" and insert them into "tblWOH"

You do not need to move records between the tables. In fact, I do not think that you need tblWOH at all. It comes down to how you structure your tables, so I will focus on that. Once we structure the tables properly, you will, most likely have to recreate your forms.

In order to structure your tables properly, here are a few questions

Can a company have multiple service addresses at which you conduct your work?

Can you have many jobs (either proposed or approved) at a service address?

What is the significance of the RemainingScheduledValue ($) field and how is the value determined?

 

[Samantha]

Thanks for you reply, my main reason for utilizing another table is for the utilization of the date stamp "now()". I have to know when it is approved because I may not be the one whom does it. Jobs should also be scheduled in order of approval, so sorting would be done by this field.

To answer your questions Yes any Company used has multiple addresses that are serviced, and yes any one project can be multiple addresses.The scheduled vaule is determined down the road - the original contract value is what the project starts at, and it will not be a calculated value itself. It is based on what is left to do in the field which the Project Manager will fill in.

Now creating additional fields of blank data, wouldn't that hurt in the long run in regards to space. We are talking 5000 records, and about 50% are approved.

If not then maybe I just create a (Y/N) Reviewed field, and manually insert the date.

 

[jzwp22]

Based on what you have provided, there is a one-to-many relationship between the company and its addresses, so we need a couple of related tables to handle that

tblCompany
-pkCompanyID primary key, autonumber
-txtCompanyName
other fields


tblAddresses
-pkAddressID primary key, autonumber
-fkCompanyID foreign key to tblCompany
-txtAddr
-txtCity
-txtState
etc.

Now your job/project table

tblProjects
-pkProjectID primary key, autonumber
-txtProjectDescription
-currContractAmount

Since a project may involve many addresses, that is another one-to-many relationship

tblProjectAddresses
-pkProjAddrID primary key, autonumber
-fkProjectID foreign key to tblProjects
-fkAddressID foreign key to tblAddresses

Assuming that there are many events that can be related to a project including proposed, approved, scheduled and completed, we have another one-to-many relationship. Further, since an event can apply to many projects, we have another one-to-many relationship. When we have 2 one-to-many relationships between two tables (projects and events in this case) we have a many-to-many relationship which requires a junction table.

First, we need a table to hold all possible events (each event would be a record in this table.

tblEvents
-pkEventID primary key, autonumber
-txtEvent

Now for the junction table to join projects and events

tblProjectEvents
-pkProjectEventID primary key, autonumber
-fkProjectID foreign key to tblProjects
-fkEventID foreign key to tblEvents
-dteEvent (date of the event i.e. when it was proposed or approved or completed etc.)

In order to find when a project was approved and thus classified as work on hand, you would only need to run a query that looks at the most recent event for each project and select those whose most recent event were not proposed or completed. (Of course, if you add other events you will have to filter those out as applicable). You would use the same approach to find those projects that are scheduled but not yet complete or any variation you may need.

 

[Samantha]

Now for the junction table to join projects and events

tblProjectEvents
-pkProjectEventID primary key, autonumber
-fkProjectID foreign key to tblProjects
-fkEventID foreign key to tblEvents
-dteEvent (date of the event i.e. when it was proposed or approved or completed etc.)

In order to find when a project was approved and thus classified as work on hand, you would only need to run a query that looks at the most recent event for each project and select those whose most recent event were not proposed or completed. (Of course, if you add other events you will have to filter those out as applicable). You would use the same approach to find those projects that are scheduled but not yet complete or any variation you may need.

So I'm with you thus far (I think)- so I should remove JobStatus or (fkEventID) from the tblProjects all together into this additional juction table. Then the other field ScheduledValue I was wanting I should insert into tblProjects and tie it back together with a query.

 

[jzwp22]

So I'm with you thus far (I think)- so I should remove JobStatus or (fkEventID) from the tblProjects all together into this additional juction table. Then the other field ScheduledValue I was wanting I should insert into tblProjects and tie it back together with a query.

You are correct on the first part relating to the jobstatus. As to the remainingscheduledvalue field, if it is just a value that is calculated outside of Access by your project managers then, yes it would just be a field in the project table.

 

[Samantha]

Another question, so I have the tables all set up like above. On my one form frmProposalDetails I have a button cmdAddtoWOH that opens a model dialog box - How do I pass what is the primary key JobNumber to the second form.

Everything works as I would like it to I just want to prevent the possibility of typo errors.

Thank you so much for your time!

 

[jzwp22]

I typically use something like the following. It would go after the command that opens the second form.

forms!secondformname!nameofcontrolonthesecondform=me.controlnameholdingvalueoncurrentform

 

[Samantha]

One side effect of the changes that were made - and I have ran into this before, but since you have been so helpful maybe you would know.

On the form I have changed the control source to tblProjectEvents.JobStatus however now the form will only cycle through records that are present in that table. Is there a way to keep that field on my form and cycle through all the records. I don't want to be able to edit it from that form just be able to see the status. The source of the form is a qry that is based on tblProposals and other related tables although the other related tables there is ajoining information for each record.

 

[jzwp22]

It sounds as though you need to change the join type in the query on which the form is based (a left join). Since the events are on the many side of the relationship, you can have many event records that could be displayed which you cannot do in a single control. The records of the many side of the relationship are generally shown using a subform on the main form. However, if you want to display only the most recent event related to the project, that can be done in 1 control on the main form, but to get there your need to build a series of queries and join the last query of that series to your proposal table via the left join.

I have attached a sample database that illustrates the queries I discuss below.

First, create a query that finds the most recent event for each project

query name: qryMostRecentProjectEvent
SELECT tblProjectEvents.fkProjectID, Max(tblProjectEvents.dteEvent) AS MostRecentEventDate
FROM tblProjectEvents
GROUP BY tblProjectEvents.fkProjectID;


Next, create another query that includes the above query and tblProjectEvents. Join the two by both the fkProjectID and the date fields (MostRecentEventDate and dteEvent).

query name:qryMostRecentProjectEventDetail
SELECT qryMostRecentProjectEvent.fkProjectID, qryMostRecentProjectEvent.MostRecentEventDate, tblEvents.txtEvent
FROM tblEvents INNER JOIN (qryMostRecentProjectEvent INNER JOIN tblProjectEvents ON (qryMostRecentProjectEvent.MostRecentEventDate = tblProjectEvents.dteEvent) AND (qryMostRecentProjectEvent.fkProjectID = tblProjectEvents.fkProjectID)) ON tblEvents.pkEventID = tblProjectEvents.fkEventID;


Now, create a third query that includes your project (proposal) table and qryMostRecentProjectEventDetail above. Note the LEFT JOIN in the query below

query name: qryProjectsWithEventDetail
SELECT tblProjects.pkProjectID, tblProjects.txtProjectDescription, tblProjects.currContractAmount, qryMostRecentProjectEventDetail.txtEvent, qryMostRecentProjectEventDetail.MostRecentEventDate
FROM tblProjects LEFT JOIN qryMostRecentProjectEventDetail ON tblProjects.pkProjectID = qryMostRecentProjectEventDetail.fkProjectID;


There is some sample data in the attached database so that you can see how the results look through the series of queries. You would bind your form to the last query discussed.

 

[Samantha]

ahh.. You're Wonderful! Thank you so much!

 

[jzwp22]

You're welcome. Glad to help.