Hello All,
I have these tables (lets call it ‘EmpCalls', ‘EmpOrders', and
‘Stats') that each contain the list of EmployeeIDs, I want to be able
to create a Module in which I could call in my VB form (by clicking
and command button)that will add a column (field) to each table and
label it with the current date that is specified in my form. Does
anyone know how I can go about doing this?
Any suggestion will be greatly appreciated.
11  14413 
You could use DoCmd.RunSQL (or the DAO equivalent) to run an ALTER TABLE
statement which adds the field. Look up the syntax in Access help.
I'm curious though, why would you want to do this? I feel scary database
design in the offing...
"Bobbak" <bo****@ottawa. com> wrote in message
news:64******** *************** ***@posting.goo gle.com... Hello All,
I have these tables (lets call it 'EmpCalls', 'EmpOrders', and
'Stats') that each contain the list of EmployeeIDs, I want to be able
to create a Module in which I could call in my VB form (by clicking
and command button)that will add a column (field) to each table and
label it with the current date that is specified in my form. Does
anyone know how I can go about doing this?
Any suggestion will be greatly appreciated.
Bobbak wrote: I have these tables (lets call it ‘EmpCalls', ‘EmpOrders', and
‘Stats') that each contain the list of EmployeeIDs, I want to be able
to create a Module in which I could call in my VB form (by clicking
and command button)that will add a column (field) to each table and
label it with the current date that is specified in my form. Does
anyone know how I can go about doing this?
Your question is only the tip of the iceburg of problems
you're going to run into. There is a limit of 255 fields in
a table so this approach will eventually crump out on you.
It will also create all kinds of problems if you ever want
to have a report with totals or other cumulative results.
Having this kind of table is often called "committing
spreadsheet" on your database.
You should normalize your table by using a row for each
date/employee instead of adding a column for each date. A
good rule for designing tables is to remember that columns
are very expensive, but rows are almost free.
--
Marsh
MVP [MS Access]
Marshall Barton <ma*********@wo wway.com> wrote in message news:<rt******* *************** **********@4ax. com>... Bobbak wrote:
I have these tables (lets call it ?EmpCalls', ?EmpOrders', and
?Stats') that each contain the list of EmployeeIDs, I want to be able
to create a Module in which I could call in my VB form (by clicking
and command button)that will add a column (field) to each table and
label it with the current date that is specified in my form. Does
anyone know how I can go about doing this?
Your question is only the tip of the iceburg of problems
you're going to run into. There is a limit of 255 fields in
a table so this approach will eventually crump out on you.
It will also create all kinds of problems if you ever want
to have a report with totals or other cumulative results.
Having this kind of table is often called "committing
spreadsheet" on your database.
You should normalize your table by using a row for each
date/employee instead of adding a column for each date. A
good rule for designing tables is to remember that columns
are very expensive, but rows are almost free.
Well the database file itself will only contain data for one month at
a time, so the 255 field limit is not an issue.
The reason for me to have the code to add a column instead of adding
the columns manually when designing the table is to save myself some
time every month from re-creating the tables.
But thanks again for your suggestion, if you have any other ideas on
how i can achieve this, I'd very much like to know.
What are you planning to do when your boss wants a comparison of the last 2
months.
"Bobbak" <bo****@ottawa. com> wrote in message
news:64******** *************** ***@posting.goo gle.com... Marshall Barton <ma*********@wo wway.com> wrote in message
news:<rt******* *************** **********@4ax. com>... Bobbak wrote:
I have these tables (lets call it ?EmpCalls', ?EmpOrders', and
?Stats') that each contain the list of EmployeeIDs, I want to be able
to create a Module in which I could call in my VB form (by clicking
and command button)that will add a column (field) to each table and
label it with the current date that is specified in my form. Does
anyone know how I can go about doing this?
Your question is only the tip of the iceburg of problems
you're going to run into. There is a limit of 255 fields in
a table so this approach will eventually crump out on you.
It will also create all kinds of problems if you ever want
to have a report with totals or other cumulative results.
Having this kind of table is often called "committing
spreadsheet" on your database.
You should normalize your table by using a row for each
date/employee instead of adding a column for each date. A
good rule for designing tables is to remember that columns
are very expensive, but rows are almost free.
Well the database file itself will only contain data for one month at
a time, so the 255 field limit is not an issue.
The reason for me to have the code to add a column instead of adding
the columns manually when designing the table is to save myself some
time every month from re-creating the tables.
But thanks again for your suggestion, if you have any other ideas on
how i can achieve this, I'd very much like to know.
"Bobbak" wrote Marshall Barton <ma*********@wo wway.com> wrote in message news:<rt******* *************** **********@4ax. com>... Bobbak wrote:
I have these tables (lets call it ?EmpCalls', ?EmpOrders', and
?Stats') that each contain the list of EmployeeIDs, I want to be able
to create a Module in which I could call in my VB form (by clicking
and command button)that will add a column (field) to each table and
label it with the current date that is specified in my form. Does
anyone know how I can go about doing this?
Your question is only the tip of the iceburg of problems
you're going to run into. There is a limit of 255 fields in
a table so this approach will eventually crump out on you.
It will also create all kinds of problems if you ever want
to have a report with totals or other cumulative results.
Having this kind of table is often called "committing
spreadsheet" on your database.
You should normalize your table by using a row for each
date/employee instead of adding a column for each date. A
good rule for designing tables is to remember that columns
are very expensive, but rows are almost free.
Well the database file itself will only contain data for one month at
a time, so the 255 field limit is not an issue.
The reason for me to have the code to add a column instead of adding
the columns manually when designing the table is to save myself some
time every month from re-creating the tables.
But thanks again for your suggestion, if you have any other ideas on
how i can achieve this, I'd very much like to know.
Columns are added using SQL's ALTER TABLE, DAO's CreateField
or ADO's Fields.Append. However, your problem description
suggests this may not be the solution.
De-normalizing can be the thing to do - most often for boosting
performance in reporting systems. Tracking history (that's what
your after, or?) is hardly ever a good reason and de-normalizing
operational data is almost taboo.
Look again at Marshall's suggestion and consider crosstab queries for
lining up the dates. Why re-create tables or add columns at all?
Simply empty the tables each month, if you really must.
"paii, Ron" <pa**@packairin c.com> wrote in message news:<vp******* *****@corp.supe rnews.com>... What are you planning to do when your boss wants a comparison of the last 2
months.
That's not gonna happen, the purpose of this is to have a monthly
figure per employee, by day.
Now can we get back to my orginal question.
"Ben Eaton" <be************ @hotmail.com> wrote in message news:<bn******* ****@news.wplus .net>... You could use DoCmd.RunSQL (or the DAO equivalent) to run an ALTER TABLE
statement which adds the field. Look up the syntax in Access help.
I'm curious though, why would you want to do this? I feel scary database
design in the offing...
You have nothing to be scared of, this isn't your database that you
are doing this too.
You will may not get much help doing what you propose. Along with what
Marshall Barton stated, I think you will have table corruption problems is
you try to add fields to a active database. You cannot add fields to a table
while you are viewing it with a form.
If you add a date field to your detail records, it will be easy to use a
cross tab query to extract dates from any month. The Cross tab will have the
dates as column headings. You can then empty the tables every month,
although that will not be necessary.
"Bobbak" <bo****@ottawa. com> wrote in message
news:64******** *************** ***@posting.goo gle.com... "paii, Ron" <pa**@packairin c.com> wrote in message
news:<vp******* *****@corp.supe rnews.com>... What are you planning to do when your boss wants a comparison of the
last 2 months.
That's not gonna happen, the purpose of this is to have a monthly
figure per employee, by day.
Now can we get back to my orginal question.
"Bobbak" <bo****@ottawa. com> wrote in message
news:64******** *************** ***@posting.goo gle.com... "Ben Eaton" <be************ @hotmail.com> wrote in message
news:<bn******* ****@news.wplus .net>... You could use DoCmd.RunSQL (or the DAO equivalent) to run an ALTER TABLE
statement which adds the field. Look up the syntax in Access help.
I'm curious though, why would you want to do this? I feel scary
database design in the offing...
You have nothing to be scared of, this isn't your database that you
are doing this too.
We do get a bit scared and do have something to be scared of.
We might be hired after you leave and one person bad mouthing Access does
more harm than 1000 silent because they are happy people can overcome.
Mostly I suspect we are curious since writing code to add something that
will only be useful in a table after it has been created unless you plan on
writing a lot more code is a curious thing. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: Claudio Lapidus |
last post by:
Hello
Now perhaps this is a bit dumb, but...
I just populated a new table via \copy. After that, I realize that perhaps is a good thing to have a row identifier in it, so I try
clapidus=> alter table tickets add column rid serial;
NOTICE: ALTER TABLE will create implicit sequence "tickets_rid_seq" for SERIAL column "tickets.rid"
ERROR: adding columns with defaults is not implemented
|
by: Gail Zacharias |
last post by:
I am investigating the possibility of using pgsql as the database in an application. I have some unusual requirements that I'd like to ask you all about. I apologize in advance if my terminology is a little "off", I'm not familiar with pgsql (yet).
My first requirement is that I need to be able to add new columns to any database table on the fly. By this I mean that I need this to happen interactively -- a user will drag some UI widget...
|
by: Tony D. |
last post by:
I could really use some help with a problem I am having. I am trying
to add a new field to an existing form that combines two tables into
one form. In this same databse I have another form that exists off of
1 table and when I add a new field into the table I have no problem
adding into the corresponding form. However when I add a new field to
one of the following tables:
- Employee List & Main Profile tables (This is what the combined...
|
by: Randy A. Bohannon |
last post by:
This is probably a simple thing to do, but I can't find how in the help
files or the manual I have.
I'm using Access 2000. I have three tables joined by a common field, and
I'm using one form to enter data into all of them. The form was created
using the form wizard. I need to add new fields to one of the tables, and
consequently, to the form. I can add the fields to the table, but not to
the form. I've done this before with a form...
|
by: GL |
last post by:
Hi,
Is there a way to add a field to an existing table using a query of
some sort (without needing to manually add a field to the table). I
know how to do it with a make table query, but I have a specific need
to only add a new field to a table if possible. Here's a simplified
example of what I'm trying to do:
I get a file with the following two fields:
First Name
| | |
by: Art |
last post by:
Hi everyone
I was hoping someone might be able to help me with this. I'm just starting to try to work with MS Access tables through VB.net. In Access I can take an existing table and add a new field with the type AutoNumber. I can then set this up as a key. Then if I go into the table I will see sequential numbers have been inserted into that field for me. Can I do this through VB.net
I'd appreciate any help or alternative suggestions...
|
by: Rudy |
last post by:
Hi all,
I know this is easy, just can't seem to get it. I have a windows form, and
a text box, with a value already in it. I need to add that value to a table.
It's just one value, so the entire row doesn't get filled. I have a
connection and all that stuff.
Private Sub btnPlaceBet_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnPlaceBet.Click
' Dim Myds As Footbet.DStable
'...
|
by: Microsoft_Public |
last post by:
All I'm getting is <null>......
I have a legacy input form that I must maintain for a few more months until
the balance of the site can be converted to .Net.
I need the one database field to contain either 0, 1, 2.
The form has 2 pairs of radio button fields.
If the answer to the first Q is Yes value = 1
|
by: Dixie |
last post by:
I want to append a number of records onto an existing table. Each record
will have an ID number (it is not an autonumber as it is too variable). It
is currently set as an integer field. I want to be able to work out what
the highest existing number in that field in the table is and start adding 1
to it for each record. In other words, I want a bunch of sequential numbers
with no gaps.
Is there a simple way to do this with a query?
...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
| | |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
