Trouble Using "RunSQL" to add Column to Table

[Boatguyrookie]

I am simply trying to us a line of code
DoCmd.RunSQL "alter table tblphyinvrecords add column now() date"

To create a new column within this table with a label of the current system date.

That way each time someone inputs a daily cycle count it will record the counted number under the column of the day it was counted.

However it doesn't accept the "now()". Anyone have any ideas to make this happen?

 

[SkipVought]

Hi,

It's really a BAD DESIGN to add DATA as columns. It De-normalizes your table, assuming that it was normalized. This is not a best and accepted practice. Your table would CONSTANTLY be changing it's schema. YUK! What a nightmare!

This Date() data ought to be store in a column for Date/Time data.

Skip,

_{I'll be dressed to the nines this week, as I go to have my prostatectomy...
Because, if I'm gonna BE impotent, I want to LOOK impotent!}

 

[Boatguyrookie]

Skip..
Thanks for the heads up, I'm kinda new to the way things should be, and I am used to just trying to find ways to make them work.

In your case how would I make that line of code create a new column with a different name each time? Such as if I were to just type a "standardized" name, then the next time it went to record the data then it wouldn't let it due to having the same name.

Also how do I get it to create that column and then insert the current date as a piece of data?

Thanks

 

[SkipVought]

Your table should not be adding columns. You add ROWS.

Why do this with code? Just add a column for InventoryDate, ONE TIME.

Then a new inventory transaction is just a NEW row in the table, with Date() in InventoryDate.

FYI:

http://www.deeptraining.com/litwin/dbdesign/FundamentalsOfRelationalDatabaseDesign.aspx

Skip,

_{I'll be dressed to the nines this week, as I go to have my prostatectomy...
Because, if I'm gonna BE impotent, I want to LOOK impotent!}

 

[Boatguyrookie]

Check out below, showing an example of the way I was thinking it would work. The PN# column will usually have about 3000 items in it. Then each day the warehouse will cycle count a handful of the items, and I wanted to create a column to hold each days count serperatly. It is dealing with multiple counts to each single date, so I don't think I can just add a date to each row.

Any suggestions?

PN# / 3-8-08 / 3-9-08 / 3/10/08
4511 / 5 / 6 / 2
1154 / 1 / 6 / 1

 

[SkipVought]

[tt]
PN# InvDate Qty
4511 3-8-08 5
4511 3-9-08 6
4511 3-10-08 2
1154 3-8-08 1
1154 3-9-08 6
1154 3-10-08 1
[/tt]
is typical.

Skip,

_{I'll be dressed to the nines this week, as I go to have my prostatectomy...
Because, if I'm gonna BE impotent, I want to LOOK impotent!}

 

[Boatguyrookie]

I agree and I thought about doing it this way, however since each day it would be appending hundreds of items. I figured I would run into a problem wiht too many rows before a month or two.

 

[SkipVought]

Not a problem.

However the converse would be a problem, where after one year you would have some 250 columns and 500 after two years and 750 after three..... YUK!.

Using that kind of table would be a MESS!

As I previously stated, it is certainly NOT an accepted practice in the industry. I work in the aricraft industry, where we have hundreds of thousands or millions of rows of data, but tables with FIXED structure. Columns rarely if ever get added.

Skip,

_{I'll be dressed to the nines this week, as I go to have my prostatectomy...
Because, if I'm gonna BE impotent, I want to LOOK impotent!}

 

[Boatguyrookie]

Ok - I'll got that route. Thanks