can I use field name as a query search criteria? Please help. 2

[russai]

Hi. Here's the situation.

(Not my database design, but I need to go with what is available.)

There are 2 dozen tables with 13 fields each named for a calendar quarter. (i.e., 1/2005, 1/2006, 3/2005... ) The fields hold currency values. There is also a snn text field.
Tables will share 12 field names with others.

I need a query where I set the search criteria/parameter to look for a specific ssn and then the field name that I want (ex., 1/2006) and have the values for each table having that field name show.

How do I do this? Please help.

Russ

 

[dhookom]

I would use a union query to normalize the table structure so that your field names become data values in a "quarter" field. You search query can then use a criteria on the generated "quarter" values.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[russanalyst]

Thanks for the idea.

But can you give me an example of what the union coding might look like? (Or anyone else, out there?)

Here's more details. Each table shares a SSN text field. However, a specific ssn might exist in all 24 tables, or only 3, or 5, or...
Likewise, each table has 13 other fields named after calendar quarters (ex, 1/2004..., 1/2006, 2/2006,... 1/2007). Obviously, many tables will share many of the same field names, but not all.

The administrator wants a query/report where she will enter a specific ssn, enter the field name she seeks (ex, 1/2006) and get a result showing the values in each table for that field name where the record also shows the ssn.

I know this might be confusing, but...

Russ

 

[lespaul]

Example:

SELECT SSN, "1/2006" As Quarter, [1/2006] As Amount FROM TableName
UNION
SELECT SSN, "1/2004", [1/2004] FROM TableName

this results in:
[tt]SSN Quarter Amount
123456789 1/2006 52.35
123456789 1/2004 74.35[/tt]





Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[russanalyst]

Leslie,

Thank you for the response. Please let me ask some dumb questions related to this.

* Will this coding allow me to get results if in each table for that ssn, I ask for the same field name? (The same quarter's field name will exist in many, but not all of the tables

* Will this work with2 dozen tables? Or is that too many?

* What happens if the requested SSN does not exist in any one or more of the stated tables? I have no real way of knowing (without manually checking) which tables will have a record with that particular SSN.

* Do I use a parameter in the select part of the query for the desired SSN?

Thank you for your patience and your help.

Russ

 

[lespaul]

Will this coding allow me to get results if in each table for that ssn, I ask for the same field name? (The same quarter's field name will exist in many, but not all of the tables
If you save the union query as "qryNormalized" and then use that as your source (in the FROM clause of the search query) then you can search by SSN or Quarter or both. The fact that not every quarter exists in every table is overcome by creating this union query. You will end up with a normalized dataset that contains a record for each combination of SSN and Quarter that exists in all your tables.

Will this work with2 dozen tables? Or is that too many? It really depends on the number of fields, not the number of tables.

What happens if the requested SSN does not exist in any one or more of the stated tables? I have no real way of knowing (without manually checking) which tables will have a record with that particular SSN. Once you have the qryNormalized you use that for the search query. It doesn't matter if a particular table has the information, your new query does.

Do I use a parameter in the select part of the query for the desired SSN? Yes, or build a form that allows the user to select existing SSN or Quarters.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[russanalyst]

Thank you so very much for your time and knowledge!

While not used tow orking with Union queries, I will give it a shot.

ONE LAST QUESTION, if I may??
When looking at your coding example for the union query, do I have to explicitly specify each possible field name for each of the 2 dozen tables, individually?

Russ

 

[lespaul]

You will need a query for each field in each table in the UNION query. You may also need a UNION query for each table. But once you get ALL the information from the 12 tables in a single result set then you write ANOTHER query:

SELECT * FROM qryNormalized WHERE SSN = [Enter SSN to search]

You will want to use qryNormalized for ANY query you write searching this database, it contains the data in the correct format for use within a RDBMS.

Read the fundamentals link below for more information on normalization.

HTH

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[russanalyst]

Thank you, so much, again, Leslie.

(As an aside, I do, at least, understand the basics for mormalization. The problem is that I 'inherit' these tables from the gov't which creates a separate table for the SSNs and the most recent 13 calendar quarters of wages, once per calendar quarter. And they want to be able to track differences for the same person and quarters, among other trhings that they want to check.)

Thanks so much!

Russ

 

[dhookom]

Thanks for the great follow-up Leslie...

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[russanalyst]

Hi, folks.
Sorry to come back on this same topic, but an agency programmer was asked to help me with this project.
However, he said you folks who responded are wrong. (How dare he!)
He said there are far easier and better ways to search for values by the name of the field. Yet he did not offer anything that worked.
Do you have other ideas? I do not want this to be a power struggle. But I need some feedback/ideas here.

Thanks in advance.

Russ

 

[dhookom]

The structure is definitely wrong and the union queries will create a dataset that is more easily queried and reported.

If you can't use the queries as outlined by Leslie and me, there is a query by form applet at

http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

This can be imported into your application and allows users to select fields, set criteria, sorting, grouping,...

However, storing data in field names is not ever a good practice.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[russanalyst]

Thanks, so very much for the response.
I will check the link.

I totally agree with any and all comments regarding the structure of these tables. Unfortunately, not really my choice.

Thanks again.

Russ