Check Mark = Value

  • Thread starter Thread starter Jorge
  • Start date Start date
J

Jorge

Good day!

I have a question concerning Check Mark...

table:Receive
RFname
RLname
RStAddr
ServiceCode
ServiceCode1
ServiceCode2

The ServiceCode Fields are Check Box fields and on the form I have displayed
as such.
If a particular box is "checked" it will represent the value in the SCodes
table below.

table:SCodes
ServiceCode
ServiceDesc
ServicePrice

Then I can base report or query based on the results.

How do I have this happen?

ie..
If a check is placed in ServiceCode field it represents a "1"
It then looks in SCodes finds "1" which represents SeviceDesc and a certain
ServicePrice.
If a check is placed in ServiceCode1 field it represent a "2"
It then looks in SCodes finds "2" which represents SeviceDesc and a certain
ServicePrice.
 
It is not clear from your description whether or not the
user should check only 1 of the 3 check boxes (in which
case you should represent this as an option group or as a
text box) i.e. is it valid for ServiceCode and ServiceCode1
to be both checked and, if so, what value to this equate to
on the table Scodes

Gerald Stanley MCSD
 
Gerald Stanley said:
It is not clear from your description whether or not the
user should check only 1 of the 3 check boxes (in which
case you should represent this as an option group or as a
text box) i.e. is it valid for ServiceCode and ServiceCode1
to be both checked and, if so, what value to this equate to
on the table Scodes
Click to expand...

Scodes has items 1 thru 10, each equals a different cost.
It is valid for a user to select ALLl or just ONE ServiceCode.
By the selections made (check marks) the reports produced will take that
info tally up the cost for a particular user.
 
I would recommend that you change the structure of table
Receive to remove the multiple Service Code columns and add
a new table to show the many to many relationship between
the Receive and Scodes tables. If your Receive table has
not got a unique tableId then I would recommend that you
add one. That would mean that the new table would have two
columns, the tableId of the Receive table and the Service
Code of the SCodes table.

Hope That Helps
Gerald Stanley MCSD
 
Hmmm?

How then, will I be able to allow multiple service codes be entered with the
receive table?

table:SvC
ServiceCode(pkey)
ServiceDesc

table:SCodes
ServiceCode (pkey)
SvcType
ServicePrice

table:Receive
Rec# (pkey)
RFname
RLname
RStAddr
ServiceCode

I'm really trying - really...
I'm shooting blanks..

Am I close?

:) Jorge
 
The structure of your three tables should be as follows

table:Receive
Rec# (pkey)
RFname
RLname
RStAddr

table:SCodes
ServiceCode (pkey)
ServiceDesc
SvcType
ServicePrice

table:ReceiveSvc
Rec# (foreign key to Receive)
ServiceCode (foreign key to SCodes)

You should then design a form for table ReceiveSvc then
include it as a subform in your form for Receive. The link
field between the form and the subform is Rec#.

Hope That Helps
Gerald Stanley MCSD
 
Thank-You I will definitely try this scenario!
Now I just need to figure out what a "Foreign" key is... :)

Jorge
 
I have just read on foreign key and I think I got it...
The pkey in the tables, should I set them up as autonumber? does it matter?
 
Now setting up a foreign key.. when I create the table it wants to create a
primary key...
Do I just NOT create one? and link the table in the relationship to the
proper tables?
 
Jorge

For the table ReceiveSvc, you should set up the Primary Key
as the combination of both fields. You can do this in the
table design view by selecting the Index icon, typing in
Primary as the name of the Index, setting its Primary
property to Yes and selecting both columns.

Hope That Helps
Gerald Stanley MCSD
 
I'm really NOT dumb... but I feel like it...

When I click the index icon I see..

Indexes: ReceiveSvc

Index name Field Name Sort order
ReceiveSvcRec# Rec# ascending
ServiceCode ServiceCode ascending

Index Properties
Primary - No
Unique - No
Ignore Nulls - No

Selecting both columns?
 
Jorge

There's nothing to feel dumb about - Access has added these
two indexes in automatically for you. You will be able to add
a new index youself by
1- typing in the next available row (which should be the third
row), the name of the new Index e.g. PrimaryIndex
2- in the middle column of that row, selecting Rec# from the
dropdown list (if this is the name of the primary key from the
Receive table)
3- in the third column of that row, selecting Ascending from
the drop down list
4-in the next available row (which should be the fourth row),
leaving the first column blank
5- in the middle column of that row, selecting ServiceCode
from the dropdown list (if this is the name of the primary key
from the sCodes table)
6- in the third column of that row, selecting Ascending
7- while still on that row, setting the index properties to
Primary - Yes
Unique - Yes
Ignore Nulls - No

You index dialog box should then look like

Index name Field Name Sort order
ReceiveSvcRec# Rec# ascending
ServiceCode ServiceCode ascending
PrimaryIndex Rec# ascending
ServiceCode ascending

When you cursor is on either of the rows for PrimaryIndex,
the property box should lokk like

Index Properties
Primary - Yes
Unique - Yes
Ignore Nulls - No

Hope That Helps
Gerald Stanley MCSD
 
Oki-Doki... this setup worked - I see that it placed key mark next to both
fields within...
Can you tell if anything in the relationships should be set?

I have One - Many at each join.

Scodes ---> ReceiveSvc
Receive---> ReceiveSvc

Is this right?

Mucho !
 
OK all is setup.
I have created a form for ReceiveSvc.
I then created a form for Receive and placed the form I created for
ReceiveSrv as a subForm.
Should I have created a lookup into Scodes within the ReceiveSrv table?
So that when you go to the subform it allows the user to pick what Service
was performed?
It is possible that several services will be performed.

Thank-you your to kind!
 
Jorge

The answer depends upon which sort of form you have
created for ReceiveSvc.

If it is in datasheet view, then the lookup is a good idea.

If it is in form view, then a combo box may be the best way to
go.

Either way, you should be able to enter multiple service
against a single entry in the Receive table.

The main point is that, now that you have the tables designed
in this 'normalised' manner, you should find that you are able
to explore these various input possibilities much more easily
than when you had the previous table designs.

Hope That Helps
Gerald Stanley MCSD
 
Yes it does help Gerald!
Thank-You so much!

Bet you thought I was done asking questions? :)
Will this type design work in such way as to not duplicate services?
We don't want " Joe Blow" to accidentally select the same service twice.
The Scodes is a (table) that contains 14 records and no more.
I tried to limit the number of records in this table and it says its to
complex?
 
Hi Gerald...

I have created a form based on Receive and the subform for ReceivSrv like
you suggested.
When you go to the subform and choose a service code it looks to the Scodes
table and you are able to vies all four fields.
What I'm attempting to do is have the ServDesc and SrvPrice show as well...
can't change it but the user can see it.
Not sure what I'm suppose to do. I've tried additional lookups and queries
but it just don't happen!
Simply says NAME?

Ahhhhhhhh(scream)
J
 
Hi Jorge

The design of ReceiveSrv table is such as to prevent the
duplication of services. This is because the combination of
the two foreign keys from the other tables to be the table's
primary key means that the combination must be unique.

Regards
Gerald
 
Jorge

Try something along the following lines:

- Put the ReceiveSrv table into Design View, highlight the
column serviceCode and click on the LookUp tab.
- For Display Control, select ComboBox
- For RowSourceType, select Table/Query
- For RowSource, type in "SELECT serviceCode, svcType,
servicePrice FROM Scodes;
- For Bound Column , type in 1
- For Column Count, type in 3
- For LimitToList, select Yes.

Save the design and try the form again. If it doesn't look like
you want it to, let me know what the default view for the sub
form is.

Regards
Gerald
 
Back
Top