New to MS Access

  • Thread starter Thread starter Continental Translations
  • Start date Start date
C

Continental Translations

I am using MS Access 2000.

I've never used it before and so ain't got a clue, however if the below is
possible, i'll get myself a book from the library to assist me on my way.

Basically, I am wanting to create a searchable database. I will have a
different entry for around 100 people. Each entry will contain name, contact
details, three specific talents (ie. singing, dancing, painting). If they
only have 2, then the last field will be left blank. Now on top of this,
there will be a few prices, ie. price per hour etc...

Now once I have all my entries in my database, is there a way of me typing
in some search thingy-ma-bob "singing and dancing" and it will then come
back with a list of people who fall into this category?

Thanks all and sorry if this sounds a dumb question!
 
This is what Access is all about. You're talking about a
basic database layout to accomplish what you want to do.

From what I understand of your question you're going to
have something like this:

Tables:

tblContacts
-----------
ContactID (Primary Key)
LastName
FirstName
Address
Phone
Etc....

tblTalents
----------
TalentID (Primary Key)
TalentDescription

tblContactTalents
-----------------
ContactID
TalentID

(ContactID and TalentID combined would be the primary key)

With this type of layout you aren't limited to any number
of contacts, any number of talents or assigning any
number of talents to each contact.

Once your tables are designed, create relationships
between the tables using "Tools"..."Relationships".
You're linking fields together from table to table that
are the same such as ContactID from tblContacts and
ContactID from tblContactTalents.

Create forms for at least tblContacts and
tblContactTalents. Use the ContactTalents form as a
subform on the Contacts form.

The result is that each time you go to the next contact
record, all of their corresponding talents will appear in
the subform.

The database is completely searchable and there are
several ways to search for multiple criteria such
as "dancing and singing".

I'm not sure what you meant about "on top of this there
will be a few prices"...so I don't know if a separate
table would be needed for that or if the data would be
stored in the table already described. If you're going to
have multiple intances of the data for the same contact
then you'll need another table to store that information.
Just make sure that you include ContactID to link the
table to tblContacts.

Good luck with your database. I'm sure that someone here
can help you if you need it.
 
Bob,

You're a star. As it is for freelance employers, I need a price for when I
use them per hour. So for example, someone who is a singer who charges £10
per hour. Would I need to put this price in a seperate table? I would if I
want to be able to search by price wouldnt i?

Thanks again
 
Add another field to Bob's tblContactTalents and call it HourlyRate. You then
have an hourly rate for each individual and each talent he or she has. You could
then for example create a query based on this table joined to each of the other
tables and put a criteria in the alent field for the talent you want and a
criteria such as Between £10 And £15 in the HourlyRate Field to get the talent
at the price you want to pay.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com
 
PC Datasheet said:
Add another field to Bob's tblContactTalents and call
it HourlyRate. You then have an hourly rate for each
individual and each talent he or she has. You could
then for example create a query based on this table
joined to each of the other tables and put a criteria
in the alent field for the talent you want and a
criteria such as Between £10 And £15 in the
HourlyRate Field to get the talent at the price you
want to pay.
Click to expand...

What about the query for a contact with singing-AND-dancing? That won't be
a simple sql structure. (Or will it?)

- Peter
 
<<
I am using MS Access 2000.

I've never used it before and so ain't got a clue, however if the below is
possible, i'll get myself a book from the library to assist me on my way.

Basically, I am wanting to create a searchable database. I will have a
different entry for around 100 people. Each entry will contain name, contact
details, three specific talents (ie. singing, dancing, painting). If they
only have 2, then the last field will be left blank. Now on top of this,
there will be a few prices, ie. price per hour etc...

Now once I have all my entries in my database, is there a way of me typing
in some search thingy-ma-bob "singing and dancing" and it will then come
back with a list of people who fall into this category?

Thanks all and sorry if this sounds a dumb question!
The free downloadable sample database at www.bullschmidt.com/access uses the
query by form concept so that on the invoices dialog one can optionally choose
a rep, a customer, and perhaps a date range, click on a button that says
"Input," and then have the invoice form open up showing all the invoices that
match the criteria.

And you'd like you can use this as a starting point for your project.

And here is how the query by form concept can work.

On the invoices dialog there are the following controls:
InvDateMin with DefaultValue of =DateSerial(Year(Date())-1,1,1)
InvDateMax with DefaultValue of =Date()
InvRepNum with DefaultValue of *
InvCustNum with DefaultValue of *

Also on the invoices dialog there is a command button called cmdInput to open
the invoices form with the following code behind the OnClick property:
DoCmd.OpenForm "frmInv"

And of course there could be a button to open a report the same way:
DoCmd.OpenReport "rptInv", acViewPreview

The invoices form (frmInv) has RecordSource property of qryInv.

And the qryInv query's criteria for the InvDate field has:
Between [Forms]![frmInvDialog]![InvDateMin] And
[Forms]![frmInvDialog]![InvDateMax]

And the qryInv query's criteria for the RepNum field has:
Like [Forms]![frmInvDialog]![InvRepNum]

And the qryInv query's criteria for the CustNum field has:
Like [Forms]![frmInvDialog]![CustNum]

One related point is that you probably wouldn't want to allow blanks (i.e.
Nulls) in fields that are going to be used with Like in any criteria for that
field. Otherwise the blanks wouldn't be shown.

For example (based on what is entered into a last name search field):

Like 'Smith' would show Smith records

Like '' would show no records (probably not what one would want)

Like '*' would show all records

And to counter that I like to have the search fields have a DefaultValue of *
and not allow the search fields to be blank.

Or a more complicated solution would be to create the query's SQL statement
dynamically so that the criteria on a particular field isn't used unless
needed.

For example:

' Set strSQL.
strSQL = "SELECT * FROM MyTable WHERE (1=1)"
If Not IsNull(Rep) Then
strSQL = strSQL & " AND (Rep='" & Rep & "')"
End If
If Not IsNull(Customer) Then
strSQL = strSQL & " AND (Customer='" & Customer & "')"
End If

Best regards,
J. Paul Schmidt, Freelance Access and ASP Developer
www.Bullschmidt.com/Access - Sample Access Database
www.Bullschmidt.com/login.asp - Web Database Demo
 
Back
Top