Automating the process of making a query.

[michaelak117]

I know its possible to have Access run a query, but is there any way to have it create and run the query for you? I have the same number of fields and data types coming in daily and I'd like to automate it to that point to make it easier? The process is simple and involved dragging moving the fields down, apply a group by, and setting some criteria..

 

[boblarson]

I know its possible to have Access run a query, but is there any way to have it create and run the query for you? I have the same number of fields and data types coming in daily and I'd like to automate it to that point to make it easier? The process is simple and involved dragging moving the fields down, apply a group by, and setting some criteria..

The description you are giving makes me think that this database is not normalized, nor is it designed well enough to handle incoming data. Can you explain your situation further? Where does this data come from, why does it have the same number of fields and data types but you require them to be in different tables, to have different queries, etc.

 

[michaelak117]

The data comes from a computer simulation and spits out raw data involving text fields and a few number fields. Then the object is to run a query and draw out the "days" data (todays date). There are relationships built between the daily data and other data lookups to attempt to answer your normalizaion question, but no relationships build for the whole database, refering to the datasheet tools--> relationships option. I hope that helps, i just really want to automate basically the same steps in queries I run daily, but in macros only know about the run query macro?

 

[boblarson]

Actually, you really need to append that data to an EXISTING table structure and then you can have your queries built as necessary and then you can set their criteria so you get whatever you want.

 

[michaelak117]

Thats exactly what im doing. The table is appended to daily and I use criteria to query what I need for daily processing.

 

[boblarson]

Thats exactly what im doing. The table is appended to daily and I use criteria to query what I need for daily processing.

Then I'm confused as to why you are in the need of "automating the making of a query." Your queries should be getting input criteria from a form where you can select the criteria. You can set defaults on the form's On Open event so you don't have to fill in things like today's date, but that's where the criteria should come from, not hard-coded in the query. Does that help at all?

 

[michaelak117]

Yes that makes sense, but when you say form Im confued, im getting my input from the table thats being appended to daily, Is that what your saying. Its just the fact that I create new query everyday, so your saying use the same query, set Open events for the criteria and then just save the query as??

 

[boblarson]

Yes that makes sense, but when you say form Im confued, im getting my input from the table thats being appended to daily, Is that what your saying. Its just the fact that I create new query everyday, so your saying use the same query, set Open events for the criteria and then just save the query as??

Okay, you run queries. But, instead of typing in the criteria for the query, you reference a form's controls for it.

See the attached Sample. Open the query to see how it references the form.

 

[michaelak117]

Very cool, thanks for the help

 

[michaelak117]

Do you know if its possible to have one form automate multiple criteria. Like have it do todays data and then also another fields criteria? Is it possible do something similar w/ a group by total??

 

[boblarson]

Do you know if its possible to have one form automate multiple criteria. Like have it do todays data and then also another fields criteria? Is it possible do something similar w/ a group by total??

Essentially, if it is something you would normally type into the criteria line you can do it from a form.