Specific Query

[skydiver]

Access 2003. I have a table with 60+ fields. I also have numerous queries/reports for specific data. Instead of having a specifc query/report for specifc data, I would like to create a "parameter" query/report that can randomly pull any data I want from my table. HELP!

 

[John Big Booty]

You could us an unbound form with various combo boxes and text fields to provide criteria for your query, in the criteria use;

Like "*" & Forms!FRM_YourFormName!ControlName & "*"

 

[Galaxiom]

A parameter query can only supply values. It does not allow you to specify the fields that contain that value. To do this you need to make a dynamic query using VBA.

With over sixty fields in a table there is a very good chance that your data is not properly normalized. A different data structure would probably make your querying task much easier.

 

[skydiver]

Big Booty,

Can you elaborate on the "various combo boxes" and "text fields?" Also, how would I be able to use this form to extract any specific data on any specific day for any specific field?

 

[skydiver]

Big Booty,

Can you elaborate on the "various combo boxes" and "text fields?" Also, would I be able to use this form to extract any specific data for any specific day, on any specific field?

 

[jdraw]

Watch these FREE video tutorials.

http://www.datapigtechnologies.com/flashfiles/searchform.html
http://www.datapigtechnologies.com/flashfiles/combobox1.html
http://www.datapigtechnologies.com/flashfiles/combobox2.html
http://www.datapigtechnologies.com/flashfiles/combobox3.html

 

[skydiver]

jdraw,

Excellent turtorials, however most of my data is numeric. I would like a parameter query that would enable me to select any specific field for any specific date range and retrieve that specific number (stat) from the field of my choice. Any suggestions?

 

[Galaxiom]

Selecting the field to query requires it to be dynamically written in VBA. The field cannot be selected via a parameter.

You could use a form with combos to select the target fields.

However, on top of the 60 fields mentioned earlier, wanting to retrieve a numeric value from any field confirms that the data structure is the real problem. A properly normalized design would greatly simplify the task and allow static queries (even parameter queries) to be used.

 

[skydiver]

Alright Galaxiom...I was trying to avoid you, but I guess I need to face the music. The 60+ fields I have on the main table/form is used to record specific data that may or may not occur for a single event. For each event there may be any of those fields utilized to collect a statistic. Once the event is completed, the next event is ready to be processed and so on and so forth. Are you saying for me to condense these fields and categorize them in several tables in order for my database to be normalized? Scary thought. Be gentle Galaxiom.

 

[MSAccessRookie]

The data layout has not been specified, but it sounds more like a Spreadsheet layout than a Database layout, so Normalization could be an issue here. If the Layout of the Database (no data required) were available for comment, then perhaps a better assesment could be made.

 

[Galaxiom]

Alright Galaxiom...I was trying to avoid you, but I guess I need to face the music. The 60+ fields I have on the main table/form is used to record specific data that may or may not occur for a single event. For each event there may be any of those fields utilized to collect a statistic. Once the event is completed, the next event is ready to be processed and so on and so forth. Are you saying for me to condense these fields and categorize them in several tables in order for my database to be normalized? Scary thought. Be gentle Galaxiom.

The fields in a table should be used to store aspects of the records that are essentially different in some way. For example in a Person table you might have FirstName, Surname and DOB. Quite different things.

In your case, each statistic is in essence a variant of the same thing. In relational databases these kind of records are held in a related table. The Stats table has a "Foreign Key" which is used to join the records to a particular record in the Events table via its Primary Key.

Your Stats table will generally have the following fields:

StatID: The Stats Primary Key used to join to other related tables that might be linked to these records.

EventID: This is the Foreign Key that connects the Stat record to a record in the Event table.

StatTypeID: This links to another table which holds the human comprehendible description of the Stat. The ID is an Integer so the computer can quickly connect the records. The forms and reports lookup the StatName field from the StatTypes table for the user.

StatValue: The number you are currently recording in the Statistics-n fields of the Events table.

StatNote: Other stuff about the statistic. You can have multiple fields for this if you want. Otherwise you have a StatNotes table related by the StatID in a similar way to the Event-Stats relationship and have as many records there for separate StatNotes as you like. A structure like this allows different users to record notes each with an entry time stamp.

This concept is the essence of relational data structure is is fundamental to RDBs.

As you can see this means instead of limiting the statistics to the number of fields in the Events table you can literally have millions of statistics linked to one Event record.

Moreover instead of adding new Statistics-n fields to the Events table you can add new types of statistics by simply adding records to the StatTypes table. (On your forms you choose the the StatType from a combobox whose RecordSource property is a query on the StatTypes table.)

Querying becomes trivial. Now when you need information from a Stat you simply query the Stats table.

You may limit it to a particular Event record using a WHERE clause with the EventID FK. Use a WHERE with a particular StatTypeID to show all those StatIDs for every EventID or you can use both together.

These two fields plus the EventDate can select everything you want to know about the Stats for any Event trough a query with a join between the tables on the PK and FK fields.

This structure is simple and blindingly fast, especially if you Index the fields you are selecting with the WHERE Clause. Also index the FKs in any table.

The Stats records are displayed in a Continuous Forms or Datasheet subform. So instead of having to use 60 controls on the main form you only need two on the subform, the StatType and the StatValue (plus the StatNote if you want.)

They all neatly line up without any intervention and you can sort them to a standard order on the subform recordsource query. This can be done alphabetically on the StatTypeName or by a custtom order recorded in a field in the StatTypes table. There are many tricks of the trade to organise them in columns too.

Hope this makes sense.