Question on Yes/No Fields

[jj95]

All,

Sorry for the dumb question, but I have exhausted all my ideas. BTW, I am not an Access-guru, so pardon my ignorance. Not sure if my question is really table or query related, so I posted here.

Here is my situation, in short. I have a database of equipment, with all the specifications for that equipment. When I get a new job in, I want to enter the specifics of that job (dates, time, location, etc), and then assign equipment to it (by the equipment ID that we all refer to them as). The job could have 1 or more assigned to it. Then, I'd like to be able to run my queries to scrape from the various tables and populate various forms.

My idea was, on this "new job" form, my thought was to have Yes/No check boxes for each of the equipment IDs. Lets say one of those equipment IDs is KZ45. In my inventory tables, that is my ID for each record. But I dont know if or how I can compare the Yes/No in the Jobs table to the Equipment ID (text value KZ45) in the Inventory table(s).

Any thoughts or ideas would be appreciated.

Thanks!

 

[jzwp22]

Welcome to the forum!

Can a piece of equipment be used on multiple jobs?

 

[jj95]

Good point. Yes (and no)! In reality, it is only (actively) assigned to one job. But we do have long-standing jobs where KZ-45 is assigned for the next 12 months to support a certain task, but it may get seperately tasked for a week or two for other purposes. Ideally, I would like to not break that relationship with the long-standing job.

Thanks!

 

[jzwp22]

So basically you are saying that a piece of equipment can be used on many jobs over time. Since a job can have many pieces of equipment and a piece of equipment can be used on multiple jobs, then you have two one-to-many relationships between the same two entities which is represented with a junction table:

tblJobs
-pkJobID primary key, autonumber
-JobNo
(other job related fields)

tblEquipment
-pkEquipID primary key, autonumber
-EquipNumber
(other equipment related fields)


tblJobEquipment
-pkJobEquipID primary key, autonumber
-fkJobID foreign key to tblJobs
-fkEquipID foreign key to tblEquipment

As to equipment that is dedicated to long-term jobs (for the most part), you may add a field to tblEquipment to identify those pieces that are dedicated but can be "loaned" out on short-term jobs.

 

[jj95]

Thanks for the response. I think I am pretty comfortable about how I am going to query to get the various things I need.

My point of confusion is if I can utilize the Yes/No function and query against the other tables. So:
Table Field Type Example
Inventory EquipID Text KZ-45
Jobs KZ-45 Yes/No Yes

As I understand, the Yes/No is really a 1 or 0 (or -1, cant recall)? But not sure if there is a way I can join on these two fields. I suppose I could have fields where we type in "KZ-45" as the ID we want to assign, but I thought it would be nice to use check-boxes instead (I only have 30 or so of these equipments to track).

Thanks!

 

[jj95]

The spacing I put in there didnt work out, so this time commas:

Table, Field, Type, Example
Inventory, EquipID, Text, KZ-45
Jobs, KZ-45, Yes/No, Yes

 

[jzwp22]

Your relationships demand the use of 3 tables not 2

Any piece of equipment that is assigned to an active job via a record in the tblJobEquipment would not be in "inventory". You would not need a formal inventory table; you would determine which pieces of equipment are available by finding out which ones are sitting idle on inactive jobs--you would determine this with a query. You will need a field (I suggest a date field such as jobcompleted) that tells you whether a job is active or not.

 

[jj95]

It's starting to make more sense, thanks. I will tinker around with it and see what I get.

Thanks for your help!

 

[jzwp22]

You're welcome; please post back if you have additional questions.

 

[jj95]

Instead of listing all my equipment IDs with Yes/No fields, I ended up creating a List Box. The source of the List Box is a query that shows all the equipment IDs (KZ-45, KZ-46, KZ-92, etc). Now I can run down that box and select all the IDs that I want assigned to a job.

It appears to work well. When I look at the value of that field on my job record, it shows the ones that I have selected, separated by commas. I have also been able to run some rudimentary queries against it (joining with other tables) and got the expected results.

What I would now like to figure out is how I can create records (in another table) based on this. So if I selected KZ-45 and KZ-46, I'd like to have it create 2 records in another table. Some of the data (equipment ID, job ID, etc) will be copied from this initial record. Other data will be pre-populated based on default values. Other data might be required to be entered. This is more of a validation step - 9 times out of 10, I know the values that need to be in this record, but occassionally they change, hence I want the opportunity to have them reviewed.

Not sure if that makes much sense. If anyone has any ideas, I would appreciate it. Its been a LONG time since I played with Access, and things are starting to come back to me, but sometimes my memory fails!

Thanks!

 

[jzwp22]

It sounds like you are using a multi-select list box. You will need to loop through the list and identify those items that have been selected and then add the records. I pulled the following code snippet from one of my projects that might prove useful. The code adds an applicable record for each type of document a particular applicant selected from the list box. The code needs to capture the primary key value of the applicant. In your case you will need to provide the primary key of the job, so that the jobID foreign key and the equipment key are added to the table.

Dim cnn1 As ADODB.Connection
Set cnn1 = CurrentProject.Connection
Dim myRSAppDoc As New ADODB.Recordset
myRSAppDoc.ActiveConnection = cnn1

myRSAppDoc.Open "tblApplicationDocuments", , adOpenDynamic, adLockOptimistic
    If Me.lstAppDocs.ItemsSelected.Count <> 0 Then
        If Me.lstAppDocs.ItemsSelected.Count > 0 Then
            For lngloop = 0 To Me.lstAppDocs.ItemsSelected.Count - 1
                    With myRSAppDoc
                        .AddNew
                        !fkAppID = holdapp
                        !fkDocID = Me.lstAppDocs.ItemData(Me.lstAppDocs.ItemsSelected(lngloop))
                        .Update
                    End With
            Next lngloop
        End If
    End If

myRSAppDoc.Close
Set myRSAppDoc = Nothing

 

[jj95]

Thanks for yoru help, that makes sense. I have never messed with any VB inside Access, so not sure where this kind of logic goes. My thought is that once the intial record is completed, I then have a Macro/button that will save/close the current record, walk through this loop to create the additional records in the other table, and then open those record(s) in order to view/edit them. Sounds like an Access macro to me, but not sure if the VB piece fits into that mold?

Thanks again!

 

[jzwp22]

I do not use macros, so I don't know if a macro could handle what you want to do.

A simple way to do this would be to have an unbound form that has your list box, a combo box (for the job requiring equipment) and a command button. The code I provided would go in the On click event of the command button. The code would get the job's primary key value from the combo box and loop through the list box to add records to the table.

After adding the records, you would open another form that was of a form/subform design with the main form being bound to the job table and the subform bound to the job-equipment table. You can use the primary key of the job to open the form to the job record that was just used in the code.