Selecting Fields From a Record

L

Leo

I have a qry called qryEPO built from table called 'Schd.'
Each record has 71 fields, EPO1 through EPO23, Date1 through Date23, Med1
through 23. another field for Name, and a User input field called 'What Date?'

Criterion for EPO fields is >0. (EPO fields are number fields)
Criterion for Med fields is NotNull (Med fields are Text fields)

When query is Run it should ask for user input for the 'WhatDate' and select
only those records which meet the criteria EPO and /Or (both and as well as
Or) criteria Med. It should not select when both EPO and Med field do not
meet the criteria. Of course the it should select only one from Date1 through
Date23 that matches the 'WhatDate'.

When user inputs 8/3/2009 then the display need look as follows:
Name Date Epo Med
JohnDoe 8/3/2009 30000 Kefzol
JaneDoe 8/3/2009 Venofer
BobBob 8/3/2009 20000
RobertBob 8/3/2009 500 Vanco

How can I accomplish this?

Additional Info:
All the data is in one table called Schd, Except the name which is in
another table called 'Client_Name'. The two tables are linked bu a common
field called 'ID_Number.'(One to Many)

Please Help.
Thanking you
Leo
 
J

Jerry Whittle

You have "commited spreadsheet". Your table has serious normalization
problems and just will NOT work correctly in a relational database. This is
evidence by the repeating EPO, Med, and Date fields across. What you really
need is another table or two so that this information can be stored down, not
across.

Think about this: What happens to all your reports, forms, and queries if
someone add a 24th EPO/Med/Date? It's back to the drawling board time.

I highly recommend getting some relational database training or reading
"Database Design for Mere Mortals" by Hernandez before proceeding any further
on this database.
 
J

John Spencer MVP

What you want to do is almost impossible given your current table structure.
The table Schd should have four fields (total)

ID_Number (Field identifying specific Client)
TheDate (Date Field containing one of the values in fields Date1 to Date23)
EPO (Number field containing one of the values in fields EPO1 to EPO23)
Med (Text field containing one of the values in fields Med1 to Med23)

Once you have that design you can work with queries to get the needed information.

Right now you would have to have 23 or clauses in your query to return the
desired information and all the columns would have have to be returned. The
SQL for that query would look something like

SELECT ClientName.[ClientNameField], Schd.*
FROM Schd INNER JOIN Client_Name
ON Schd.Id_Number = Client_Name.Id_Number
WHERE (EPO1 >0 and Med1 is Not Null ANd Date1 = CDate([What Date?]))
OR (EPO2 >0 and Med2 is Not Null ANd Date2 = CDate([What Date?]))
....
OR (EPO23 >0 and Med23 is Not Null ANd Date23 = CDate([What Date?]))

Now that will return every column in schd table.

You might be able to modify the SELECT clause to return one set of fields
using a calculated field with the SWITCH function. However, if there are two
sets of fields that match your criteria then you are SOL.

SELECT ClientName.[ClientNameField]
, Switch(Date1=[What Date?],Date1,...Date23=[What Date?],Date23) as TheDate
, Switch(Date1=[What Date?],Epo1,...Date23=[What Date?],Epo1) as EPO
, Switch(Date1=[What Date?],Med1,...Date23=[What Date?],Med23) as Med
FROM Schd INNER JOIN Client_Name
ON Schd.Id_Number = Client_Name.Id_Number
WHERE (EPO1 >0 and Med1 is Not Null ANd Date1 = CDate([What Date?]))
OR (EPO2 >0 and Med2 is Not Null ANd Date2 = CDate([What Date?]))
....
OR (EPO23 >0 and Med23 is Not Null ANd Date23 = CDate([What Date?]))

Alternative to building the a new table might be to use a UNION query to
properly format the data and then use the saved union query as the source

SELECT ID_Number, Date1 as TheDate, EPO1 as EPO, Med1 as Med
FROM schd
UNION ALL
SELECT ID_Number, Date2 , EPO2 , Med2
FROM schd
UNION ALL
SELECT ID_Number, Date2 , EPO2 , Med2
FROM schd
....
UNION ALL
SELECT ID_Number, Date22 , EPO22 , Med22
FROM schd
UNION ALL
SELECT ID_Number, Date23 , EPO23 , Med23
FROM schd



John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
L

Leo

John Spencer MVP said:
What you want to do is almost impossible given your current table structure.
The table Schd should have four fields (total)

ID_Number (Field identifying specific Client)
TheDate (Date Field containing one of the values in fields Date1 to Date23)
EPO (Number field containing one of the values in fields EPO1 to EPO23)
Med (Text field containing one of the values in fields Med1 to Med23)

Once you have that design you can work with queries to get the needed information.

Right now you would have to have 23 or clauses in your query to return the
desired information and all the columns would have have to be returned. The
SQL for that query would look something like

SELECT ClientName.[ClientNameField], Schd.*
FROM Schd INNER JOIN Client_Name
ON Schd.Id_Number = Client_Name.Id_Number
WHERE (EPO1 >0 and Med1 is Not Null ANd Date1 = CDate([What Date?]))
OR (EPO2 >0 and Med2 is Not Null ANd Date2 = CDate([What Date?]))
....
OR (EPO23 >0 and Med23 is Not Null ANd Date23 = CDate([What Date?]))

Now that will return every column in schd table.

You might be able to modify the SELECT clause to return one set of fields
using a calculated field with the SWITCH function. However, if there are two
sets of fields that match your criteria then you are SOL.

SELECT ClientName.[ClientNameField]
, Switch(Date1=[What Date?],Date1,...Date23=[What Date?],Date23) as TheDate
, Switch(Date1=[What Date?],Epo1,...Date23=[What Date?],Epo1) as EPO
, Switch(Date1=[What Date?],Med1,...Date23=[What Date?],Med23) as Med
FROM Schd INNER JOIN Client_Name
ON Schd.Id_Number = Client_Name.Id_Number
WHERE (EPO1 >0 and Med1 is Not Null ANd Date1 = CDate([What Date?]))
OR (EPO2 >0 and Med2 is Not Null ANd Date2 = CDate([What Date?]))
....
OR (EPO23 >0 and Med23 is Not Null ANd Date23 = CDate([What Date?]))

Alternative to building the a new table might be to use a UNION query to
properly format the data and then use the saved union query as the source

SELECT ID_Number, Date1 as TheDate, EPO1 as EPO, Med1 as Med
FROM schd
UNION ALL
SELECT ID_Number, Date2 , EPO2 , Med2
FROM schd
UNION ALL
SELECT ID_Number, Date2 , EPO2 , Med2
FROM schd
....
UNION ALL
SELECT ID_Number, Date22 , EPO22 , Med22
FROM schd
UNION ALL
SELECT ID_Number, Date23 , EPO23 , Med23
FROM schd



John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
I have a qry called qryEPO built from table called 'Schd.'
Each record has 71 fields, EPO1 through EPO23, Date1 through Date23, Med1
through 23. another field for Name, and a User input field called 'What Date?'

Criterion for EPO fields is >0. (EPO fields are number fields)
Criterion for Med fields is NotNull (Med fields are Text fields)

When query is Run it should ask for user input for the 'WhatDate' and select
only those records which meet the criteria EPO and /Or (both and as well as
Or) criteria Med. It should not select when both EPO and Med field do not
meet the criteria. Of course the it should select only one from Date1 through
Date23 that matches the 'WhatDate'.

When user inputs 8/3/2009 then the display need look as follows:
Name Date Epo Med
JohnDoe 8/3/2009 30000 Kefzol
JaneDoe 8/3/2009 Venofer
BobBob 8/3/2009 20000
RobertBob 8/3/2009 500 Vanco

How can I accomplish this?

Additional Info:
All the data is in one table called Schd, Except the name which is in
another table called 'Client_Name'. The two tables are linked bu a common
field called 'ID_Number.'(One to Many)

Please Help.
Thanking you
Leo
Click to expand...
Thanks John. Didn't reply back soon, because it took me that long to write the SQL codes and correct them. It is working! I greatly appreciate your help.
Click to expand...

Just another question...for future ref. Are there any draw backs in using
the union querys? And how to output the results into a table?

Thanking you again
Leo
 
J

John Spencer

Drawbacks. The following two come immediately to mind.

UNION queries are not updatable.

Union queries are slow when used in other queries since you cannot use
any indexes for filtering or sorting.

UNION queries are limited in the number of fields they can handle, so
you may not be able to get all the data with one query.

To use the union query in a make table query, save the union query. Use
the query as if it were a table in a make table query. OR better (in my
opinion) is design the destination table with data types and indexes and
then use the union query as the source for an append query.


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Pulling particular fields from a record 7
prepopulate dates in a record using fields from another table 1
look for certain data on three different fields in a query 4
Multi-Update records from a form (Recordsets)? 6

Top