Search Form & Report of multiple fields

[Guest]

I have a table called Denials that has 4 fields called Denial1, Denial2, etc.
I also have a table Reasons that contain the have reasons for denials. Any
one record in the Denials table could have any combination of reasons from
the Reasons table.

I want to create a report that groups based on the Reasons. So Denial1 may
have same Reason as Denial 4 in 2 different records. Just not sure how to
group this in the report. Tryed several ways.

I also want to create a search form that if I enter keyword it would search
all 4 fields of Denials on each record. I can get it to search but only off
1 field.

Record examples:
Name Denial1 Denial2 Denial3
Name1 No Info Incomplete Missing
Name2 Incomplete
Name3 Incomplete Missing

 

[Allen Browne]

Whenever you see fields repeating like your Denial1, Denial2, ..., it always
means you need a related table where you can enter many records, instead of
having many fields. The reason is exactly as you describe: you then have
just one field to search to find all the reasons. You can count, group, and
compare much more easily.

If you cannot use a correct design, you are stuck with trying to workaround
the issue with fudges such as a UNION query to try to get all the values
into the same column. This kind of thing:

SELECT [Name], Denial1 AS Denial
FROM Table1 WHERE Denial1 Is Not Null
UNION ALL
SELECT [Name], Denial2 AS Denial
FROM Table1 WHERE Denial2 Is Not Null
UNION ALL
SELECT [Name], Denial3 AS Denial
FROM Table1 WHERE Denial3 Is Not Null
UNION ALL
SELECT ...

BTW, I hope that Name is just an example here. That's not a good field name,
since almost every object in Access has a Name property. For example, Access
will get confused between the Name of of the form and the value of the Name
field. Hopefuly you have a table of people (or whatever) with an ID (primary
key), and you are using that value as a foreign key in this table rather
than a field called Name.

 

[Guest]

Thanks Allen,

Yes, Name is just an example.

I think I understand what you are saying, but was having a difficult time
conceptilizing how to do the tables and get them to work on data entry form.
I will play around with it and see if I can get it to work.

Whenever you see fields repeating like your Denial1, Denial2, ..., it always
means you need a related table where you can enter many records, instead of
having many fields. The reason is exactly as you describe: you then have
just one field to search to find all the reasons. You can count, group, and
compare much more easily.

If you cannot use a correct design, you are stuck with trying to workaround
the issue with fudges such as a UNION query to try to get all the values
into the same column. This kind of thing:

SELECT [Name], Denial1 AS Denial
FROM Table1 WHERE Denial1 Is Not Null
UNION ALL
SELECT [Name], Denial2 AS Denial
FROM Table1 WHERE Denial2 Is Not Null
UNION ALL
SELECT [Name], Denial3 AS Denial
FROM Table1 WHERE Denial3 Is Not Null
UNION ALL
SELECT ...

BTW, I hope that Name is just an example here. That's not a good field name,
since almost every object in Access has a Name property. For example, Access
will get confused between the Name of of the form and the value of the Name
field. Hopefuly you have a table of people (or whatever) with an ID (primary
key), and you are using that value as a foreign key in this table rather
than a field called Name.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a table called Denials that has 4 fields called Denial1, Denial2,
etc.
I also have a table Reasons that contain the have reasons for denials.
Any
one record in the Denials table could have any combination of reasons from
the Reasons table.

I want to create a report that groups based on the Reasons. So Denial1
may
have same Reason as Denial 4 in 2 different records. Just not sure how to
group this in the report. Tryed several ways.

I also want to create a search form that if I enter keyword it would
search
all 4 fields of Denials on each record. I can get it to search but only
off
1 field.

Record examples:
Name Denial1 Denial2 Denial3
Name1 No Info Incomplete Missing
Name2 Incomplete
Name3 Incomplete Missing

 

[Allen Browne]

Here's another example, with a bit more explanation of how it works:
Relationships between Tables (School Grades example)
at:
http://allenbrowne.com/casu-06.html

If you are trying to research it further, the 3rd table is called a junction
table, and it is the standard way of resolving a many-to-many relation into
a pair of one-to-many relationships.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks Allen,

Yes, Name is just an example.

I think I understand what you are saying, but was having a difficult time
conceptilizing how to do the tables and get them to work on data entry
form.
I will play around with it and see if I can get it to work.

Whenever you see fields repeating like your Denial1, Denial2, ..., it
always
means you need a related table where you can enter many records, instead
of
having many fields. The reason is exactly as you describe: you then have
just one field to search to find all the reasons. You can count, group,
and
compare much more easily.

If you cannot use a correct design, you are stuck with trying to
workaround
the issue with fudges such as a UNION query to try to get all the values
into the same column. This kind of thing:

SELECT [Name], Denial1 AS Denial
FROM Table1 WHERE Denial1 Is Not Null
UNION ALL
SELECT [Name], Denial2 AS Denial
FROM Table1 WHERE Denial2 Is Not Null
UNION ALL
SELECT [Name], Denial3 AS Denial
FROM Table1 WHERE Denial3 Is Not Null
UNION ALL
SELECT ...

BTW, I hope that Name is just an example here. That's not a good field
name,
since almost every object in Access has a Name property. For example,
Access
will get confused between the Name of of the form and the value of the
Name
field. Hopefuly you have a table of people (or whatever) with an ID
(primary
key), and you are using that value as a foreign key in this table rather
than a field called Name.

I have a table called Denials that has 4 fields called Denial1, Denial2,
etc.
I also have a table Reasons that contain the have reasons for denials.
Any
one record in the Denials table could have any combination of reasons
from
the Reasons table.

I want to create a report that groups based on the Reasons. So Denial1
may
have same Reason as Denial 4 in 2 different records. Just not sure how
to
group this in the report. Tryed several ways.

I also want to create a search form that if I enter keyword it would
search
all 4 fields of Denials on each record. I can get it to search but
only
off
1 field.

Record examples:
Name Denial1 Denial2 Denial3
Name1 No Info Incomplete Missing
Name2 Incomplete
Name3 Incomplete Missing

 

[Guest]

Thanks the light bulb just came back on. Been many years since I have worked
with this.

Here's another example, with a bit more explanation of how it works:
Relationships between Tables (School Grades example)
at:
http://allenbrowne.com/casu-06.html

If you are trying to research it further, the 3rd table is called a junction
table, and it is the standard way of resolving a many-to-many relation into
a pair of one-to-many relationships.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks Allen,

Yes, Name is just an example.

I think I understand what you are saying, but was having a difficult time
conceptilizing how to do the tables and get them to work on data entry
form.
I will play around with it and see if I can get it to work.

Whenever you see fields repeating like your Denial1, Denial2, ..., it
always
means you need a related table where you can enter many records, instead
of
having many fields. The reason is exactly as you describe: you then have
just one field to search to find all the reasons. You can count, group,
and
compare much more easily.

If you cannot use a correct design, you are stuck with trying to
workaround
the issue with fudges such as a UNION query to try to get all the values
into the same column. This kind of thing:

SELECT [Name], Denial1 AS Denial
FROM Table1 WHERE Denial1 Is Not Null
UNION ALL
SELECT [Name], Denial2 AS Denial
FROM Table1 WHERE Denial2 Is Not Null
UNION ALL
SELECT [Name], Denial3 AS Denial
FROM Table1 WHERE Denial3 Is Not Null
UNION ALL
SELECT ...

BTW, I hope that Name is just an example here. That's not a good field
name,
since almost every object in Access has a Name property. For example,
Access
will get confused between the Name of of the form and the value of the
Name
field. Hopefuly you have a table of people (or whatever) with an ID
(primary
key), and you are using that value as a foreign key in this table rather
than a field called Name.


I have a table called Denials that has 4 fields called Denial1, Denial2,
etc.
I also have a table Reasons that contain the have reasons for denials.
Any
one record in the Denials table could have any combination of reasons
from
the Reasons table.

I want to create a report that groups based on the Reasons. So Denial1
may
have same Reason as Denial 4 in 2 different records. Just not sure how
to
group this in the report. Tryed several ways.

I also want to create a search form that if I enter keyword it would
search
all 4 fields of Denials on each record. I can get it to search but
only
off
1 field.

Record examples:
Name Denial1 Denial2 Denial3
Name1 No Info Incomplete Missing
Name2 Incomplete
Name3 Incomplete Missing