Parsing (Contencate?) 2 fields into 1

[Guest]

I need to parse(or is it contencate) 2 fields together from different tables
to make a Primary key on each record in my main table [Training Course], the
Primary Key is called [Course ID] and i need help to write the code to parse
the data from the fields "Date" in [Training Course] & "Course Code" in table
[Course Type].

The user will enter data in a form called "Year Planner" based upon the
table [Training Course] where they will enter the "Date" and "Course Type" in
the form with other data to create a record in the [Training Course] table.
The field "Course Type" is the PK of the table of the same name and is a
foreign key in [Training Course]. Course Code is a 3 charachter text string.

[Trainign Course] table will look like this:

(PK in Course Type) PK(in Training Course)

Date "Course Type" "Course ID"
11/07/2006 Diversity Training DVT110706
15/01/2006 EWS Training EWS150106


I need some code that will take the data from these two fields and combine
them to make Course ID before i save the record since i will need the PK to
be able to do this.

 

[Guest]

Hi Engels,

Concatenate is the word you need; parse means to break down a sentence into
its component parts describing them grammatically.

I'm not too sure where you need the help, I suspect its in turning a date
into a numeric string and then appending it to the course abbreviation. (By
the way where does the course abbreviation come?) Or is it retrieving the
data? Or is it writing the concatenated data to the main table. More about
the concatenation later.

Have you considered compound keys for your main table? You don't need to
concatenate then. Simply define both fields in the table design, then select
them, then click on the primary key button in the toolbar. Alternatively
define a meaningless primary key such as an autonumber and then define a non
primary compound key via View/Indexes from the menu bar. Make this index
unique values.

Concatenation is achieved by using the ampersand (&). There are many, many
ways to turn an Access date into a string. If you don't care about the
slashes then Format(CourseDate,"Short Date") will suffice. Read up on both
the Format function and the DatePart function.

Something like MyNew PK = CourseAbbrv & Format(CourseDate,"Short Date")
will do it for you.

If this is not what you wanted then shout.

Regards,

Rod

 

[Guest]

Hi Rod

I think you understand most of what i am saying. You are right i want to
concatenate.

I had forgotten i needed to turn the date into a string i think i wil be
able to work this out.

The course abbreviation is in the "Course Types" table although it is not
the PK. The [Training Course] primary key needs to be a concatenation of the
abbrevation of (a text field "Course Code") and the date of the course to
make a unique identifier (there cant be two types of the same course on the
same day)..

The database is fairly well developed so far with manual entry of the Course
ID, hence it wil be a pain to redevelop by changing the way the primary key
works.. hence the fact i am trying to work around this by creating this
"automatic" primary key.

I'm still a newbie with the coding but i guess i need the help most with the
code fragment that will copy the data from one table to another and then
concatenate it with the date field from the same table that i am "pasting" to.

Thanks

 

[Tim Ferguson]

I need some code that will take the data from these two fields and
combine them to make Course ID before i save the record since i will
need the PK to be able to do this.

No you don't: really bad idea. Can I suggest that your tables look
something like

CourseTypes (
CourseType Text(3) PRIMARY KEY,
FullName Text(32),
Duration Integer,
TutoredBy Text(5),
etc
)

TrainingCourses (
StartDate DateTime,
CourseType Text(3) FOREIGN KEY REFERENCES CourseTypes,
PaidUp Currency,
etc
CONSTRAINT pk PRIMARY KEY (StartDate, CourseType)
)

(PS: you can mess about with spaces and brackets if you like, but for
legibility I have used only legal characters in table and field names)

You really need to keep the CourseType as a separate field in the
TrainingCourses table, because otherwise you wont be able to enforce the
relationship with the CourseTypes table. There is no problem with making
the pair of fields into the Primary Key: just ctrl-click the rows in the
table design and then click the yellow key toolbar button.

Hope that helps


Tim F

 

[Guest]

Hi Engels,

Tim (see Tim Ferguson's post) is correct in describing the 'best practice'
design. In fact to my mind a full design has a Course table with a
CourseType table qualifying a foreign key value in Course. Then there is a
Person table that contains details of student/delegates (and trainers if you
wish). There is also a CourseInstance table that is a child of the Course
table giving the dates, location, fees and whatever else you need to record
about a training session. There could be many CourseInstances for each
Course. Finally there is an associative table relating CourseInstances with
Persons. The benefit of first designing a full detailed entity relationship
is that as you reduce it to a more practical level that suits the
circumstances you know exactly what features and functions your design cannot
provide and what assumptions you are making. If you want to take this
further try and find some texts on Data Normalization (3rd Normal Form is
sufficient) and Entity Relationship Diagrams. The Access Relationships
window is in fact an Entity Relationship Diagram (ERD) when populated with
relationships. The fact that it also shows attribute listings inside the
entity boxes makes it a Bachmann Diagram (think I'm right - seem to remember
that name from eons ago).

But for let's assume you are going ahead with your current design. You say
Year Planner is based on (bound) to Training Course yet the latter is the
table for which you need to compose a primary key. Thus you cannot save a
new record until you have inseterted the new key in the field bound to the
table attribute (or column) for that primary key. I therefore recommend you
use the form's BeforeUpdate event to insert this key. However as the event
fires for all updates, not just new records, it is important to first test
for the existence of a key, supplying a new one only if it is missing.

A prerequisite is that you must include the primary key attribute/column in
your bound result set - expressed less formally: Course ID must be implicitly
selected by use of "*" in the SQL (or wizard) or specifically selected by
name. It may be included as a field on the form and this is probably best as
you have a meaningful key to which users can refer. If it is I suggest you
make the field/control Disabled and Locked. Also turn off the Tab Stop. I
will assume this control is named Course ID.

Don't use the name Date by itself for anything. Date is a function that
return the system date. Most of the time you will get away with it but
sooner or later there will be an instance where you get an unpredictable
error. I assume you rename this Course Date.

You mention that the user enters Course Type. I won't enquire how the user
selects this but just assume a valid course type is available in a
field/control on the form.

In the form's BeforeUpdate procedure code something like the following. (I
have used the underscore concatenation symbol to split an otherwise long
statement across several lines.)

If Len(Trim([Course ID])) = 0 then
[Course ID] = [Course Type] & _
Format(DatePart("d",[Course Date]),"00") & _
Format(DatePart("m",[Course Date]),"00") & _
Format(DatePart("yyyy",[Course Date]) Mod 100, "00")
End If

If all my assumptions are correct Access takes care of the rest.

Regards,

Rod

Hi Rod

I think you understand most of what i am saying. You are right i want to
concatenate.

I had forgotten i needed to turn the date into a string i think i wil be
able to work this out.

The course abbreviation is in the "Course Types" table although it is not
the PK. The [Training Course] primary key needs to be a concatenation of the
abbrevation of (a text field "Course Code") and the date of the course to
make a unique identifier (there cant be two types of the same course on the
same day)..

The database is fairly well developed so far with manual entry of the Course
ID, hence it wil be a pain to redevelop by changing the way the primary key
works.. hence the fact i am trying to work around this by creating this
"automatic" primary key.

I'm still a newbie with the coding but i guess i need the help most with the
code fragment that will copy the data from one table to another and then
concatenate it with the date field from the same table that i am "pasting" to.

Thanks

 

[Guest]

Sorry, quick update. I meant to allow for the fact that Course ID could be
null. Change the If statement to:

If Len(Trim(Nz([Course Id],""))) = 0 then

Rod

 

[Guest]

Rod &Tim thanks for the help

Gven how developed the database is so far i cannot go back and change the
whole stucture like Tim suggests although i can see why this is the correct
soluton had i have imlpemented this earlier on in the process. I have now
implemented what Rod has suggested.

Rod you correctly infered the different tables and relationships that i do
have already and that the form i am using is indeed bound to the table
Trainign Course.

I haev impemeted the code you suggested succesfully however there is one
difference in that i do not want to use "Course Type" as the first part of
the "Course ID".
I want instead to use "Course Code" this is a column on the [Course Types]
table and is related to [Training Course] through "Course Type" pk. I am
trying to adapt you code to call this field instead although am stuggling a
bit so a little bit of help here would be appreciated...

 

[Guest]

Hi Ngels,

OK change the second line in my example to be:

[Course ID] = DLookUp("[Course Code]",[Course Type]","[Course Type] = " &
[Course Type]) &

Rod

 

[Guest]

Thanks that works really well but you seemingly had a rogue "&" in that bit
of code just after the equals sign and i got really confused and a few other
bits have changed like table/field names. But i have got it working and this
is the code i have used:

[Course ID] = DLookup("[3 Letter Course Code]", "Course Types", "[Course
Type] = [Course Type]") & ................

It works but maybe you meant it to do somethign slightly different?

Thanks again

Hi Ngels,

OK change the second line in my example to be:

[Course ID] = DLookUp("[Course Code]",[Course Type]","[Course Type] = " &
[Course Type]) &

Rod

Rod &Tim thanks for the help

Gven how developed the database is so far i cannot go back and change the
whole stucture like Tim suggests although i can see why this is the correct
soluton had i have imlpemented this earlier on in the process. I have now
implemented what Rod has suggested.

Rod you correctly infered the different tables and relationships that i do
have already and that the form i am using is indeed bound to the table
Trainign Course.

I haev impemeted the code you suggested succesfully however there is one
difference in that i do not want to use "Course Type" as the first part of
the "Course ID".
I want instead to use "Course Code" this is a column on the [Course Types]
table and is related to [Training Course] through "Course Type" pk. I am
trying to adapt you code to call this field instead although am stuggling a
bit so a little bit of help here would be appreciated...

 

[Tim Ferguson]

Gven how developed the database is so far i cannot go back and change
the whole stucture like Tim suggests

If you can't afford to design it right, how come you can afford to design
it wrong..?


B wishes


Tim F

 

[Guest]

Hi again.

I hate to worry you but there was no spurious ampersand in my code and the
code you posted is incorrect. The third term of the DLookUp should resolve
to a SQL WHERE clause without the word WHERE. In essence you are telling the
function, "where the value in the table column equals the local value I am
giving you." I assumed course type was numeric, on rereading I believe it
may not be. Anyway the syntax if numeric is

"[Course Type] = " & [Course Type]

The sysntax if a string is

"[Course Type] = '" & [Course Type] & "'"

The reason for the second syntax is that strings must be enclosed within
quotes.

What you have coded is, "where the value in the table column equals the
value in the table column." This (apart form nulls) is always true and
Access will return the first record. Always!

Regards,

Rod

Thanks that works really well but you seemingly had a rogue "&" in that bit
of code just after the equals sign and i got really confused and a few other
bits have changed like table/field names. But i have got it working and this
is the code i have used:

[Course ID] = DLookup("[3 Letter Course Code]", "Course Types", "[Course
Type] = [Course Type]") & ................

It works but maybe you meant it to do somethign slightly different?

Thanks again

Hi Ngels,

OK change the second line in my example to be:

[Course ID] = DLookUp("[Course Code]",[Course Type]","[Course Type] = " &
[Course Type]) &

Rod

Rod &Tim thanks for the help

Gven how developed the database is so far i cannot go back and change the
whole stucture like Tim suggests although i can see why this is the correct
soluton had i have imlpemented this earlier on in the process. I have now
implemented what Rod has suggested.

Rod you correctly infered the different tables and relationships that i do
have already and that the form i am using is indeed bound to the table
Trainign Course.

I haev impemeted the code you suggested succesfully however there is one
difference in that i do not want to use "Course Type" as the first part of
the "Course ID".
I want instead to use "Course Code" this is a column on the [Course Types]
table and is related to [Training Course] through "Course Type" pk. I am
trying to adapt you code to call this field instead although am stuggling a
bit so a little bit of help here would be appreciated...