Using a subform for multiple entries in main record

[jjarman]

I think I'm on the right track with using a subform, but could use some guidance.
I have a form that tracks job assignments, and I need to have the ability to have multiple services assigned to each job.
I'm assuming that a subform is the way to go as some jobs may only have one service on them, and others could be 5 or 6.
I have a seperate table called services that I want to be able to look up and select for each job, BUT..
Not even really sure where to start or if I'm on the right track.

 

[Beetle]

Yes, you are on the right track with a form/sub form, however, you need to make sure your table structure is correct first. You mentioned you have a table for Services, but didn't really give any specifics. Is the Services table related to whatever table is the "parent"?
If you want to give us an idea about your table structure we can offer more specific advice. Something like;

tblJobs
JobID (Primary Key)
JobDescription
*other attributes of a Job

tblServices
ServiceID (PK)
JobID (Foreign Key to tblJobs)
ServiceDate
*other attributes of a Service

 

[jjarman]

There is a linked table called services which has service and rate in it, service being the PK.

The table called assign has the following in it;
autonumber - PK
project code, customer, project description, manager, createdate

AND

service and rate, which I assumed I needed to have in it.
I haven't set anything up in relationships.

Haven't used Access in a long time, and isn't like riding a bike
Any help is appreciated.

 

[Beetle]

There is a linked table called services which has service and rate in it, service being the PK.

You mention that this table is linked but you didn't specify that about your other table. Is the Service table the only one that is linked? Is this essentially a predefined list of the Services that can be performed and the rate that is billed for each?

The table called assign has the following in it;
autonumber - PK
project code, customer, project description, manager, createdate

AND

service and rate, which I assumed I needed to have in it.
I haven't set anything up in relationships.

The service and rate fields should not exist in this table based on what you've said in previous posts. You need to establish a relationship with the Services table to return this data. However, the method by which you establish that relationship is yet to be determined. It sounds like you may have a many-to-many relationship here, which would require a junction table, but I don't know that for certain without knowing the answer to my question at the start of this post. As far as the Customer and Manager fields from Assign table, those may be appropriate but they really should just be Foreign Key fields to Customer and Manager (or Employee) tables respectively.

 

[jjarman]

Yes the linked table "services" is a predefined table that has the service and rate associated with it.
There are 2 other linked tables, also with predefined information;
employee, which has employee names AND
ITEM CODE - which has the details about the project itself.

Essentially what I want to do is lookup any existing job from ITEM CODE, and then;

pick a service/rate from services AND
pick employees to assign to that service.

There are usually mutliple services associated with projects, and different employees for each service.


So service and rate should NOT exist in the assign (main) table?

 

[Beetle]

Well, now it's starting to sound as though the Assign table actually is the junction table for this scenario. I don't know for certain because I still don't have complete picture of your table structure. However, if that is the case, then it would be appropriate to have the Service field there as long as it is a Foreign Key field that is storing the PK value from the Services table. Ditto for the Employee (manager) field. You haven't said whether or not you have a table for Customers, but if you don't, you should.

As far as the Rate field in the Assign table, that may or mat not be necessary. It depends on the nature of the Rate. If the rate can change over time (which would seem likely) then you do want a Rate field in the Assign table so that you can store the value of the Rate at the time of the service. In that case the redundancy is acceptable because it is historical data that needs to remain static in that table. If the Rate never changes, then there is no reason to store it in the Assign table. You can just retrieve it from the Services table when needed.

 

[jjarman]

Not sure what a Foreign Key is.

In the assign table;
- PK is an autonumber field.
-projectcode, customer, projectdescription and manager will all be looked up in the linked table called ITEM CODE in which the field itemcode is the PK.
- employee comes from a linked table, only one field employee and is PK
- service and rate come from linked services table, service is the PK

Does this help any?

 

[jjarman]

I guess the other question I had, do I need to have another table or form to record services associated with a record in the assign table?

 

[jjarman]

Thanks for the help. Managed to get this working... once I broke it down and started to understand what it was doing!

But I could use some advice on setting up a report to show the info the same way I see it on the form. For some reason I'm only seeing the first record from the subform in the report and it's ignoring everything else. I'm sure it's something simple, but can't see it.

 

[Beetle]

Difficult to answer your report question without knowing more. What is the record source of the report? How is the report being opened? If it is opened from a command button on a form (for example), then is there code in the command button that is applying a filter?

Need more details, or you can post a copy of your app here if you want and I can take a look at it.