Realtionships and auto creating records.

[gtslabs]

I need some assistance setting up my project. I currently use Excel to
do most of this but want to try Access.

I need to log in samples that are in sets. Currently every sample I
have has a serial number and is 1 row in an excel worksheet. Each set
has up to 15 samples.

Now for Access - I created 2 tables.
Each set has its own information in a table called "Log" which also
includes the number of samples in that set.

I have a separate table "Samples" that lists all the samples received.

I want to use the first Serial Number from each set to be the Set
number. So I assume I need a "Set Number" field in each table to
link for my relationship. But what should be primary key?

I set up my relationship but it is bringing in all the data from
"Samples" into my form and subform when I select the "Set Number" in
the "Log Form". So I a am having problems getting it to only see the
data from "Samples" that equal the Set. I set the relationship to the
"Set Number" in both tables but is is not working.

I am also having trouble understanding how to set these autonumber and
primary keys up. I really want to have "x" samples created it my
"Samples" table when I create a new record in "Log" that has "x"
number in the Sample Count field. I dont know if this can be done so
I was thinking of having a maximum number of Samples per set of 15 and
each new Set number be a factor of 15.

Can this easily be set up?

 

[John W. Vinson]

I need some assistance setting up my project. I currently use Excel to
do most of this but want to try Access.

Just be aware that they are VERY different, and require a different mindset
and approach. Being an Excel expert can make learning Access harder, not
easier, as you may have to "unlearn" the way things work in Excel.

I need to log in samples that are in sets. Currently every sample I
have has a serial number and is 1 row in an excel worksheet. Each set
has up to 15 samples.

Now for Access - I created 2 tables.
Each set has its own information in a table called "Log" which also
includes the number of samples in that set.

Well... you do want a table of sets, but it should almost certainly NOT
include the number of samples in any table, period. That value can and should
be calculated on the fly using a query.

I have a separate table "Samples" that lists all the samples received.

I want to use the first Serial Number from each set to be the Set
number. So I assume I need a "Set Number" field in each table to
link for my relationship. But what should be primary key?

The PK should be some field in the Sets table which is unique (only one set
can have that value), stable (not likely to change), and ideally short (a
Number is fine). I would not think it a good idea to use a sample number; you
might want to just use an Autonumber in the sets table. In any case, the
samples table should have a unique ID (your Serial Number sounds good) and, in
a separate field, the Set Number to which that sample begins. This field is
called a "foreign key".

I set up my relationship but it is bringing in all the data from
"Samples" into my form and subform when I select the "Set Number" in
the "Log Form". So I a am having problems getting it to only see the
data from "Samples" that equal the Set. I set the relationship to the
"Set Number" in both tables but is is not working.

Please explain a bit more about the actual fields in your tables, and how they
are related. The Sets table should have only one row per set, and there will
be multple rows in the Sample table with that set number. Is that the case?

I am also having trouble understanding how to set these autonumber and
primary keys up.

If you already have a "natural key" - a unique, stable and short identifier -
just use it as the primary key. Autonumbers are handy but *not* essential.

I really want to have "x" samples created it my
"Samples" table when I create a new record in "Log" that has "x"
number in the Sample Count field.

I dont know if this can be done so
I was thinking of having a maximum number of Samples per set of 15 and
each new Set number be a factor of 15.

And if someday you have twenty samples in a set...? Breaks your model
entirely!!!

Can this easily be set up?

I'd suggest instead just using a Form based on the Sets table, with a Subform
based on the Samples table. Use the SetID as the Master/Child Link Field;
adding new data about samples in the subform will cause that set ID to be
automatically inherited and filled in.

 

[gtslabs]

I was able to get the relationship to work.

I now have a "Log" table with the Set# as the PK and an autonumber.
My "Samples" table has a Set# as the FK.
But I need a way to get the sample Serial number to auto increment for
each set.
For instance I would like

Set# Serial#
10 10-1 or 10.001
10 10-2 or 10.002
10 10-3 or 10.003
11 11-1 or 11.001
11 11-2 or 11.001
etc..
We routinely add these so it would be very tedious to do so manually.
that is why I wanted these serial numbers to be autogenerated when
ever we type in any other parameter in the the samples subform
datasheet (ie when a new row or record is created) or from the total
samples entered in on the main form (ideal).

 

[John W. Vinson]

I was able to get the relationship to work.

I now have a "Log" table with the Set# as the PK and an autonumber.

Why the autonumber? It plays NO useful role if you already have a unique Set#.

Sidenote: I'd avoid using # (or blank, or any other special characters) in
fieldnames. # is a date delimiter and Access can get confused.

My "Samples" table has a Set# as the FK.

Excellent. Again... despite Microsoft's blandishments to the contrary, you
don't need the autonumber.

But I need a way to get the sample Serial number to auto increment for
each set.
For instance I would like

Set# Serial#
10 10-1 or 10.001
10 10-2 or 10.002
10 10-3 or 10.003
11 11-1 or 11.001
11 11-2 or 11.001
etc..

You can use two fields - an Integer or Long Integer SetNo (Set#) and likewise
for the Serial#. You would make these two fields the joint primary key for the
table (with the table in design view, ctrl-click both fields and click the Key
icon). They can be concatenated for display purposes if you wish, but you
would NOT want to include the Set# as part of the Serial#.

We routinely add these so it would be very tedious to do so manually.
that is why I wanted these serial numbers to be autogenerated when
ever we type in any other parameter in the the samples subform
datasheet (ie when a new row or record is created) or from the total
samples entered in on the main form (ideal).

Easily done with a single line of VBA code! In the Subform's BeforeInsert
event put

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!SerialNo = NZ(DMax("[SerialNo]", "Samples", "[SetNo] = " & Me![SetNo])) + 1
End Sub

This will look up the largest existing serialno for this setno; the NZ()
function will return 0 if there is no existing serialno. The +1 will increment
whatever was found and assign that as the serialno for the new record.