database design, forms and combo boxes

[Snjezy]

Hello,
I'm still learning about Access 2013 and any help I can get is much appreciated. I'm trying to establish a database for the mineral collection.
I have a table (mineral collection) with many fields and a nice form for editing records and adding new ones. Every specimen is a separate record.
I'd like to improve a field called Mineral name in my form in a certain way.
Since a single specimen may have many minerals I'd like to be able to put every mineral name (every mineral found on a single specimen) via separate combo box in the specific order (not alphabetically, but in the order of inputing or in the order preferred by the collector) and yet to be displayed and managed under the same field: Mineral name. Can this be done?
The combo box is related to a different table of suggested mineral names. I tried multivalued option within a single combo box but it keeps displaying minerals alphabetically in the field.
Many thanks,
Zana

 

[CJ_London]

I suspect multivalue field is not the way to go, presumably a mineral is a mineral is a mineral - i.e. you are only choosing one. If so that is not what multi value fields are for.

suggest your other table will need an additional 'sort' column so the rowsource can use it to define the order.

If you have different users, all wanting different orders, you will need a second table to store the sort order to include a field to identify the user and link that to the mineral PK.

 

[Snjezy]

Thank you for the quick reply,
User is inputing mineral names one by one meaning if there is mostly quartz on the sample then it is the first one to be selected and displayed in the field (it is the main or dominant mineral on the sample). Next mineral selected should be displayed after the first one, regardless of its first letter and preferrably comma separated. I cannot define order in advance since I can't predict which minerals will be identified on each new sample. They all must belong to the same sample/record of course and to the same field: Mineral name so later I can make reports including mentioned field that should look like this e.g. Mineral name: quartz, calcite, pyrite...
I have it all in my mind but can't put it to work... .
Thank you in advance,
Zana

 

[Snjezy]

Here's a simple drawing of what I have in mind...

Best regards,

Zana

 

[CJ_London]

the only way you can be sure to get the correct order of input is to use a timestamp field (simply a datetime field with the default set to now()). Users never need to see it but it can be used for sorting.

If you are basing the order based on dominance, then you need to have a 'dominance' field and order by that instead.

You description seems to be confusing data input with presentation which is an Excel trait. Although a multivalue field will show what has been selected, you have no control of the order of presentation.

I would expect you to have 3 tables

tblMinerals
MinerialPK autonumber
MineralName text
...
...

tblSamples
SamplePK autonumber
SampleDesc text
SampleCollected Datetime
SampleLocation text
...
...

tblSampleMinerals
SMPK autonumber
SampleFK long - link to tblSamples
MineralFK long - link to tblMinerals
Dominance percentage
TimeStamp datetime


tblSampleMinerals is the equivalent of what is created in a multivalue field, but without the fields you need to be able to sort, which is why this wont work for you.

You would have a main form with tblSamples as recordsource

You would have on this a subform with a controlsource of another form based on tblSampleMaterials with a recordsource of

"SELECT * FROM tblSampleMinerals ORDER BY Dominance Desc"

or

"SELECT * FROM tblSampleMinerals ORDER BY Timestamp Desc"

The subform control linkmaster and linkchild properties would be SamplePK and SampleFK respectively

In the subform itself the control for MineralFK would be a combobox with a rowsource of

"SELECT * FROM tblMinerals ORDER BY MineralName"

To display data horizontally e.g. quartz; iron; you would use something like the concatrelated function (google to find out more) or perhaps a crosstab query.

There are other ways of selecting and presenting data, but they would all be based on the above table structure and require a reasonable skill level to create. So before exploring them, see if the above works for you and clarify what doesn't

 

[Snjezy]

Thank you for the reply,
I'm not an advanced user so I'm now a bit lost. I think timestamp field might help since the order of minerals displayed in the field depends basically on the time of inputing the data (the user usually puts the main mineral first and then the others in the order of it's own choosing) but I didn't understand how to make it happen. Should I make another field (datetime field) which is related to the Mineral name field somehow? And how? I'm sorry, you lost me...
Can this be acomplished using many comboboxes within a single field (Min name field) instead (like in the drawing?) Each combobox drop down list will have the same rowsource (tbl Minerals)?
BR
Zana

 

[CJ_London]

It is not difficult, I've provided the table structure, and use the concatrelated function for presentation.

You seem to be asking how to create a table.

 

[CJ_London]

I've put this simple example together for you, see if it helps

 

[Snjezy]

It looks very promising, I'll have to dig into it more thoroughly to figure out how it works with other objects in my database and of course how you did all that I don't know how to work with modules but it'll be fun.
You helped a lot, thank you,
kind regards,

Zana