Let's start again

[Continental Translations]

OK, lets start from the beginning as I agree I am not making much sense.

What i'm after is a database do keep a list of all my freelance translator
and their contact details (name, address, telephone number etc.....) All of
my contact entries will be automatically given a Contact ID (that being my
primary key for this table) Now, for each freelance translator, they will
each have one or more language pairs, the first language being the 'source'
language and the second being the 'target' language which they work between,
ie French into Spanish and English into Spanish. So, for example, my first
contact, William Brown, will be able to translate between French (source
language) and German (target language), and English (source language) and
German (target language). Now, I am not too sure whether I should put these
language details in the 'Contact Details' table or in a new table. I also
want to be able to add a price per 1000 words that each 'contact' charges.

Eventually, what I want to be able to do, is search on a few principles. For
example, I want to be able to search via surname, source language, target
language, source language AND target language together and price.

Any help??

 

[Continental Translations]

I forgot to mention. Each contact will have 1,2 or 3 specialist themes also.
So for example, contact 1 will translate between French and Spanish, with
specialism in Marketing and Engineering. I would also be able to search by
'specialisms' too as well as language combinations and specialisms

 

[Bruce]

First thing is that you should keep one thread going,
otherwise it can be difficult to know what has happened in
the past.
Good question about using a new table or the Details
table. If each translator had one source and one target
language, or some definite maximum, I would say to put it
in the Contact Details table (tblContactDetails). Since
you could have somebody with lots of combinations, I would
say to use a new table.
Let's call your autonumber Primary Key (PK) from the
details table ContactID. Make a new table for languages
(tblLanguages), with LanguageID as the PK ContactID as the
foreign key (FK), and fields for Source and Target.
Establish a relationship between the two tables. Make a
form (use autoform to get started) from each table (call
them frmContactDetails and fsubLanguages), and set up
fsubLanguage as a subform on frmContactDetails. One way
to do this is to drag the icon for the languages form onto
the contacts form in design view. Check Help for more on
this.
Whether you add Specialty to the tblContactDetails table
or to another depends for one thing on whether the
specialty applies to all source and target languages. If
so, and if you want to limit it to three or so, you could
have that information in tblContactDetails table. If the
specialty varies for different source and target languages
(for example, Marketing specialty might not apply to all
language combinations for one translator), you could add
the information to the languages table, so that selecting
Source and Target would also mean selecting a Specialty
(or Specialties) relevant to that combination. Or you
could take a similar approach as with tblLanguages, and
link the Specialties table (tblSpecialty) to either
tblContacDetails in identical manner as with tblLanguages,
or link it to tblLanguages, using LanguageID as the FK in
tblSpecialty. It all depends on your needs. After
establishing the relationships, make a form out of
tblSpecialty and set it up as a subform to either
frmContactDetails or fsubLanguages.
I think this will get you started. I probably will not
check this newsgroup again until Monday. Good luck.

 

[Bullschmidt]

<<
What i'm after is a database do keep a list of all my freelance translator
and their contact details (name, address, telephone number etc.....)
....
Eventually, what I want to be able to do, is search on a few principles. For
example, I want to be able to search via surname, source language, target
language, source language AND target language together and price.
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 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