Basic questions from a newbie! :(

[Michael_]

Afternoon/Morning/Evening!

I'm trying to create a simple database. I've imported my data from Excel into a fresh table in a new DB. I didn't create the Excel data so have to work with it in this form.



The DB basically looks like this.




Dave has chosen option B and option D, Phil has only chosen 1 option. Options go onto span 60 options (prefixed by a date) and there's 120 names.


Now I know this is really basic and I'm so sorry but I'm just loosing my mind on this!!

I would like to print a report on who has chosen option A (there's about 120 names but all follow the example in layout) - Display this in a form first, but would be making a report to print off. If I could as a user select the name and then only have it display the options selected in the form?

Secondly I'd like to inform Dave and each person what options they have chosen - probably in a mailmerge. Happy enough doing the mailmerge or report but I know I need to query/filter the data first!


Hope this makes a little sense! Sorry for the basics. I'm kicking myself that I just can't do this. So far I can produce the report and the form but it displays all options - not just the chosen ones (1's in my case!)

Hope someone can help and thank you for taking the time to read this

Michael

[pbaldy]

Are you looking to correct the design, or work with this monstrosity as it is?

It sounds like you want to keep it like it is, which will be problematic. For starters, the name of your field isn't "Option A", but "2". So the direct answer to your question is a query:

SELECT [1]
FROM Sheet1
WHERE [2] = "1"

[TheShabz]

Do what Pbaldy said, but beforehand, reimport that excel sheet and check the "first row contains field names" to get proper field names.

[Michael_]

Opps...Thanks guys.

I have/had done that - just made that screenshot as an example and didn't import, just quickly typed something to try and show you what I meant!

Schoolboy error there....

So fields are correctly titled (unlike my screenshot!) as I described in the first post

Monstrosity? If only I hadn't made it worse by adding a shoddy screenshot....

My stupid posting mistake aside - Does your answer still stand pbaldy?

Thanks guys - much appreciated and thank you for your patience....

[pbaldy]

If there are still fields for dates and options, I stand by "monstrosity".

And presuming there are, then my query would still work, you would just need to adjust the field names to actual. If possible, I'd seriously consider normalizing the data when you bring it into Access.

[Michael_]

Baldy - Thanks again for replying. I'm not sure what you mean I'm afraid.




Hopefully this looks better - an actual screengrab of the db with names marked out.

Thanks again

[pbaldy]

Try

SELECT FirstName
FROM Sheet1
WHERE [10/05/2010 Option A] = 1

If I've correctly deduced what that field name is, and if the table name is still Sheet1.

That design will be really difficult to work with in the long run. It's normal spreadsheet design, but databases are designed to work differently. A normalized table might look like the attached, based on your first example (though in a normalized database you would only store the person's ID, not their name too). More info about the data would yield better design ideas.