? search and return?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am looking for away to search the table below for "RGRND" and if possible
return the corresponding "Total" Unfortunately "rgrnd" does not always fall
into the same column.
For instance under ps it would return 380.

Any ideas?

Name Date C1 C1TOTAL C2 C2TOTAL C3 C3TOTAL C4 C4TOTAL

PS 10/18 OT 420 REG 380 RGRND 380 OTGRND 420
DS 10/18 REG2 2004 RGRND 2004
SS 10/18 REG 15 OT2 1227 OTGRND 1227 RGRND 15
BS 10/18
Thanks
Jeanne
 
I'm sure it's obvious to you, faced with this problem, that your data are
not normalized. One should never be puttting the same data in "separate"
fields that are identified by "number" labels. That is not how a database
works best.

To do what you want with the data structure that you're using, you'll need
to create two separate queries.

The first query must "normalize" your data. It is a union query that puts
all three data field combinations into a "single field":

SELECT T.[Name], T.C1 AS IDName, T.C1Total AS IDTotal
FROM Tablename AS T
UNION ALL
SELECT TT.[Name], TT.C2, TT.C2Total
FROM Tablename AS TT
UNION ALL
SELECT TTT.[Name], TTT.C3, TTT.C3Total
FROM Tablename AS TTT;

Create and save this query; call it qryUnion.

Then create a second query that will be used to find the data that you wish:

SELECT Q.[Name], Q.IDName, Q.IDTotal
FROM qryUnion AS Q
WHERE Q.IDName = [Enter the text string to find:];

This second query will find the desired info for you.

Also, I note that you're using Name and Date as the names of fields in your
table. This is not a good practice because Date is the name of a VBA
function, and Name is the name of a property for many objects. Using such
reserved words can confuse ACCESS. For more info on which words should not
be used as field names, control names, etc., see this Knowledge Base
article:
ACC2002: Reserved Words in Microsoft Access
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335
 
Dear Jeanne:

I recommend the data be composed properly. If this is done, you can
perform your search quite easily and directly.

Properly normalized, this information would look like:

Name Date ColNo C TOTAL
PS 10/18 1 OT 420
PS 10/18 2 REG 380
PS 10/18 3 RGRND 380
PS 10/18 4 OTGRND 420
DS 10/18 1 REG2 2004
DS 10/18 2 RGRND 2004
SS 10/18 1 REG 15
SS 10/18 2 OT2 1227
SS 10/18 3 OTGRND 1227
SS 10/18 4 RGRND 15

From this you can simply filter the one column C to find a value like
"rgrnd" because it does fall into one column. That's one of the
reasons why this is the way the database must be designed.

The way to convert the appearance of the data is with a Normalizing
UNION:

SELECT [Name], [Date], 1 AS ColNo, C1 AS C, C1TOTAL AS Total
FROM YourTableName
WHERE C1 IS NOT NULL
UNION ALL
SELECT [Name], [Date], 2 AS ColNo, C2 AS C, C2TOTAL AS Total
FROM YourTableName
WHERE C2 IS NOT NULL
UNION ALL
SELECT [Name], [Date], 3 AS ColNo, C3 AS C, C3TOTAL AS Total
FROM YourTableName
WHERE C3 IS NOT NULL
UNION ALL
SELECT [Name], [Date], 4 AS ColNo, C4 AS C, C4TOTAL AS Total
FROM YourTableName
WHERE C4 IS NOT NULL

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Thanks for your help! I will try it.
The information is information is download from another system and makes its
own database. That is the reason the database is not normalize. Will the
first query normalize all the data everytime I download the table?


Ken Snell said:
I'm sure it's obvious to you, faced with this problem, that your data are
not normalized. One should never be puttting the same data in "separate"
fields that are identified by "number" labels. That is not how a database
works best.

To do what you want with the data structure that you're using, you'll need
to create two separate queries.

The first query must "normalize" your data. It is a union query that puts
all three data field combinations into a "single field":

SELECT T.[Name], T.C1 AS IDName, T.C1Total AS IDTotal
FROM Tablename AS T
UNION ALL
SELECT TT.[Name], TT.C2, TT.C2Total
FROM Tablename AS TT
UNION ALL
SELECT TTT.[Name], TTT.C3, TTT.C3Total
FROM Tablename AS TTT;

Create and save this query; call it qryUnion.

Then create a second query that will be used to find the data that you wish:

SELECT Q.[Name], Q.IDName, Q.IDTotal
FROM qryUnion AS Q
WHERE Q.IDName = [Enter the text string to find:];

This second query will find the desired info for you.

Also, I note that you're using Name and Date as the names of fields in your
table. This is not a good practice because Date is the name of a VBA
function, and Name is the name of a property for many objects. Using such
reserved words can confuse ACCESS. For more info on which words should not
be used as field names, control names, etc., see this Knowledge Base
article:
ACC2002: Reserved Words in Microsoft Access
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335


--

Ken Snell
<MS ACCESS MVP>



jeanne said:
I am looking for away to search the table below for "RGRND" and if possible
return the corresponding "Total" Unfortunately "rgrnd" does not always fall
into the same column.
For instance under ps it would return 380.

Any ideas?

Name Date C1 C1TOTAL C2 C2TOTAL C3 C3TOTAL C4 C4TOTAL

PS 10/18 OT 420 REG 380 RGRND 380 OTGRND 420
DS 10/18 REG2 2004 RGRND 2004
SS 10/18 REG 15 OT2 1227 OTGRND 1227 RGRND 15
BS 10/18
Thanks
Jeanne
Click to expand...
Click to expand...
 
Dear Jeanne:

My friend Ken's solution is nearly identical. However, your sample
data indicates you do not always have all 4 datapoints for each Name
and Date. I put a criterion in to eliminate creating those rows. I
believe this would be an improvement.

Another difference is that I preserved numbering for the sets 1 to 4
within the data. This may or may not be useful.

The query will normalize the data every time it is run. You can
either create a maketable from this, append the rows to another table,
or just use the query as the basis for your search and any other work.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


Thanks for your help! I will try it.
The information is information is download from another system and makes its
own database. That is the reason the database is not normalize. Will the
first query normalize all the data everytime I download the table?


Ken Snell said:
I'm sure it's obvious to you, faced with this problem, that your data are
not normalized. One should never be puttting the same data in "separate"
fields that are identified by "number" labels. That is not how a database
works best.

To do what you want with the data structure that you're using, you'll need
to create two separate queries.

The first query must "normalize" your data. It is a union query that puts
all three data field combinations into a "single field":

SELECT T.[Name], T.C1 AS IDName, T.C1Total AS IDTotal
FROM Tablename AS T
UNION ALL
SELECT TT.[Name], TT.C2, TT.C2Total
FROM Tablename AS TT
UNION ALL
SELECT TTT.[Name], TTT.C3, TTT.C3Total
FROM Tablename AS TTT;

Create and save this query; call it qryUnion.

Then create a second query that will be used to find the data that you wish:

SELECT Q.[Name], Q.IDName, Q.IDTotal
FROM qryUnion AS Q
WHERE Q.IDName = [Enter the text string to find:];

This second query will find the desired info for you.

Also, I note that you're using Name and Date as the names of fields in your
table. This is not a good practice because Date is the name of a VBA
function, and Name is the name of a property for many objects. Using such
reserved words can confuse ACCESS. For more info on which words should not
be used as field names, control names, etc., see this Knowledge Base
article:
ACC2002: Reserved Words in Microsoft Access
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335


--

Ken Snell
<MS ACCESS MVP>



jeanne said:
I am looking for away to search the table below for "RGRND" and if possible
return the corresponding "Total" Unfortunately "rgrnd" does not always fall
into the same column.
For instance under ps it would return 380.

Any ideas?

Name Date C1 C1TOTAL C2 C2TOTAL C3 C3TOTAL C4 C4TOTAL

PS 10/18 OT 420 REG 380 RGRND 380 OTGRND 420
DS 10/18 REG2 2004 RGRND 2004
SS 10/18 REG 15 OT2 1227 OTGRND 1227 RGRND 15
BS 10/18
Thanks
Jeanne
Click to expand...
Click to expand...
Click to expand...
 
Good changes, Tom!

--

Ken Snell
<MS ACCESS MVP>

Tom Ellison said:
Dear Jeanne:

I recommend the data be composed properly. If this is done, you can
perform your search quite easily and directly.

Properly normalized, this information would look like:

Name Date ColNo C TOTAL
PS 10/18 1 OT 420
PS 10/18 2 REG 380
PS 10/18 3 RGRND 380
PS 10/18 4 OTGRND 420
DS 10/18 1 REG2 2004
DS 10/18 2 RGRND 2004
SS 10/18 1 REG 15
SS 10/18 2 OT2 1227
SS 10/18 3 OTGRND 1227
SS 10/18 4 RGRND 15

From this you can simply filter the one column C to find a value like
"rgrnd" because it does fall into one column. That's one of the
reasons why this is the way the database must be designed.

The way to convert the appearance of the data is with a Normalizing
UNION:

SELECT [Name], [Date], 1 AS ColNo, C1 AS C, C1TOTAL AS Total
FROM YourTableName
WHERE C1 IS NOT NULL
UNION ALL
SELECT [Name], [Date], 2 AS ColNo, C2 AS C, C2TOTAL AS Total
FROM YourTableName
WHERE C2 IS NOT NULL
UNION ALL
SELECT [Name], [Date], 3 AS ColNo, C3 AS C, C3TOTAL AS Total
FROM YourTableName
WHERE C3 IS NOT NULL
UNION ALL
SELECT [Name], [Date], 4 AS ColNo, C4 AS C, C4TOTAL AS Total
FROM YourTableName
WHERE C4 IS NOT NULL

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


I am looking for away to search the table below for "RGRND" and if possible
return the corresponding "Total" Unfortunately "rgrnd" does not always fall
into the same column.
For instance under ps it would return 380.

Any ideas?

Name Date C1 C1TOTAL C2 C2TOTAL C3 C3TOTAL C4 C4TOTAL

PS 10/18 OT 420 REG 380 RGRND 380 OTGRND 420
DS 10/18 REG2 2004 RGRND 2004
SS 10/18 REG 15 OT2 1227 OTGRND 1227 RGRND 15
BS 10/18
Thanks
Jeanne
Click to expand...
Click to expand...
 
Well, thanks, Ken. There is a philosophy behind the column I call
ColNo, which is to preserve all the information in the original data
such that you could recreate the original data if necessary. I don't
like to think of "reducing the total amount of information" in the
data in any way.

Not generating rows where the values are NULL is probably obvious.

These weren't actually changes. Your post came through while I was
composing mine.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Back
Top