MS Access problems - Really Stuck

[blasted]

Does anyone out there have any knowledge of MS Access?

I have a large table that has some 750 questions in it relating to vehicle parts. Each of the questions are as a record down the left hand side, whilst along the top there are about 40 different fields. The table is then made up of either a Y for yes or N for no. The point being you can look across the top of the table to the field "hydraulics" then read down for the Y's telling you which questions are required. I can do that with a query simple enough, by putting Y as a critera in the query.

Heres where I lose the plot.

I would like to construct a form that when its opened gives the user the option of selecting which fields they would like to search. It may be the case that only 8 fields are needed. By selecting the appropriate fields via a check box or similar, the user would be be presented with the questions marked Y for the fields they selected in a report.

Help would be greatly appreciated.

[damo_in_sale]

Originally Posted by blasted

Does anyone out there have any knowledge of MS Access?

I have a large table that has some 750 questions in it relating to vehicle parts. Each of the questions are as a record down the left hand side, whilst along the top there are about 40 different fields. The table is then made up of either a Y for yes or N for no. The point being you can look across the top of the table to the field "hydraulics" then read down for the Y's telling you which questions are required. I can do that with a query simple enough, by putting Y as a critera in the query.

Heres where I lose the plot.

I would like to construct a form that when its opened gives the user the option of selecting which fields they would like to search. It may be the case that only 8 fields are needed. By selecting the appropriate fields via a check box or similar, the user would be be presented with the questions marked Y for the fields they selected in a report.

Help would be greatly appreciated.

40 fields is an very large number. When you have so many fields it is often because you haven't rationalised the data structure properly, and this causes all sorts of problems later on.

Lets say you sell DVD's.

You could have a table that has all of the following information:

Customer name, address, tel., DVD ordered, DVD price, etc.

But in this case you should really have three tables.

A customer table:

Name, Address, Tel, email etc.

Then you should have a DVD table: Title, price, stock, etc.

Then you might have an order table:

Order number, Customer Number, DVD number.

These tables are then linked together.

Given that you suggest you have 750 questions, and each question is a record, that sounds like a 'quirky' way to use Access mate, unless I have the wrong end of the stick which is entirely possible .

If you don't know SQL then you can use the Wizards I suppose. If you're a business then you could perhaps do with a professional to help you in this regard.

Kind regards,

Damo


PS, I missed that you can do queries mate. I think I wrongly assumed that you were new to databases and Access. In which case much of what I have written in just ABC. Please accept my apologies.

[blasted]

Im still rather new to access, and i dont know how to programme in VB.

The situation is, I was asked to make the existing system more user friendly by sticking a front end on it. If there is a better way to organise the table then i can alter it.

The table is used as a question bank and the goal is to strip out the relevant questions. But i want the user to be able to select which fields they want, for example they may only want to drag out the questions regarding brakes, bearing and axles.

[RichardK]

Do you really need to use a form? There's already a feature in Access that'll do what you want. Open the table then look at the filter by form option off the records menu. It'll allow you to set your criteria and then apply the filter so it only shows the rows that match.

[Iaind]

it certainly sounds as if your database needs to be normalised. Wthout seeing the structure, but based on what you have said already I would suggest the following:

tbl_Section

UID (Integer)
SectionName (Text)
Archived (Integer)

tbl_Questions

UID (Integer)
SectionID (Integer)
QuestionText (Text)
Archived

tbl_Section.UID is the Foreign key (i.e.) linked to tbl_Questions.SectionID

Example Data:

tbl_Sections

UID SectionName Archived
1 Brakes 0
2 Steering 0

tbl_Questions

UID SectionID QuestionText Archived
1 1 What are brakes 0
2 1 Where are the brakes 0
3 2 What is the steering 0

SQL Queries.

To Select Categories/Sections

Select UID,SectionName from tbl_Sections where Archived = 0 order by SectionName

To Select Questions from selected Category

Select UID,QuestionText from tbl_Questions where SectionID = [SelectedSection] and Archived = 0 Order by UID

By using the Archived flag you can set questions on and off without actually deleting them from the database. Also, you can be inputting new data into the database that is hidden until you want it to appear to the user.

Cheers,
Iain

[blasted]

Thanks chaps, Its all on the works computer, and as such i wont be able to work on it until monday. But rest assured i shall no doubt make a right mess of it.

regards

blasted

[Singh400]

I’ve tried to understand your post several times, but I get nowhere. For a start it sounds like your DB isn’t properly set-up/designed.

How are you inputting data? Directly into the table view, or via form?

It sounds like “questions” and “hydraulics” are in one table. They should be in separate tables, with correct relationships.

Then pulled together using queries, and the data entry should take place via form built from the query.

If it all possible, could you upload a mock sample of your DB for us to look at?

I’m no DB expert, so no guarantees

[RichardK]

I don't understand why anyone feels the need to redesign the database. The table has only got 750 rows in, so even for Access it's a very small table, and with the way the OP is using it there's not going to be anything other than the simplest queries run against it. And, if any of the questions can be in more than one of the groups of parts, the design is correct anyway.

[mark1080p]

Originally Posted by RichardK

The table has only got 750 rows in, so even for Access it's a very small table, and with the way the OP is using it there's not going to be anything other than the simplest queries run against it.

Agree.

As its a small amount of data, I think it might be best to export to excel and build an auto filter on the 40 columns.

Please correct me if im wrong but all you need is a simple query to filter what questions to ask when choosing car part?

Regards

Mark

[blasted]

To try and give a better understanding of why this table is needed. If you were to build a vehicle, you would also need to supply a maintenance plan for that vehicle. People (us) then need to check that maintenance plan against the mandatory requirments of vehicle maintenance before we can sign it off. So, if you were to have a form that by use of a check box for example, you could check the required parts, it would then pull up the appropriate questions. the questions relate to individual requirements e.g. for hydraulics, does the plan ask for the calibrations of gauges?

The table its self is really easy. At present there is no real need to input any data, i'm not to bad with the form side of things. Whilst the questions are records running down the left hand side, the fields along the top are the different components, the table matrix is then filled out with Y or N. I dont really see how i could break it down, other then to have a table called hydraulics, with between 10 to 40 questions and so on for all 40 sections. Its not to bad for me to use, but other people with no access skill at all will be using it, hence the need for a pretty tick box form and a printed report.

The ideal solution for the other office users is to be able to open access, a front sheet pops up, so they dont see the table. on this sheet is a list with all the fields, the user simply checks the required ones, access then pulls all the questions for each field with a Y, and puts it in a report.

I do appreciate the help guys, so keep it coming.