Possible to use DMAX with SQL Insert??

[harleyskater]

possible to use DMAX here?

If 1 > 0 Then
                strSql2 = "INSERT INTO [dbo_operations] ( part_item_id , [COLOR=red]job_process_count[/COLOR], operation_num, op_type, op_description, emp_id, est_mins ) " & _
                    "SELECT   " & lngPartItemID & ", [COLOR=#000000]DMax("job_process_count", "dbo_operations", "job_num = 10248"), operation_num , op_type, op_description, emp_id, est_mins " & _
                    "FROM [dbo_operations_template] WHERE [dbo_operations_template].template_part_item_id = " & lngPartID & ";"
                DBEngine(0)(0).Execute strSql2, dbFailOnError
            Else
                MsgBox "Part record duplicated, but there were no operation records."[/COLOR]
End If

 

[ecawilkinson]

Why not just try it on in a development/test area?

 

[harleyskater]

I am in the process of doing that right now. I am setting up a test DB on my SQL backend and I am going to give it a try. Reason I posted is because I don't understand how VBA uses the insert statement. I was hoping to get a response about maybe it wouldn't work because of the way the code cycles.

For instance.. If the insert is only stepped through once. It would probably only generate 1 dmax number. Which would of course cause a problem for me because I am hoping to increment that number for each JOB. I am hoping to build a autonumber type field to run adjacent to my real autonumber field for the table. So I can have not only a unique number for each record in the table but a unique number that is specific to the Job Number.

Why not just try it on in a development/test area?

 

[ecawilkinson]

If the SELECT section of the INSERT statemnet generates more than 1 record, then you have a problem because, as you feared the DMAX statement is only executed once. Unfortunately, I'm not exactly sure what you are trying to do.
Is job_process_count for dbo_operations_template an autonumber?
Also, what relation does job_process_count in dbo_operations have to bear to job_process_count in dbo_operations_template? i.e. does it have to be the same, or 1 more than the corresponding record dbo_operations_template?

Chris

 

[harleyskater]

I have a large database- almost 30 tables.
One part of this database holds what we call templates. Its basicly a generic version of a set of records. We hold that generic instance in the templates table so we can pull one of those templates into our real table and then make changes as we see fit. We have about 1000 templates and each template has about 20 instances in our real set of tables. Those 20 instances or so for each template all have small changes and relations and references after in child tables.
so i have 2 sets of tables in my database that are almost identical.

-dbo_part_items and dbo_operations
-dbo_part_items_template and dbo_operations_template

Like I said we have a large database, so the Operation_ID which is an autonumber PK for the operations table is going to get very large. This large number is going to have zero meaning to the people that have to use it.
There is a waterfall of tables that look like this

jobs_and_quotes->po_items->part_items->operations->timeclock

Those tables have many tables related to them.

So in a chance to give the operations_id more meaning to the people that have to use it. We are going to try to create a new field in the operations table called - job_process_count. This is going to use DMAX to count the number or records for each Job_Num. So instead of people having to use 134152431 with the timeclock they can use [Job_num] & "-" & [job_process_count]

The job number is a very well known here.

If it was my choice of course, I would use the autonumber, it means less work for me and more simplicity to the access project and the database. But management is worried about the workers using a number that means nothing to them as a way of identifying the process they are working on.

If there was a way to make the insert cycle for each record that would be the answer to all my problems.

-btw my management thinks anything is possible and puts the responsiblity squarely on my shoulders. I am the only IT guy here and they have me working for 2 companies. That's why I come here and ask questions. I love the opinions and help I get here. <3

If the SELECT section of the INSERT statemnet generates more than 1 record, then you have a problem because, as you feared the DMAX statement is only executed once. Unfortunately, I'm not exactly sure what you are trying to do.
Is job_process_count for dbo_operations_template an autonumber?
Also, what relation does job_process_count in dbo_operations have to bear to job_process_count in dbo_operations_template? i.e. does it have to be the same, or 1 more than the corresponding record dbo_operations_template?

Chris

 

[ecawilkinson]

Looking back at your insert statement, does the SELECT part of the statement return more than 1 record? If it returns multiple records, could you give me a small amount of data jsut to help me grasp what is going on? I'm nearly ther but not quite.
I'm on these lines - correct me if i'm wrong:
the select bit grabs a template with an ID of lngPartId, and wants to insert it into dbo_operations. If there are already say 15 template instances, this one needs to be number 16, i.e. job_process_count = 16.

If you could post the srtucture of the dbo_operations_template and dbo_operations, making clear which fields make each record unique, that would help.

Chris

 

[harleyskater]

Not quite, and sorry for not answering your question the first time.

It works like this. There is a form which I have for looking at all the information all the way down the waterfall I spoke of to the operations table.

jobs_and_quotes->po_items->part_items->operations

There is a combo box on that form that is filled with the templates. Someone selects the template. Then there is a command button that when clicked takes that template from the combo and inserts
- first the part_items_template record- 1 record into the part of the subform with the current focus
- second it takes the template_part_item_id from the template and queries for all the template_operations and then inserts them into the real operations table replacing the template_part_item_id with the new part_item_id from the new instance of the record in the part_items - that was inserted into the subform

each table in the waterfall is 1-many - the job of the job_process_count is to increment the number of operations for that particular job from the jobs_and_quotes table.

ie.
2 jobs - job numbers: 12142 and 12060

2 po_items for job 12142 and 3 for 12060
- 12142 item # 1
- 12142 item # 2
- 12060 item # 1
- 12060 item # 2
- 12060 item # 3

each one of those po_items can have a different part_item -

- 12142 item # 1 - part_item # 1A
- 12142 item # 1 - part_item # YZ2
- 12142 item # 2 - part_item # T3

- 12060 item # 1 - part_item # 3
- 12060 item # 1 - part_item # A1
- 12060 item # 3 - part_item # GYZ-B


now each one of those part_items has many operations usually 10 -20

- 12142 item # 1 - part_item # 1A
- op_id 1 -job_process_count # 1
- op_id 2 -job_process_count # 2
- op_id 3 -job_process_count # 3
- op_id 4 -job_process_count # 4
- 12142 item # 1 - part_item # YZ2
- op_id 5 -job_process_count # 5
- op_id 6 -job_process_count # 6
- op_id 7 -job_process_count # 7
- 12142 item # 2 - part_item # T3
- op_id 8 -job_process_count # 8
- op_id 9 -job_process_count # 9

- 12060 item # 1 - part_item # 3
- op_id 10 -job_process_count # 1
- op_id 11 -job_process_count # 2
- op_id 12 -job_process_count # 3
- 12060 item # 1 - part_item # A1
- op_id 13 -job_process_count # 4
- op_id 14 -job_process_count # 5
- op_id 15 -job_process_count # 6
- 12060 item # 3 - part_item # GYZ-B
- op_id 16 -job_process_count # 7



Sorry if that is hard to read -

Looking back at your insert statement, does the SELECT part of the statement return more than 1 record? If it returns multiple records, could you give me a small amount of data jsut to help me grasp what is going on? I'm nearly ther but not quite.
I'm on these lines - correct me if i'm wrong:
the select bit grabs a template with an ID of lngPartId, and wants to insert it into dbo_operations. If there are already say 15 template instances, this one needs to be number 16, i.e. job_process_count = 16.

If you could post the srtucture of the dbo_operations_template and dbo_operations, making clear which fields make each record unique, that would help.

Chris

 

[ecawilkinson]

I'm sure there must be a single SQL statement - all be it complicated - that can do this. However, you need a solution and I think I have one which should not be too slow:

set rs = db.openrecordset("SELECT operation_num, op_type, op_description, emp_id, est_mins " & _
    "FROM [dbo_operations_template] WHERE [dbo_operations_template].template_part_item_id = " & lngPartID)

do until rs.eof
    intJobProcessCount = DCount("*", "dbo_operations", "job_no = 10248") + 1
    db.Execute ("insert into dbo_operations " & _
        "(part_item_id , job_process_count, operation_num, op_type, op_description, emp_id, est_mins ) " & _
        "VALUES (" & lngPartItemID & ", " & intJobProcessCount & ", " & _
        rs!operation_num & ", '" & rs!op_type & "', '" & rs!op_description & "', " & _
        rs!emp_id & ", " & rs!est_mins & ")")

    rs.movenext
loop

(In the VALUES part of the insert statement, you may need to put single quotes, or remove them in places where I was not sure if the field was numeric or not)
Essentially the code loops throught the templates table, counts how many entries there are for that job_no, adds 1, then inserts a record into the operations table. I have only put the main body of the code in as I 'm sure you can work out how to open and close a recordset, etc. It may not be pretty but it will work and should not be too slow for 10-20 records at a time. Also, I left the job_no as 10248 (as you had) because I was not sure where you got that from.

HTH,
Chris

 

[harleyskater]

Awesome! I will try to put some of that code into action! and see what it looks like! - Do you know any good tutorials on record sets? and VBA. I want to learn that side of access

I'm sure there must be a single SQL statement - all be it complicated - that can do this. However, you need a solution and I think I have one which should not be too slow:

set rs = db.openrecordset("SELECT operation_num, op_type, op_description, emp_id, est_mins " & _
    "FROM [dbo_operations_template] WHERE [dbo_operations_template].template_part_item_id = " & lngPartID)
 
do until rs.eof
    intJobProcessCount = DCount("*", "dbo_operations", "job_no = 10248") + 1
    db.Execute ("insert into dbo_operations " & _
        "(part_item_id , job_process_count, operation_num, op_type, op_description, emp_id, est_mins ) " & _
        "VALUES (" & lngPartItemID & ", " & intJobProcessCount & ", " & _
        rs!operation_num & ", '" & rs!op_type & "', '" & rs!op_description & "', " & _
        rs!emp_id & ", " & rs!est_mins & ")")
 
    rs.movenext
loop

(In the VALUES part of the insert statement, you may need to put single quotes, or remove them in places where I was not sure if the field was numeric or not)
Essentially the code loops throught the templates table, counts how many entries there are for that job_no, adds 1, then inserts a record into the operations table. I have only put the main body of the code in as I 'm sure you can work out how to open and close a recordset, etc. It may not be pretty but it will work and should not be too slow for 10-20 records at a time. Also, I left the job_no as 10248 (as you had) because I was not sure where you got that from.

HTH,
Chris