Record insert vs. update in related tables

[better]

Hello!

Can someone help me figure out how to set up my tables for this application?

I need to capture data on the contents of a package (doors).
Static data for the doors will include JobName, DoorName, and Location.
Dynamic data will include opt1, opt2, opt3...
I need default dynamic data sets defined in another table.
The live data options may stay defaults, or may be modified to create a door-specific data set.

I only want the user to intially supply the static data, and select a default set, but I need to be able to change the default set, or modify to create a door specific set.

In consideration of normalization, my thought is to have :

Table {Doors} Columns (DoorID*, JobName, Location, DoorName, DefID)
Table {DoorDefaults} Columns (DefID*, opt1, opt2, opt3...)

My concern is that I want changes to the options on a per-door basis to result in the creation of a new default-set, not a modification of an existing one. I understand how to update the defaults by a direct query of {DoorDefaults}, but how would I force updates to those values from a query of {Doors} to result in the creation of a new record in {DoorDefaults}?
:dunno

Can I specify an INSERT instead of an UPDATE if a certain defaultID ("custom") is specified?

Thanks a bunch,
Bart

[Norie]

Bart

I don't think that second table isn't normalized.

Wouldn't it be more like this.?

Table {Doors} Columns (DoorID*, JobName, Location, DoorName)
Table {DoorDefaults} Columns (DefID*,DoorID, optName, optValue)

Of course I may be wrong, but I haven't seen any data.:)

[better]

To be perfectly honest, Norie, I'm not entirely sure; I'm still very new at this. I think I may not have explained my scenario well enough. Table {Doors} holds unique data about the doors. Table {DoorDefaults} holds the values for the 20-someodd variables that apply to each door (door type, lock type, etc.), and many doors may share a common variable set.

I'm thinking that table {DoorDefaults} only needs one entry for each unique combination of variable values.

Bart

[XLGibbs]

better, you can stick to the original thread, or reference it when posting similar discussions so the new reader may understand better what it is you are doing overall..

Thanks.

http://www.vbaexpress.com/forum/showthread.php?p=57855#post57855

If you intend to UPDATE certain records when changed, you would use an UPDATE statement.

UPDATE [TABLE]
Set [FeildName] = {Argument} 'argument can be a Select statement from another table, joined to the [TABLE]

INSERT INTO [TABLE] (Feild names)
Select (FeildNames) FROM [OtherTABLE]

would be for adding new records to the database.

[Norie]

Gibbs

This is the original thread, I think.:)

But I know what you mean.

[XLGibbs]

Well, it appears to be related to the whole ASP issue,and I felt it relevant that you, among others be aware (not assuming you werent, but understanding context as you know is relevant sometimes)

[Norie]

Gibbs

I've not really considered the ASP issue, though I'm sure it's at the heart of the matter.

Don't you think the OP might have the wrong data structure in the first place?:)

That was my first thought, and I'll take a look at the attachment in the other thread when I get a chance.

[XLGibbs]

Well, he may have a different structure than you or I might design, but i think the problem lays somewhere in the ASP pull down of data. His intent appears to be to loop through the data as it is coming in, which I think may not be the more efficient way.

If it were me I would just pull in the data into a table, and have access use append/update queries to do the dirt. That said, I am sure the OP would be open to any and all suggestions to reach his goal as he is a self described novice at some of the code things.

[better]

I posted in seperate threads because I percieved the problems as two seperate issues, though they both exist in the same application. Do you prefer unrelated problems within one application to be posted in one thread, or in seperate, appropriately titled threads?

Yes, I am open to suggestions. I'm green, but not so green as to not know to heed the advice of experts! As for the ASP, I'm not sure I understand what you mean by looping through the data as it's coming in. What I think I'm doing with my ASP code is the equvalent of filling in the form and then hitting Submit 'N' times. I don't much care how it gets accomplished, I just want to create a specified number of records with the collected column values.
I understand what you mean by pulling the data into a table, and I think I understand what you mean about using an append query to do the work, but I don't understand how you cause the queryy to be executed the specified number of times.

Bart

[XLGibbs]

No problem Better, I just wanted to make sure respondents to this thread were aware of the tangent issues in the other...

You would use the looping device I showed you in the other thread, where you have an outer loop to move throught the recordset, and another inside it to perform the inner action the X number times. X being the the numberdoors from your table...