[bentfocus]

I have figured out how to add a new record to ONE table in an Access database.
I need to add same records to MULTIPLE tables in same Access database.

In other words...
In my football program wanted to create an add players to pool form.
I have done so, yet can only add records for the new player in one table (Week1, or whatever I put as data source for an ado object).

I have a different table for each week in the nfl season. So Access database has a form "Week1", a form "Week2"...and so on.
Each form has a field "Player name" along with fields:"Pick16" thru"Pick1"
I want my form in VB application to add players to the pool, thereby adding The players name into the "Player name" field in each of the tables representing the 17weeks in an NFL football season.

I hope I've been clear enough...I know that all of you know how difficult it can be to explain what it is that you have trouble with.

Thanks in advance for your help!
Ryan

[CThompson]

Are you writing your code in VBA (if so could I see a snippet) or are you using wizards?

[deell]

example:

Dim db As Database
Dim rsWeek As Recordset
Dim PName As String 'your player name

Set db = OpenDatabase("[your database name]")

PName = "..."

For i = 1 to 38 'say u have table 'Week1' to 'Week38'
    rsWeek = db.OpenRecordset("Select * From Week" & i)
    rsWeek.AddNew
    rsWeek.Fields("Player Name") = PName
    rsWeek.Fields("[...]") = [...]
    [...]
    rsWeek.Update
Next

[bentfocus]

I'm not sure how the code posted above will help.
Where should it be placed?
I am new at this so bear with me...

[gellerche]

bentfocus:

One of the more experienced people here may contradict me, but what you are doing is a MAJOR violation of the rules of normalization (Rule #1, I think). Instead of having a separate table for each week, why not add a WeekOfTheYear field to your table, and populate that as you need to? This way, all your queries, reports, etc. can stay the same, instead of needing a separate one for each table.

Steve Geller

[bentfocus]

Ya know,
It actually FELT wrong as I did it that way...waste of space.

I don't really know what Im doing in access or vb, but if I put a WEEKoftheYear field to a table I don't see how I can have each players records show 14-16 picks for each week.
The way it is now, one row corresponds to that player only...14-16 columns across for each winner they pick...one form for each week.

[cmeares]

Quote:

---

Originally Posted by bentfocus
Ya know,
It actually FELT wrong as I did it that way...waste of space.

I don't really know what Im doing in access or vb, but if I put a WEEKoftheYear field to a table I don't see how I can have each players records show 14-16 picks for each week.
The way it is now, one row corresponds to that player only...14-16 columns across for each winner they pick...one form for each week.

---

It's mui bueno to have it all in one table. I'm not understanding your table structure though.

YOu mind posting your project / db ?

you can also email it to

theocpcguru at hotmail dot com

i'll look at it.

[bentfocus]

Here it is:

[bentfocus]

Quote:

---

Originally Posted by CThompson
Are you writing your code in VBA (if so could I see a snippet) or are you using wizards?

---

I posted the app above...check it out.

[deell]

ya. what gellerche suggests will be better.

[bentfocus]

can someone here reccommend a way for me to create a database that is normalized?
Maybe post a small example for access.
Thanks a bunch!

[gellerche]

Quote:

---

Originally Posted by bentfocus
can someone here reccommend a way for me to create a database that is normalized?
Maybe post a small example for access.
Thanks a bunch!

---

Here's a link:
http://www.datamodel.org/NormalizationRules.html