Query to find a specific 'feature' in a table

[ladyck3]

I must apologize if this question has been asked and answered a million times, but I have no idea what to search for to look for an answer. I know just enough about databases (Access) to impress anyone sitting next to me by putting my info into tables (big whoopie... File > Get External Data) cuz I do a lot of work in Excel with making lists 'n things. I can import my brains out... but when it comes to utilizing the data (any type of expressions, macros, vba) I'm fully and completely lost.

I have a table of unit features, the first field is the model, the remaining 24 fields are y/n fields or fields with specific info pertaining to that particular feature represented in that field.

If, creating a query, in the Model field (Field 1) in the Critera line I enter the following Like "*" &[Which Model?]& "*" it will pull up a dialog box and any model number or portion thereof, produces the unit with the features for that unit.

Lets make each of the 24 feature fields be labeled Feature1, Feature2, ...etc

If I wish to create a query to be able to label it "Which Feature?" and allow the user to enter, for instance, Feature16, yes, to pull up a list of all of the units which Feature16 applies to and not show the ones which, in the table state Feature16 as a No.

Or say Feature19 is a feature which can have multiple responses such as ABC, DEF, XYMH, whatever... and I want the user to be able to enter XYMH and it produce a list of all units which support or relate to XYMN, how do i do this?

I created a query using all fields from the table, model through feature 14, then in the Criteria field mentioned above, used Like "*" &[etc.... for each column. It pulls up an input field for the user but it returns either a table of the entire contents or pulls up one record and its empty. I know this is wrong but I have no clue what tools or feature or query or what I need to research in order to find some sort of code to make this query do what I want it to do.

Please help.
I do appreciate it.... VERY MUCH. Also, if YOU understand what I am trying to do and may have some information on what I should be researching for or even have some links, I sure would appreciate it!

I have been struggling with this for a week now and am now throwing in the towel.. UNCLE!

LadyCK3
(aka: Laurie)


LadyCK3

 

[dhookom]

Laurie,
Is there any chance you could normalize your table structure? I would have created tables like:
tblUnits
UnitID
Model
...

tblFeatures (one record per feature)
FeatureID
FeatureName
...

tblUnitFeatures
UnitFeatID
UnitID links to tblUnits.UnitID
FeatureID links to tblFeatures.FeatureID


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]

 

[ladyck3]

Well the I used "Feature1, Feature2" as examples so as to not reveal what product matrix I am working with.

Each one of the fields has a specific title, for example:

-- Camera (this is a yes/no)
-- Camcorder (this is a yes/no)
-- Email Video (this is a yes/no)
Then we get into other field such as:
-- Phonebook Entries (variables such as 500, 1000, 2000, Available Memory) - a text field due to the variables
-- Sync Software (variables from No, to name of software)
-- Sync Transfer (entites supported in synching again variables)
-- Connection (connection type supported, Serial, USB or Serial/USB, again variables)

The first column in the table is "Model" each subsequent 24 fields are all uniquely named. I might have to create a query for each feature? But how to link those into one form or report or whatever, is this some of that crosstab query stuff that I have no clue what it really is much less how or when to use it? I told you I"m clueless but REALLY want to at least understand what I'm doing a little bit

Thanks in advance....
Laurie


LadyCK3

 

[dhookom]

If you can't change the structure, you could try create a union query that normalizes your "spreadsheet" type table.
SELECT Model, "Camera" as Feature
FROM tblUnits
WHERE Camera
UNION ALL
SELECT Model, "Camcorder"
FROM tblUnits
WHERE Camcorder
UNION ALL
SELECT Model, "Email Video"
FROM tblUnits
WHERE [Email Video]
UNION ALL
...etc...

This would create a recordset that might be easier to query and/or report.


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]

 

[ladyck3]

Oh Duane...

That sounds good but I am way over my head. I'm creating tables for each entity because I know I can create a query for each one and then let the report do the work... What you are talking about makes perfect sense but I have no clue how to accomplish this and will not tax you for a step by step, trust me I can be very cumbersome trying to do something I have no clue how to do..

I thank you for your assistance, and did not want to go without sending you a thank you. I just don't feel comfortable having other's do the work .... it pains me to even have to ask... its a matter of I just don't know yet... but don't expect everyone here to teach me either... its just toooooo much to ask.

Thanks Duane.. I mean it
Laurie


LadyCK3

 

[lespaul]

What Duane is doing is creating a query that will take your non-normalized data and convert it to a normalized model. You can then save this query and use it as the source for the query you originally asked about.

for more information on normalization check out The Fundamentals of Relational Database Design

So you currently have:

Model          Camera          CamCorder        Email
1234             yes             no               yes
2345             no              yes              no
3456             no              no               yes

using Duane's query you will end up with a result set like this:

Model            Feature
1234             Camera
1234             Email
2345             CamCorder
3456             Email

Then you can use this query to get model where Feature = 'Email' and see all the models that have email, or Feature = 'Camera' and see all the models that have the camera feature.

HTH

leslie

 

[dhookom]

Thanks 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]

 

[cheerio]

The problem with the original structure is that whenever you add a new feature to the system you have to add another column to the table and then you have to redesign every query, form and report to take account of that feature.

Although it sounds more complicated to have more tables, the suggestions made by Duane and Leslie will create a solution that will be more stable.

Normalization is simply the process of getting data from your starting format into a form that is easier to maintain.

 

[ladyck3]

Thanks to everyone. After I opted out of this conversation due to overwhelming myself and frustration... I think it just kinda of 'came to me' today.

Last night I created a table for each entity

Unit Camera
123 Yes
124 N0


Unit Camcorder
123 No
124 Yes

Whatever... thinking this would minize the confusion for me... HA!

What I've done is the Matrix I created which is some 24+ fields of features and created a query using that table but only using fields in a group such as a query for Camera/Camcorder and any fields that apply for one report.

Then one for Messaging/Email and fields that pertain for another query/report

Software Support and associated fields for a Software/Transfer query/report. I think this will suit my purposes. My switchboard is looking pretty cool as well.

I just have to get this complete and then decide how to incorporate this on the web

Baby steps... gotta take this in baby steps... I appreciate everyone's input, seriously... thank you!!!

Laurie



LadyCK3

 

[lespaul]

In the long run this approach is going to cause you to do a lot of maintenance work. Did you read the document on normalization?

Leslie

 

[ladyck3]

I just downloaded the document, have not had a chance to view it... am on my way to do just that.



LadyCK3