Advise on queries
- Thread starter rainbows
- Start date
It is good style to use only the fields that are really needed. So these fields are to be listed by name and therefore individually.
Why?
For fields that are called, the contents must also be loaded. Loading unnecessary data costs time, puts additional load on the network, etc.
If the table contains such clumsiness as lookup fields or calculated fields, they would also perform their recalculations, even if they are not needed.
//OT:
Why do you always upload such large images with quite little meaningfulness?
For example, when talking about a query, the SQL statement from the SQL view, presented as code text, shows much more. Moreover, you could immediately copy, correct and return the suggestion.
Why?
For fields that are called, the contents must also be loaded. Loading unnecessary data costs time, puts additional load on the network, etc.
If the table contains such clumsiness as lookup fields or calculated fields, they would also perform their recalculations, even if they are not needed.
//OT:
Why do you always upload such large images with quite little meaningfulness?
For example, when talking about a query, the SQL statement from the SQL view, presented as code text, shows much more. Moreover, you could immediately copy, correct and return the suggestion.
- Local time
- Today, 13:38
- Joined
- Feb 28, 2001
- Messages
- 30,625
@rainbows - concur with ebs17. Specifying only the fields you want means less network traffic, and besides that, more data can be fit into the I/O buffers that are behind the scenes during the data transfers. This increases memory usage efficiency. The "*" calls back everything in the table or query and thus calls for "wider" records than a selective query would call back. For slower networks, "more data" isn't always your friend.
thank you , to take that further if you have a table with say 20 fields in it and a form with 20 fields in it and then you want to create another form using data from that table say 7 fields is it best to call up that table again as i have to get the 7 fields or would you create a form with a query that just calls up them 7 fields
steve
steve
Exactly that.
Also remember that you work in a form as well. Filters, Requery ... all this leads to the retrieval of data from the table.
To make it noticeable: It is for sure a difference for you if you carry 7 bottles of water or 20 bottles of water, even more if you are busy for a longer time and have to walk long ways over hill and dale.
Your computer and your programs are also only water carriers and thank you if you are careful with the allocation of work.
on the form below i used a table with the "*" and the drop down box " find batch number works ok , i have now tried to use the query without the "*" and the drop down box did not work i have tried to add another one drop down box and you can see i only get 2 option . if i change it back to the table and use the " *" it works again . and idea why ?
thanks steve
thanks steve
Ideas I have.
If the wizard offers only all fields by * instead of single selection on fields, the wizard is not suitable or you use it wrong.
I myself don't use this wizard practically at all, but I set the properties of the combo box in the property sheet by hand.
In all cases you should open your eyes and know what you are doing.
If the wizard offers only all fields by * instead of single selection on fields, the wizard is not suitable or you use it wrong.
I myself don't use this wizard practically at all, but I set the properties of the combo box in the property sheet by hand.
In all cases you should open your eyes and know what you are doing.
- Local time
- Today, 14:38
- Joined
- Feb 19, 2002
- Messages
- 47,713
"Doesn't work" doesn't mean anything. You really need to tell us what "doesn't work" actually means and include any error messages.
The wizard is relying on the table definition for the primary key. If you did not select the PK in the RecordSource query, the wizard would not be able to offer the option you are looking for.
I have a twist to the suggestion regarding the "*". the ONE place I use a query with the "* to select all is as the RecordSource for the edit form for a table. Since I almost never create multiple forms to edit tables, the edit form always handles all columns so for convenience, I use the Select *. HOWEVER, I also use criteria in the RecordSource query to limit the rows returned. Usually I have an unbound combo or text search box in the form's header that allows the selection of a specific record so the Where argument refers to that/those unbound controls. That makes the form open empty. The user then uses the controls to select the desired record. If there is only one control to do the selection, I use the AfterUpdate event of the control and add a Me.Requery to rerun the RecordSource query.
This is incompatible with using the wizard which creates different code to accomplish the same process. The reason I use my method rather than using the wizard as you are trying to do, it because I NEVER want to return all records of a table from the server and then filter them locally. Most of my BE's are SQL Server or some other RDBMS which this technique is targeted to. But, using the technique even when the BE is Jet or ACE is good practice, especially if you think the app might have to be upsized in the future. Because I use various client/server best practices like this, I can upsize any app I build to SQL Server within a few hours and it only takes that long because extensive testing must take place to ensure I didn't miss anything
So 2 things:
1. if you select the PK, the wizard will probably work again.
2. move your unbound search fields to the form's header to separate them from the editable fields.
For all other references to tables, I NEVER use the Select *. I always select individual columns.
The wizard is relying on the table definition for the primary key. If you did not select the PK in the RecordSource query, the wizard would not be able to offer the option you are looking for.
I have a twist to the suggestion regarding the "*". the ONE place I use a query with the "* to select all is as the RecordSource for the edit form for a table. Since I almost never create multiple forms to edit tables, the edit form always handles all columns so for convenience, I use the Select *. HOWEVER, I also use criteria in the RecordSource query to limit the rows returned. Usually I have an unbound combo or text search box in the form's header that allows the selection of a specific record so the Where argument refers to that/those unbound controls. That makes the form open empty. The user then uses the controls to select the desired record. If there is only one control to do the selection, I use the AfterUpdate event of the control and add a Me.Requery to rerun the RecordSource query.
This is incompatible with using the wizard which creates different code to accomplish the same process. The reason I use my method rather than using the wizard as you are trying to do, it because I NEVER want to return all records of a table from the server and then filter them locally. Most of my BE's are SQL Server or some other RDBMS which this technique is targeted to. But, using the technique even when the BE is Jet or ACE is good practice, especially if you think the app might have to be upsized in the future. Because I use various client/server best practices like this, I can upsize any app I build to SQL Server within a few hours and it only takes that long because extensive testing must take place to ensure I didn't miss anything
So 2 things:
1. if you select the PK, the wizard will probably work again.
2. move your unbound search fields to the form's header to separate them from the editable fields.
For all other references to tables, I NEVER use the Select *. I always select individual columns.
