Fruit salad database ...

[Jamie Risk]

I have a requirement of Access that can be illustrated with the following
(in my best attempt at DDL):

CREATE TABLE China (
Pattern_ID INTEGER NOT NULL PRIMARY,
Pattern VARCHAR(100) NOT NULL
)

CREATE TABLE Fruit (
Fruit_ID INTEGER NOT NULL PRIMARY,
Fruit VARCHAR(100) NOT NULL
)

CREATE TABLE Salad (
Salad_ID INTEGER NOT NULL PRIMARY,
Pattern_ID INTEGER NOT NULL,
Fruit_ID1 INTEGER NOT NULL,
Fruit_ID2 INTEGER NOT NULL,
Fruit_ID3 INTEGER NOT NULL
);

I'd like to create a form to fill out the Salad table. The constraints are
this; a patern must be chosen, between one and three fruit must be chosen
from the Fruit table, and if a particular fruit is chosen, the option is
removed from subsequent fruit selections.

Currently I have a Fruit record 'None'. The solution is inelegant and makes
it difficult to implement the third of the three constraints above.

From the above I'd then like to construct reports.
* all salad records organized by china patterns and what fruit are in them
* all salad records organized by fruit, and what patterns and other fruit
are with them

If I insist on having three fruits per plate, I can see how to do this. The
drawbacks are that when I eventually am asked to add a fourth plate, there
is a lot of maintenance to the database required, also, some people just
don't want more than one fruit!

Suggestions anyone?

- Jamie



p.s. Also, for the colonically adventerous, a less important goal would be
to have as many fruit options in the Salad form/table as fruit listed in the
Fruit table. This would be a dynamic requirement of the Salad form and
table. I can see this would be possible in a pure SQL environment, but I'd
like to know if it's possible in Access.

 

[Bill Edwards]

First normalize the Salad table:
Salad_Id
Pattern_Id
Fruit_Id

You would have a compound primary key consisting of Salad_Id, Fruit_Id.
This will prevent you from adding the same fruit twice to a salad.

When the user attempts to add a record to the Salad table you could:
*** AIR CODE ***
(1) Run a sql query "SELECT count(Salad_Id) AS FruitCount FROM Salad
WHERE Salad_Id = CurrentSalid_Id
If the query returns 2 or less you would allow the addition of records.
Three or more you would allow the deletion or editing of records. You could
put this query in a PUBLIC sub that returns either TRUE or FALSE, so if you
eventually decide to allow more than 3 fruits per salad it would be an easy
change to make.

(2) In the Got Focus or Enter event of the Fruit_Id combo box (assuming
it is a combo box) you could requery the recordsource for the combo box to
see what fruits are already in the salad and exclude them from the combo box
RecordSource.

strSQL = "SELECT FruitId, Fruit FROM Fruit WHERE FruitId NOT IN (SELECT
FruitId FROM Salad WHERE Salad_Id = CurrentSalad_Id)"
me.cboFruitList.RowSource = strSQL
Me.cboFruitList.Requery

 

[Jamie Risk]

Thanks Bill!!! I'm not sure I understand exactly what you've written but
this looks like it could be the solution I've been seeking! Unfortunately my
limited experience with SQL and Access leaves me to interpret a whole lot of
interesting looking stuff.

I've got a gazzillion questions here, I hope it shows anyone reading this
Bill's got me thinking.

First normalize the Salad table:
Salad_Id
Pattern_Id
Fruit_Id

My physics background tells me that all the enteries in the Salad table are
to be orthogonal (unique) if they're 'normalized'. If that is so, am I to
create a Salad table (as you wrote "Air Code" - not tested?);

CREATE TABLE Salad (
Salad_ID INTEGER NOT NULL,
Pattern_ID INTEGER NOT NULL,
Fruit_ID INTEGER NOT NULL
);

You would have a compound primary key consisting of Salad_Id, Fruit_Id.
This will prevent you from adding the same fruit twice to a salad.

How do I make a compound key (in Access particularly)?

*wait a minute*

Is the compound key mean I've got one, two or three records in the Salad
table for each [Salad_ID], and that [Salad_ID] doesn't uniquely describe a
record in Salad, but an actual salad with one, two or three fruits
(requiring one, two or three records in Salad with the same Salad_ID)?
Could I be pointed a description of how a compound key works, or how it
applies as intended here?

This looks really promising, thanks Bill...

When the user attempts to add a record to the Salad table you could:
*** AIR CODE ***
(1) Run a sql query "SELECT count(Salad_Id) AS FruitCount FROM Salad
WHERE Salad_Id = CurrentSalid_Id

In the vernacular, can I interpret the above to mean, "I haven't tested this
but, set FruitCount to the number of (Salad_ID) that matches the current
Salid_ID being entered". Seems reasonable, but if so, would I put the query
(and subsequent test) in the form marked by a new record entry event? Is
there an event that marks the making a new record? If the event were illegal
(too many fruits), I'm assuming I could do something akin to posting a short
explanation and not allowing the record to further modified without altering
the Salad_ID?

If the query returns 2 or less you would allow the addition of records.
Three or more you would allow the deletion or editing of records. You
could put this query in a PUBLIC sub that returns either TRUE or FALSE, so
if you eventually decide to allow more than 3 fruits per salad it would be
an easy change to make.

Even better, but where do I insert "PUBLIC" subroutines so that they remain
in scope for this yet to be imlemented form?

(2) In the Got Focus or Enter event of the Fruit_Id combo box (assuming
it is a combo box) you could requery the recordsource for the combo box to
see what fruits are already in the salad and exclude them from the combo
box RecordSource.

strSQL = "SELECT FruitId, Fruit FROM Fruit WHERE FruitId NOT IN (SELECT
FruitId FROM Salad WHERE Salad_Id = CurrentSalad_Id)"
me.cboFruitList.RowSource = strSQL
Me.cboFruitList.Requery

This may be obvious when I get this far, but right now it's at the hairy
edge of my understanding.

I will most likely have questions about form organization, but that may be
clearer soon anyway.

Again, thanks for the input Bill. I look forward to another enlightening
response (from you or elsewise), until then, I've got some threads I can
pull on.

- Jamie

 

[Jamie Risk]

I found a post on normalization, hopefully it will explain compound key
formation as well.

http://support.microsoft.com/kb/209534

 

[Jamie Risk]

Thanks Bill!!! I'm not sure I understand exactly what you've written but
this looks like it could be the solution I've been seeking! Unfortunately
my limited experience with SQL and Access leaves me to interpret a whole
lot of interesting looking stuff.

I've got a gazzillion questions here, I hope it shows anyone reading this
Bill's got me thinking.



My physics background tells me that all the enteries in the Salad table
are to be orthogonal (unique) if they're 'normalized'. If that is so, am
I to create a Salad table (as you wrote "Air Code" - not tested?);

CREATE TABLE Salad (
Salad_ID INTEGER NOT NULL,
Pattern_ID INTEGER NOT NULL,
Fruit_ID INTEGER NOT NULL
);


How do I make a compound key (in Access particularly)?

Okay, I've got a compound key going
(http://support.microsoft.com/default.aspx?scid=kb;en-us;304266)
The rest I'm trying to figure out, although I'd be grateful to anyone who
can take me through my assumptions and questions below more quickly than I
can on my own..

*wait a minute*

Is the compound key mean I've got one, two or three records in the Salad
table for each [Salad_ID], and that [Salad_ID] doesn't uniquely describe a
record in Salad, but an actual salad with one, two or three fruits
(requiring one, two or three records in Salad with the same Salad_ID)?
Could I be pointed a description of how a compound key works, or how it
applies as intended here?

This looks really promising, thanks Bill...

When the user attempts to add a record to the Salad table you could:
*** AIR CODE ***
(1) Run a sql query "SELECT count(Salad_Id) AS FruitCount FROM Salad
WHERE Salad_Id = CurrentSalid_Id

In the vernacular, can I interpret the above to mean, "I haven't tested
this but, set FruitCount to the number of (Salad_ID) that matches the
current Salid_ID being entered". Seems reasonable, but if so, would I put
the query (and subsequent test) in the form marked by a new record entry
event? Is there an event that marks the making a new record? If the event
were illegal (too many fruits), I'm assuming I could do something akin to
posting a short explanation and not allowing the record to further
modified without altering the Salad_ID?

If the query returns 2 or less you would allow the addition of records.
Three or more you would allow the deletion or editing of records. You
could put this query in a PUBLIC sub that returns either TRUE or FALSE,
so if you eventually decide to allow more than 3 fruits per salad it
would be an easy change to make.

Even better, but where do I insert "PUBLIC" subroutines so that they
remain in scope for this yet to be imlemented form?

(2) In the Got Focus or Enter event of the Fruit_Id combo box
(assuming it is a combo box) you could requery the recordsource for the
combo box to see what fruits are already in the salad and exclude them
from the combo box RecordSource.

strSQL = "SELECT FruitId, Fruit FROM Fruit WHERE FruitId NOT IN (SELECT
FruitId FROM Salad WHERE Salad_Id = CurrentSalad_Id)"
me.cboFruitList.RowSource = strSQL
Me.cboFruitList.Requery

This may be obvious when I get this far, but right now it's at the hairy
edge of my understanding.

I will most likely have questions about form organization, but that may be
clearer soon anyway.

Again, thanks for the input Bill. I look forward to another enlightening
response (from you or elsewise), until then, I've got some threads I can
pull on.

- Jamie

 

[Bill Edwards]

Create four tables
tblChina
Chinakey Autonumber Primary Key
ChinaPattern Text(50) Description of china pattern
- also unique index on china pattern

tblSalad
SaladKey Autonumber Primary Key
ChinaKey Long Links back to tblChina
SaladDescription Text(50) Description of salad
- unique index on ChinaKey
- unique index on SaladDescription

tblFruit
FruitKey Autonumber Primary Key
FruitName Text(50) Name of fruit
- unique index on FruitName

tblSaladFruit
SaladFruitKey Autonumber Primary Key (or you could use the compound
index on SaladKey, FruitKey as the primary key and eliminate this field
altogether)
SaladKey Long Links back to tblSalad
FruitKey Long Links back to tblFruit

- Compound unique index on SaladKey, FruitKey will prevent you adding the
same fruit twice to the same salad

Create a form (frmSalad) based on tblSalad with the following fields:
SaladKey
ChinaKey ->control cboChinaKey a combo box whose rowsource is "SELECT
ChinaKey, ChinaPattern FROM tblChina ORDER BY ChinaPattern"; Number of
columns is 2; columns widths is 0;2 so the key value is not displayed.

Create a second form (frmSaladFruit) based on tblSaladFruit; Default view is
continuous forms
Include all three fields
FruitKey -> control cboFruitKey is a combo box whose rowsource is "SELECT
FruitKey, FruitName FROM tblFruit ORDER by FruitName"

In frmSaladFruit's current event:
Private Sub Form_Current()
Dim strSQL As String
Dim db As DAO.Database
Dim rst As DAO.Recordset
If IsNull(Me.SaladKey) Then
Else
strSQL = "SELECT count(SaladFruitKey) AS FruitCount FROM tblSaladFruit WHERE
SaladKey = " & Me.SaladKey
Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL)
If Nz(rst.Fields("FruitCount"), 0) > 2 Then
Me.AllowAdditions = False
Else
Me.AllowAdditions = True
End If

rst.Close
Set rst = Nothing
db.Close
Set db = Nothing
End If

End Sub

Take frmSaladFruit and place it on frmSalad. In the subforms properties set
the following:
LinkChildField=SaladKey
LinkMasterField =SaladKey


All this assumes a Jet backend and not a sql server backend. It has been
tested with Access/Jet but I may have missed something in this post.

Thanks Bill!!! I'm not sure I understand exactly what you've written but
this looks like it could be the solution I've been seeking! Unfortunately
my limited experience with SQL and Access leaves me to interpret a whole
lot of interesting looking stuff.

I've got a gazzillion questions here, I hope it shows anyone reading this
Bill's got me thinking.

First normalize the Salad table:
Salad_Id
Pattern_Id
Fruit_Id

My physics background tells me that all the enteries in the Salad table
are to be orthogonal (unique) if they're 'normalized'. If that is so, am
I to create a Salad table (as you wrote "Air Code" - not tested?);

CREATE TABLE Salad (
Salad_ID INTEGER NOT NULL,
Pattern_ID INTEGER NOT NULL,
Fruit_ID INTEGER NOT NULL
);

You would have a compound primary key consisting of Salad_Id, Fruit_Id.
This will prevent you from adding the same fruit twice to a salad.

How do I make a compound key (in Access particularly)?

*wait a minute*

Is the compound key mean I've got one, two or three records in the Salad
table for each [Salad_ID], and that [Salad_ID] doesn't uniquely describe a
record in Salad, but an actual salad with one, two or three fruits
(requiring one, two or three records in Salad with the same Salad_ID)?
Could I be pointed a description of how a compound key works, or how it
applies as intended here?

This looks really promising, thanks Bill...

When the user attempts to add a record to the Salad table you could:
*** AIR CODE ***
(1) Run a sql query "SELECT count(Salad_Id) AS FruitCount FROM Salad
WHERE Salad_Id = CurrentSalid_Id

In the vernacular, can I interpret the above to mean, "I haven't tested
this but, set FruitCount to the number of (Salad_ID) that matches the
current Salid_ID being entered". Seems reasonable, but if so, would I put
the query (and subsequent test) in the form marked by a new record entry
event? Is there an event that marks the making a new record? If the event
were illegal (too many fruits), I'm assuming I could do something akin to
posting a short explanation and not allowing the record to further
modified without altering the Salad_ID?

If the query returns 2 or less you would allow the addition of records.
Three or more you would allow the deletion or editing of records. You
could put this query in a PUBLIC sub that returns either TRUE or FALSE,
so if you eventually decide to allow more than 3 fruits per salad it
would be an easy change to make.

Even better, but where do I insert "PUBLIC" subroutines so that they
remain in scope for this yet to be imlemented form?

(2) In the Got Focus or Enter event of the Fruit_Id combo box
(assuming it is a combo box) you could requery the recordsource for the
combo box to see what fruits are already in the salad and exclude them
from the combo box RecordSource.

strSQL = "SELECT FruitId, Fruit FROM Fruit WHERE FruitId NOT IN (SELECT
FruitId FROM Salad WHERE Salad_Id = CurrentSalad_Id)"
me.cboFruitList.RowSource = strSQL
Me.cboFruitList.Requery

This may be obvious when I get this far, but right now it's at the hairy
edge of my understanding.

I will most likely have questions about form organization, but that may be
clearer soon anyway.

Again, thanks for the input Bill. I look forward to another enlightening
response (from you or elsewise), until then, I've got some threads I can
pull on.

- Jamie

 

[Jamie Risk]

Thank you and wow.

This is huge concept that I've been wanting to understand off and on for a
couple of years now. The specifity of your reply gives me a lot of material
to study.

Until I penetrate the code, I can just say Thanks for now.

 

[Bill Edwards]

No problem.

Noticed a mistake in:

tblSalad
SaladKey Autonumber Primary Key
ChinaKey Long Links back to tblChina
SaladDescription Text(50) Description of salad
- unique index on ChinaKey ******* this should just be index on ChinaKey
(not a unique index)
- unique index on SaladDescription

 

[Jamie Risk]

When I try to enter the code as is (all reference identifiers have the same
name, so I cut and pasted),
I get an error when I try to open the form for data entry.
***QUOTE***
Microsoft Access can't find the macro 'Private Sub Form_Current()
Dim strSQL As String
Dim db As DAO.'

The macro (or its macro group doesn't exist, or th emacro is new but
hasn't been saved.
Note that when you enter the macrogroupname.macroname syntax in an
argument, you must specify the name the macro's marco group was last saved
under.

[OK]
****end quote****

I did find some mention about errant spaces when I searched for this error
at the microsoft site. But I think it might have more to do with the DAO
data types.

Help!

 

[Jamie Risk]

I saw a reference too:
http://support.microsoft.com/default.aspx?scid=kb;en-us;225962 which didn't
help.

Suggestions?

When I try to enter the code as is (all reference identifiers have the
same name, so I cut and pasted),
I get an error when I try to open the form for data entry.
***QUOTE***
Microsoft Access can't find the macro 'Private Sub Form_Current()
Dim strSQL As String
Dim db As DAO.'

The macro (or its macro group doesn't exist, or th emacro is new but
hasn't been saved.
Note that when you enter the macrogroupname.macroname syntax in an
argument, you must specify the name the macro's marco group was last saved
under.

[OK]
****end quote****

I did find some mention about errant spaces when I searched for this error
at the microsoft site. But I think it might have more to do with the DAO
data types.

Help!

In frmSaladFruit's current event:

Private Sub Form_Current()
Dim strSQL As String
Dim db As DAO.Database
Dim rst As DAO.Recordset
If IsNull(Me.SaladKey) Then
Else
strSQL = "SELECT count(SaladFruitKey) AS FruitCount FROM tblSaladFruit
WHERE SaladKey = " & Me.SaladKey
Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL)
If Nz(rst.Fields("FruitCount"), 0) > 2 Then
Me.AllowAdditions = False
Else
Me.AllowAdditions = True
End If

rst.Close
Set rst = Nothing
db.Close
Set db = Nothing
End If

End Sub

 

[Dirk Goldgar]

I saw a reference too:
http://support.microsoft.com/default.aspx?scid=kb;en-us;225962 which
didn't help.

Suggestions?

When I try to enter the code as is (all reference identifiers have
the same name, so I cut and pasted),
I get an error when I try to open the form for data entry.
***QUOTE***
Microsoft Access can't find the macro 'Private Sub Form_Current()
Dim strSQL As String
Dim db As DAO.'

The macro (or its macro group doesn't exist, or th emacro is new but
hasn't been saved.
Note that when you enter the macrogroupname.macroname syntax in an
argument, you must specify the name the macro's marco group was last
saved under.

[OK]
****end quote****

I did find some mention about errant spaces when I searched for this
error at the microsoft site. But I think it might have more to do
with the DAO data types.

Help!

That sounds like you pasted the VBA code directly into the On Current
property line on the form's property sheet. That's not where it goes.
Clear that line out altogether, then open the form's code module (by
clicking the "Code" toolbar button or the View -> Code menu items) and
paste the code into the module. Note that doing so should automatically
set the On Current property line on the form's property sheet to "[Event
Procedure]".

 

[Jamie Risk]

That sounds like you pasted the VBA code directly into the On Current
property line on the form's property sheet. That's not where it goes.
Clear that line out altogether, then open the form's code module (by
clicking the "Code" toolbar button or the View -> Code menu items) and
paste the code into the module. Note that doing so should automatically
set the On Current property line on the form's property sheet to "[Event
Procedure]".

You're right, that is where I put the code. Didn't know any better.

Thanks,
-Jamie