multiple values in a field?

[march madness]

I have made a form for a new database. It is broken up into sections. One of the sections is Facts. For some records, there are only 3 facts, for others, maybe 6, and perhaps for others, maybe 10. So I have one cell per possible fact, fact1 throught fact10. The facts will always be different per record. Since the number of facts differ per record, what I'd really like to do is have one textbox called Facts, and enter in as many facts as there are, each one separated by a semicolon. That way, I can have 12 facts in one record, 3 in another, and I wouldn't have 7 blank cells...

The end result for me is to print out a report that will list the facts like bullet points (the actual bullets don't matter, as I suspect Access won't format a record like that). Also, I'd like to be able to search on a category, not facts, but another category I have called Issues. Some of the issues will be the same, so I would like to search for all the cases with some issue. I'd like to set the issues category up the same way as I've described the facts category. Right now, the issues category is set up like the facts category, issue1 to issue10, all textboxes.

So, does anyone know if I can put multiple values in a cell, be able to access those values individually, print them out in a report, and search or filter for them? Or do I need the 10 separate fields? I'm also trying to avoid having to add another field if one case has 11 facts or 11 issues. Let me know. I wanted to post the form using the HTML maker, but that only seems to work with excel.

you can see the form here, if this works:
http://us.f1f.yahoofs.com/bc/7baa2d4d/bc/photos/BriefForm.jpg?bfkuN5ABBIYp_ntV

 

[Norie]

I did a "Create new table in design view" and created

This isn't what I meant.

The following assumes that main table has BriefID which uniquely identifies each brief and fields Fact1...Fact10 which contain the facts.

What you need to do is create a query that selects BriefID and Fact1.

Create a new query add the main table.

From the main table add the fields BriefID and Fact1.

View the query, it should just list BriefID's and Fact1's.

Return to design view and right click.

From the pop up menu select Query Type>Make Table Query...

You will then be prompted for a table name, say Facts.

Now Access will create a table called Facts with 2 fields BriefID and Fact1.

Close the query, you can save it if you want.

Now open the facts table in design view.

Change the name of the field Fact1 to just Fact.

Now add an Autonumber field FactID and set it as the primary key.

I'll explain how to append fields Fact2 to Fact10 later, I'm off to the chippy.

how do I make FactID and BriefID invisible?

On a form each control has a visible property, set it to false to hide the control.

You need what is in the control but don't need/want to see it.


Ziggy

how do I make FactID and BriefID invisible?

You should leave it for now so you can see how it works, later you can remove them from the design.

Best to keep them in the design.

FactID for later addition. deletion of facts

and

BriefID to link back to main table

 

[march madness]

Ok. I created a database design. I didn't have any real data in it, so I have been starting from scratch. I think this is why creating the new table from a query isn't necessary for me, right?

 

[Norie]

It isn't necessary but in my opinion, the best way to do it is using the query approach.

This is because the data types and field sizes of your fields wil automatically be carried over into the new table.

Also it will populate the fields automatically.

Have you tried the query way?

 

[march madness]

Well, I sortof tried it, but couldn't figure it out. When I found out that you suggested it so as I would not have to recreate my DB, I figured I could just create a new table -- since I was starting from scratch anyway.

Anyway, I now have all the subforms in the main form. It seems to work quite well. It'd be nice to see all the records of each subform from the main form, but I figure I can't do that because of how I started this...each brief will have a different number of facts.

Now I'd like to set my report up. As of now, I don't like what I get from the wizard, it is printing the facts in reverse order. I'm not sure how to fix that.

Also, I want to be able to search it. Maybe I could create a couple of starndard searches, like "list all" of something or another, and put them on a switchboard, but I'd really like to do a text search, like "gun" or "accident" and get those records, even though they'd be in the text of a fact or an issue, not as a neat category. Can that be done?

Oh, and you all have been so fast to reply, thanks. I would have but was celebrating Independence Day this weekend.