Running Multiple Queries Simultaneously? (1 Viewer)

[Casanova411]

Hello
I need help with running multiple queries that are in relation to each other to perform one function, in addition to that, being able to use one parameter in those queries performing one function. ATM i have to click each query and input the same parameter 2-3 times. It is quite painstaking to do. Everything in my database is functional, i just couldn't find a way to shorten it.

One function has different types of queries: Update, Append, and Delete.

If you could help, you would be a lifesaver!!

 

[pr2-eugin]

Have you considered Running SQL queries via VBA.?? You can have the criteria set before hand something like..

sqlCriteria = " WHERE conditionField='SomeValue'
sqlSelect = "SELECT * FROM tblName" & sqlCriteria
srlUpdate = "UPDATE tblName SET updateField='newValue'" & sqlCriteria
DoCmd.RunSQL (sqlSelect)
DoCmd.RunSQL (sqlUpdate)

 

[plog]

I think you are experiencing symptoms of a larger issue. I'm 95% certain you can structure your queries more efficiently. I'm 75% certain you have a poor table structure.

Can you post your database and the procedure you are running to get the results you want?

 

[Pat Hartman]

Use a form with controls that hold the parameters. In the queries, reference the form fields.

Select ...
From yourtable Where somefield = Forms!yourformname!yourcontrolname;

 

[Casanova411]

Have you considered Running SQL queries via VBA.?? You can have the criteria set before hand something like..

sqlCriteria = " WHERE conditionField='SomeValue'
sqlSelect = "SELECT * FROM tblName" & sqlCriteria
srlUpdate = "UPDATE tblName SET updateField='newValue'" & sqlCriteria
DoCmd.RunSQL (sqlSelect)
DoCmd.RunSQL (sqlUpdate)

Could you give me a brief example using one of my queries? I tend to understand by examples more than place holders.

 

[Casanova411]

I think you are experiencing symptoms of a larger issue. I'm 95% certain you can structure your queries more efficiently. I'm 75% certain you have a poor table structure.

Can you post your database and the procedure you are running to get the results you want?

I have posted my database. I do think i could structure my queries to run more efficiently, but i have a problem of trying to deal with everything in one step. Could you tell me a way to improve? I learned all this by myself.

 

[Casanova411]

Use a form with controls that hold the parameters. In the queries, reference the form fields.

Select ...
From yourtable Where somefield = Forms!yourformname!yourcontrolname;

This would help the problem of having to keep inputting the same parameter right? I would have to insert it in all the involved queries? Sorry if i ask stupid questions, i am trying to get all the information before i hack away at the database. I've learned to plan before coding away from experience.

 

[Pat Hartman]

If you need to run a lot of reports with the same parameters, the easiest solution is a specialized form. If each report is run from only one place, then the query for each report would reference the form from which it is run. But in this scenario, you could use the Where argument of the OpenReport Method rather than changing the query. So, if you want to print an invoice from the Invoice form, the button on the invoice form would use the Where argument of the OpenReport method to tell the report to print one specific invoice. However, if you have a bunch of reports that are run monthly and you want them to all use the same date range, you would make a form that is used to run reports and add controls for from and through date. All the monthly report queries would reference the date fields on that form or again, you could use the Where argument to pass in the date range. Both methods work. The form control reference works best in some cases but the Where argument works best in others. Try each and see which works best for you.

BTW, if you are using OutputTo to export PDF's, the form control method is the only option.

 

[plog]

Yeah, you've got table problems. One general issue is naming conventions. You should eliminate spaces and special characters from your table and field names. As for the tables themselves, you only need 3: Equipment, Borrowers and EquipmentAssignments.

Equipment would essentially be the same as the Equipment table you have now, with one less id field. You have Equipment Number and Equipment ID fields, what's the difference between the two?

Borrowers is essentially your 'PHS-Senior & West' table. The only problems I have with it is nomenclature. 'PHS-Senior & West' has spaces and that ampersand in it, which isn't a good idea. Also, it sounds too specific, like you are going to have 4 more tables named something like 'PHS-Junior & East', 'PHS-Sophomore & South'. Table names shouldn't contain data you want to capture.

EquipmentAssignments is similar to your 'Assigned Equipment' and 'Previously Assigned Equipment' tables. It will be used to make matches between your Equipment table and your Borrowers table. The problem you have now is that 'Assigned Equipment' and 'Previously Assigned Equipment' should be merged--there's no reason to have seperate tables. This will eliminate you moving data around and instead allow you to write a SELECT query to extract the records you need.

EquipmentAssignments should have these fields:

PermID, EquipmentID, AssignDate, ReturnDate

That's it. With that structure and properly written select queries you will be able to get all the information you want from your database.

 

[Casanova411]

If you need to run a lot of reports with the same parameters, the easiest solution is a specialized form. If each report is run from only one place, then the query for each report would reference the form from which it is run. But in this scenario, you could use the Where argument of the OpenReport Method rather than changing the query. So, if you want to print an invoice from the Invoice form, the button on the invoice form would use the Where argument of the OpenReport method to tell the report to print one specific invoice. However, if you have a bunch of reports that are run monthly and you want them to all use the same date range, you would make a form that is used to run reports and add controls for from and through date. All the monthly report queries would reference the date fields on that form or again, you could use the Where argument to pass in the date range. Both methods work. The form control reference works best in some cases but the Where argument works best in others. Try each and see which works best for you.

BTW, if you are using OutputTo to export PDF's, the form control method is the only option.

Oh finally after rereading several times I understand what you are saying. Thank you very much I made a simple thing so difficult for myself. I should of gone through reports and controls better. Thank you again, if i should run into a problem would it be alright if i was to pm you?

 

[Casanova411]

Yeah, you've got table problems. One general issue is naming conventions. You should eliminate spaces and special characters from your table and field names. As for the tables themselves, you only need 3: Equipment, Borrowers and EquipmentAssignments.

Equipment would essentially be the same as the Equipment table you have now, with one less id field. You have Equipment Number and Equipment ID fields, what's the difference between the two?

Borrowers is essentially your 'PHS-Senior & West' table. The only problems I have with it is nomenclature. 'PHS-Senior & West' has spaces and that ampersand in it, which isn't a good idea. Also, it sounds too specific, like you are going to have 4 more tables named something like 'PHS-Junior & East', 'PHS-Sophomore & South'. Table names shouldn't contain data you want to capture.

EquipmentAssignments is similar to your 'Assigned Equipment' and 'Previously Assigned Equipment' tables. It will be used to make matches between your Equipment table and your Borrowers table. The problem you have now is that 'Assigned Equipment' and 'Previously Assigned Equipment' should be merged--there's no reason to have seperate tables. This will eliminate you moving data around and instead allow you to write a SELECT query to extract the records you need.

EquipmentAssignments should have these fields:

PermID, EquipmentID, AssignDate, ReturnDate

That's it. With that structure and properly written select queries you will be able to get all the information you want from your database.

Thanks for the advice I am going to start working on tables right away before working with the queries and such. Equipment Number and Equipment ID are two different fields. Equipment Number is just an auto number primary key. It solved a problem for me trying to assign a student an equipment and having he/she return it and assign it to a different student, which would not be possible since primary key values are unique. Equipment ID would be a bar code ID. Essentially the student would give there ID to be scanned and then the equipment would be scanned. Does that make sense? It is the process of like going to a library and checking out a book. As for PHS-Senior & West is just the name of my school. It has two campuses, but the students in the table are from both the campuses, so i will change the name. Could you explain "Table names shouldn't contain data you want to capture"?

 

[plog]

Table names should be generic and not hold data that might be used. You wouldn't have tables called 'ScheduleMay', 'ScheduleJune', 'ScheduleJuly'. You would have one table called Schedule and then a field in that table which allows you to designate a month to distinguish all that data. It sounds like you are doing it correctly, its just that the name of your table makes it sound like you are not. Just be sure if you want to distinguish between the campuses your students are on, you don't create a new table for each campus, you create a field in your table to hold that data.

You said Equipment Number is unique. Is Equipment ID? If so, you don't need Equipment Number. You don't need an autonumber if you have a unique field already--like what you did with the PHS-Senior & West table.

 

[Casanova411]

Table names should be generic and not hold data that might be used. You wouldn't have tables called 'ScheduleMay', 'ScheduleJune', 'ScheduleJuly'. You would have one table called Schedule and then a field in that table which allows you to designate a month to distinguish all that data. It sounds like you are doing it correctly, its just that the name of your table makes it sound like you are not. Just be sure if you want to distinguish between the campuses your students are on, you don't create a new table for each campus, you create a field in your table to hold that data.

You said Equipment Number is unique. Is Equipment ID? If so, you don't need Equipment Number. You don't need an autonumber if you have a unique field already--like what you did with the PHS-Senior & West table.

Okay makes sense, I need to be more general and specifics could/should be inside the table. I guess i could get rid of one of the fields since i can make the database count how many equipment are in inventory. Thank you 3x. If you would be willing would you check my database so that it is efficient after completion?

 

[plog]

No problem, whenever you are ready just post the file to this thread.

 

[plog]

Action queries (i.e. UPDATE, MAKE TABLE, ALTER TABLE) are generally signs of a bad database. When a database is properly structured (as yours is) you rarely need to employ these types of queries. You're overkilling it.

Your AssignParameter and ReturnParameter forms should be bound to the EquipmentAssignments table--you shouldn't be using your queries to get data into that table. When people use those forms it should directly be loaded into the EquipmentAssignments table.

 

[plog]

I'm waiting on a reply to my reply.

 

[Casanova411]

haha sorry i forgot there was a second page. So i would be better off using the Forms to directly input the data into the EquipmentAssignments? I've never done that it is the same as having the query do such? Instead of having the parameters be passed to the query i just redirect to the Table? I self taught myself how to use Access sorry for the questions. You can rest assure this knowledge is not going to waste.

 

[plog]

To all your questions: Yes.

 

[Casanova411]

Finally! i did it. i was getting errors left and right while binding it, furthermore even more when adding additional features to my form.
This isn't a major problem but before when the form was unbound i had the date automatically insert itself into the Assigned Date text box. But now since it is bound i can not enter the =date(). Any idea on how to shove it back in?

 

[plog]

In the table and the form you can set the default for that field. Put =Date() in both places.