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

 

[Ziggy]

Your Database structure is not "Normalized". thats the term used when you have too many (redundant) fields in a table when they should be broken down into other tables.

In your case you have set up multiple fields for "Facts", which is often done when working with spreadsheets.


You may not like to hear it but...

Delete them from the table (with a back up copy of the DB of course). Then make a new table like:

tblFactDetail

FactID (autonumer) (primary key)
Fact (text)
*RelatedField in main table (longInteger)


*Field related to Original table (long Integer)

*now the table that your deleted facts came from should have an ID field (your primary key) what you want to do is make another field in the new fact table that is a number type (long Integer). This gives you a means by which the 2 can be related.


What you want to do is build a subform with the 2 tables, what a subform will do for you is this. You enter your data into the main table (header if you will) and then the subform allows you to list as many facts as you like.

You have to do the same with the issue data also

I just answered a very similar question ( still helping him) check here

http://www.mrexcel.com/board2/viewtopic.php?t=94199


But I strongly suggest that you read up on Subforms it should give you a better understanding of how to structure your tables. What you are working with is a ONE to MANY relationship. Try bulding a subform with the fact table and the main table first so you can understand it. I think you need to go with what I suggest in the link once you master it.

 

[march madness]

So, let me see if I'm clear. I need to make another form, tblFactDetail, with the (crap, now I've forgotten what they're called)
factID
fact
relatedField in main table. <-- I don't quite understand this.

I'd make the fact field, type memo. I don't understand why I need a subform, as opposed to just having a memo in the main table called fact, and putting in multiple values. Can you explain that more?

Then I have my main form briefs, and make tblFactDetail a subform of briefs, right? I also need to do the same thing with my other categories, Issues, and Procedure as I did with facts.

I've never used subforms before, but I have tried to normalize my db attempts by using multiple tables that only pertain to the one subject. I always have difficulty relating all the tables in such a way as if all the values of all the tables were on one form. That's for later, but eventually, that's what I'm aiming for, being able to make a report first get the right data out one record per page, and second, second be able to search on issues and maybe procedures, certainly on other text fields as well. Is this subform approach working toward that goal?

Thanks.

 

[SydneyGeek]

Yes, the subform based on the related table is the way to go. There are plenty of tips and tutorials on data normalization (the process of getting all teh related data where it belongs). Here's one that I found in a quick search -- describes it pretty succintly.

Denis

 

[Norie]

What Ziggy means is to create a new table which
contains all the facts.

It would also contain a unique ID for each fact and the
unique ID from the main table.

To start with you should create a make-table query from the
main table. It would contain only the fact1 field and the
unique ID for the record.

You will then need to do append queries to this table for each
of the other fact fields.

You should end up with a table with two fields which lists
all the facts from the main table and the unique ID of the
record they came from.

Then open the table you create in design view and add an
Autonumber field, call it FactID, as Ziggy suggested. Make it
the primary key.

You now have a facts table which can be used in forms/queries
with the main table.

The first form to build would be a facts form based on the
facts table.

Make it simple - just a textbox for each field, but only make
the fact field visible.

Set Default View to Continous Forms, make the height of the
form as small as possible - probably equal to the height of
the fact textbox.

You should now have a form that looks a bit like a datasheet.

The second form to create is the main form based on the main
table.

Remember to include the unique ID on this form.

Now, making sure the Toolbox Wizard is on, place a subform
somewhere on this form, below the other con

At the first prompt selecct Forms and the facts form.

Follow the steps of the wizard.

Now, hopefully you will have a main form which has information
from the main table at the top, and below the facts.

If you have inserted the subform correctly as you move
through the main records the facts displayed should be
those relevant to the current record.

Hope you understood all that.

 

[march madness]

What Ziggy means is to create a new table which
contains all the facts.

It would also contain a unique ID for each fact and the
unique ID from the main table.

To start with you should create a make-table query from the
main table. It would contain only the fact1 field and the
unique ID for the record.

You will then need to do append queries to this table for each
of the other fact fields.

You should end up with a table with two fields which lists
all the facts from the main table and the unique ID of the
record they came from.

Then open the table you create in design view and add an
Autonumber field, call it FactID, as Ziggy suggested. Make it
the primary key.

You now have a facts table which can be used in forms/queries
with the main table.

The first form to build would be a facts form based on the
facts table.

Make it simple - just a textbox for each field, but only make
the fact field visible.

Set Default View to Continous Forms, make the height of the
form as small as possible - probably equal to the height of
the fact textbox.

You should now have a form that looks a bit like a datasheet.

The second form to create is the main form based on the main
table.

Remember to include the unique ID on this form.

Now, making sure the Toolbox Wizard is on, place a subform
somewhere on this form, below the other con

At the first prompt selecct Forms and the facts form.

Follow the steps of the wizard.

Now, hopefully you will have a main form which has information
from the main table at the top, and below the facts.

If you have inserted the subform correctly as you move
through the main records the facts displayed should be
those relevant to the current record.

Hope you understood all that.

A unique ID for each fact? I think that might be impractical because as far as I know, the facts can't be put in little boxes or categories, they're all going to be uniquely worded. ie, defendant hits plaintiff over the head with a beer bottle. another fact in another case might be, defendant hits plaintiff over the head with a broken beer bottle, or defendant hits plaintiff in the eye with a beer bottle, or wine bottle, or shot glass. there could be thousands of facts and thus thousands, well, maybe we're taking hundreds here, of unique IDs. that just sounds impractical, no?

I'm getting confused with the query part. and linking a query with a table. I'm trying to do this in forms. I know a form is just a representation of a table, i'm just confused.

Also, lets call the fact subform, FactSubForm, with FactID, and Fact (which will be the semicolon separated list of all the facts for ALL the different records). <-- just had an epiphany here. if my subform is FactID and Fact, then I'd have one fact per fact id, and only two entries per record in the FactSubForm. Then, I suppose, in the BriefsForm(main form), in the Facts field, I'd list all the factIDs for that particular brief? or, in the FactSubForm, would I also have a field for BriefID, where each factID would then be linked to a BriefID?

Back to original thinking BEFORE epiphany. One of these thoughts have got to be right...
Then the main form, lets call that the BriefsForm, with a BriefID, and a Facts field. in the Facts field, I guess I'd either have all the factIDs pulled from the FactSubForm records, but I think I'd rather like to see the actual fact and the factID.

Again, the end result is a one page report that lists all the pertinent info, and a db that I can search on for briefs with the same issues, or same plaintiffs etc.

Does that make sense?

 

[Norie]

Yes a unique id for each fact i.e one created by you
normally using Autonumber.

Twice I've created databases like this, for similar purpose
as yourself.

In the main form you wouldn't have a facts field.

Since BriefID is in both tables it can be used as a link.

So you have BriefID in the BriefsForm and FactSubForm.

When you create the subform on the Briefsform the Wizard
should recognize this and create the link.

So

Facts Table

Fields

FactID Autonumber Long Integer
BriefID Number Long Integer
Fact Text or Memo

Data type for Fact field is best as Text for search/sort
and other processing.

 

[march madness]

Yes a unique id for each fact i.e one created by you
normally using Autonumber.

Twice I've created databases like this, for similar purpose
as yourself.

In the main form you wouldn't have a facts field.

Since BriefID is in both tables it can be used as a link.

So you have BriefID in the BriefsForm and FactSubForm.

When you create the subform on the Briefsform the Wizard
should recognize this and create the link.

So

Facts Table

Fields

FactID Autonumber Long Integer
BriefID Number Long Integer
Fact Text or Memo

Data type for Fact field is best as Text for search/sort
and other processing.

Ok, I'm going to take a shot at making this, just this small part first, this weekend, and I'll post my results/follow up questions.

 

[march madness]

What Ziggy means is to create a new table which
contains all the facts.

It would also contain a unique ID for each fact and the
unique ID from the main table.

To start with you should create a make-table query from the
main table. It would contain only the fact1 field and the
unique ID for the record.

I did a "Create new table in design view" and created
FactID Autonumber primary key
BriefID Number
Fact Memo

Is this what you mean? I'm not sure how to make a table from the Queries part of Access.

You will then need to do append queries to this table for each
of the other fact fields.

I have no idea what this means. Can you explain?

You should end up with a table with two fields which lists
all the facts from the main table and the unique ID of the
record they came from.

I have 3 fields, problem?

Then open the table you create in design view and add an
Autonumber field, call it FactID, as Ziggy suggested. Make it
the primary key.

You now have a facts table which can be used in forms/queries
with the main table.

Again, I skipped the query step. Can you explain how to do this?

The first form to build would be a facts form based on the
facts table.

Make it simple - just a textbox for each field, but only make
the fact field visible.

how do I make FactID and BriefID invisible?

Set Default View to Continous Forms, make the height of the
form as small as possible - probably equal to the height of
the fact textbox.

You should now have a form that looks a bit like a datasheet.

Haven't tried this yet.

The second form to create is the main form based on the main
table.

Remember to include the unique ID on this form.

Now, making sure the Toolbox Wizard is on, place a subform
somewhere on this form, below the other con

At the first prompt selecct Forms and the facts form.

Follow the steps of the wizard.

Now, hopefully you will have a main form which has information
from the main table at the top, and below the facts.

If you have inserted the subform correctly as you move
through the main records the facts displayed should be
those relevant to the current record.

Hope you understood all that.

I'll get to that soon.

 

[Ziggy]

I did a "Create new table in design view" and created
FactID Autonumber primary key
BriefID Number
Fact Memo

Is this what you mean? I'm not sure how to make a table from the Queries part of Access.

Yes that looks good.

Queries

Norrie is just giving you a method of creating your new fact table from the old table and adding the records (appending) if your just testing I think you can skip it and just create sample data.

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.