Adding fields to database from a application

[Rob Berkers]

Hello,

I wondered if it possible to add new fields to an excisting database
from within a Access Appliction.

If this database defined:

ID Unique ID (Integer)
Employee String 40
Date1 Integer
Date2 Integer


From the application I would like, when needed, to add more
"date-fields" (date4, date5 etc).

Can this be done?

Thanks in advance,
Rob

 

[Rick B]

Not sure what these fields are, but if you have a variable number of date
fields that apply to your employees, you should most likely store them in a
separate table. This is a classic one-to-many relationship.

TBL1
ID
EMPLOYEE


TBL2
EMPID
DATE



Each employee would have one record in TBL1, then they would have one or
more records in TBL2 depending on how many dates apply to the particular
employee.

One other piece of advice (not sure what you use this for). If [EMPLOYEE]
is a name you may reconsider storing this in one field. It never fails that
somewhere down the road you will need to print the names in LAST, FIRST
format for one report and FIRST LAST for another. You may want to consider
revising your structure to include FIRSTNAME and LASTNAME fields if you have
not gone too far in your design.

 

[Rob Berkers]

Not sure what these fields are, but if you have a variable number of date
fields that apply to your employees, you should most likely store them in a
separate table. This is a classic one-to-many relationship.

TBL1
ID
EMPLOYEE


TBL2
EMPID
DATE



Each employee would have one record in TBL1, then they would have one or
more records in TBL2 depending on how many dates apply to the particular
employee.

One other piece of advice (not sure what you use this for). If [EMPLOYEE]
is a name you may reconsider storing this in one field. It never fails that
somewhere down the road you will need to print the names in LAST, FIRST
format for one report and FIRST LAST for another. You may want to consider
revising your structure to include FIRSTNAME and LASTNAME fields if you have
not gone too far in your design.

Yes, I know that normally a one to many relation would be best. But
this databse has to be exported to Excel and all this information
must be in one row.

Rob.

 

[PC Datasheet]

Still use Rick's suggestion. Exporting to Excel is no problem! Just use a
query to pull all the data together in one line and export the query to
Excel.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

Not sure what these fields are, but if you have a variable number of date
fields that apply to your employees, you should most likely store them in
a
separate table. This is a classic one-to-many relationship.

TBL1
ID
EMPLOYEE


TBL2
EMPID
DATE



Each employee would have one record in TBL1, then they would have one or
more records in TBL2 depending on how many dates apply to the particular
employee.

One other piece of advice (not sure what you use this for). If [EMPLOYEE]
is a name you may reconsider storing this in one field. It never fails
that
somewhere down the road you will need to print the names in LAST, FIRST
format for one report and FIRST LAST for another. You may want to
consider
revising your structure to include FIRSTNAME and LASTNAME fields if you
have
not gone too far in your design.

Yes, I know that normally a one to many relation would be best. But
this databse has to be exported to Excel and all this information
must be in one row.

Rob.

 

[Rob Berkers]

That is very interesting.... I would prefer a one to many database.

I am just starting to look at all the possibilities there are with
queries. Can you give me an example how this can be done or give a
webpage where is described?

Thank you,
Rob.