Acces report

[Judith van der Niet]

Hello,

I have a question I hope you all can help me with. What I want to do is
search all records in all columns from a table, but in the result only show
the columns that somewhere contained the search word. It is for making a
report, and I do not want to show the colums that are not important (do not
somewhere contain the searchword). Is this even posible? I hope someone can
give me a hand in this. Tnx in advance.

Judith

 

[Jeff Boyce]

Judith

Before moving into reporting, there's a possibility that your Access
database (this is a newsgroup dedicated to supporting Microsoft Access, a
relational database product) needs further normalization.

It would be very unusual to have a well-normalized database that could have
a search term located in any of the columns in a table. It would be much
more likely to have this situation working with a spreadsheet.

Why does it matter? Because the features and functions that Access offers
are optimized for working with well-normalized tables of data.

Rather than having to search all columns and identify which ones contain the
search term, in a well-normalized design, you'd likely only need to search a
single column.

Can you provide a bit more description of your data/situation? The
newsgroup's readers may be able to offer an alternate approach to what
you've asked.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[Judith van der Niet]

Jeff,

Thank you for your quick reply! I know that a normal database needs
normalisation, but i don't think this one can be normalised. This table
consists of a ID field, a Fullname and a number of tekst fields that contain
information about the person in the full name field. The information is
typed in manualy, so the user can not choose from a number of predefiened
valueas. A example of what the want: the want to search for the word
"football". This word is found in the column "Hobby" but also in the Column
"sports". In my report i would only like to see the column Fullname, hobby
and sports. I hope this clears up some things. Hope you can help. tnx

Judith

 

[Jeff Boyce]

Judith

You may wish to take a look into a "normalizing query". This is a UNION
query that combines several individual queries.

In your example, you could create one query that lists ID & Hobby, a second
query that lists ID & Sports, and a third query that combines these into a
single, two-column query, something like:

SELECT YourID, Hobby
FROM YourTable
UNION
SELECT YourID, Sports
FROM YourTable;

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[Larry Linson]

How about a Person Table, and a related Interests Table, with a foreign key
to the Person Table, an Interest Area (e.g., Hobby, Sport, School,...) and
the Interest. That would be a normalization that would greatly simplify your
search... that is, you could simply search the "Interest" Field for a
Particular Person and it would return Football for Hobby, Sport, Abiding
Passion, and Religion if it was entered for all those "Interest Areas"?

The related Table could have from zero to nearly-unlimited entries for any
given Person/Interest Area combination.

Larry Linson
Microsoft Access MVP