DB Structure Question

[wallymeister]

I have 3 tables (Models, Check Items & Parts)

Models Table has fields; [ModelID], [Desc], [Date] (e.g. 120 records)
Check Items table has; [CheckDesc], [IndexNo], [Shaded] (e.g 40 records)
Parts Table has; [PartNum], [Note], [Qty Per] (e.g should have 120 * 40
records)

What I'd like to know is, "Is there a way that when I add a new model, (121
now) the parts table will automatically populate with 40 new records from the
Check Items table. By the same token, when a new Check Item is added (41
now) a new record for all existing models be automatically added to the parts
table.

Thus the parts table will always contain the product of total Models records
times the total Check Items records.

Does this make any sense to anyone?
Any help will be greatly appreciated.
I'm just a Goober-roo
You guys are the Gurus
Thanks

 

[Fred]

Hello Wallymeister,

My first guess is that what you are trying to do might be a mistake.
Records are to store data, and if you are instantly loading 40 records which
are either blank or a copy of 40 records that are already in there, then
likely that you have a fundamental structure problem. But even to proceed
along that line, you'll need to describe the linkages between your tables,
and exactly what you want in those 40 new records.

You'll probably be better off by describing the nature and relationships of
your data, and then checking/ asking with the forum regarding your table
about asking about or checking on your table structure.

 

[wallymeister]

Fred,
Thanks for the reply.
No, it's not a mistake. This is what I have, presently if I add a new model
I have to manually copy and paste all the items in the Check Items table into
the Parts table with matching ModelId # of new model.

Sample data of each table

Models Table: ModelID (AutoNum-Key)=1; ModelDesc = "Widget1"; Size = "24"
etc.

CheckItems Table: CheckItemID(AutoNum-Key)=1 thru 40; CheckItemDesc={40
Different Desc's}; Shaded=True or False; etc.

Parts Table: ModelID (Many side of one to many with Models Table);
CheckID(Many side of one to many with CheckItems Table); PartNum; Qty Per

OK assume that I have 3 Model with 3 CheckItems the parts table would look
like this.

ModelID CheckID PartNum QtyPer
1 1 1111 1
1 2 1222 1
1 3 1333 5
2 1 2111 2
2 2 2222 1
2 3 2333 1
3 1 3111 1
3 2 3222 1
3 3 3333 4

When I add a new model, I want to automate the adding of the following to
the parts table.
ModelID CheckID PartNum QtyPer
4 1 empty empty
4 2 empty empty
4 3 empty empty

The other fields (PartNum, QtyPer) will always have to be manually populated
with appropriate part nos and qty per by user.

Does this make any more sense? I hope all this formats correcdtly as I have
typed it.
Thanks,
wally

 

[Fred]

Wally,

I'm still not understanding it well enough to answer.

Maybe somebody else can answer your question??

Otherwise, I'd just have more questions.

 

[Tom van Stiphout]

On Mon, 30 Mar 2009 12:12:09 -0700, wallymeister

You can use an Append query. Put the CheckItems table on it, and
select the Parts table to be appended to.
Set the ModelID column to DMax("tblParts", "ModelID") + 1
Set the CheckID column to CheckID from the CheckItems table.

-Tom.
Microsoft Access MVP

 

[wallymeister]

Tom,
Thanks for your guidance. I really appreciate your help.
When I create an append query with CheckItems Table how do I set ModelID to
anything. There is no ModelID field in CheckItems Table.

When I add the Parts Table so I now have a ModelID field I set ModelID to
DMax as you suggested w/o the +1 and run as Select query and it gives me what
I want. All the items in the CheckItems table and the Max ModelID. But when
I change it to an Append Query and add the + 1 it gives me message "You are
about to append 0 rows."

Is there something else I'm not getting? Again thanks for any help you can
give me.

wally

 

[wallymeister]

OK Tom,
I figured out what I need to do. First I need to add the new model in
Models Table/Form. Then I can run the update query w/o the +1 on DMax()
function. The update query has CheckItems Table & Models Table (no link
between these two) The models table is just there to find Max ModelID value.

What I need to do next is figure out how I can use this method but prevent
the adding of duplicate data by user. So if Max ModelID in Models Table
already exists in Parts table, prevent the Append from happening.

Thanks Tom,
Your input has helped alot.
wally

 

[Tom van Stiphout]

On Tue, 31 Mar 2009 06:33:04 -0700, wallymeister

That's not what I meant, but I'm glad you found a solution.

-Tom.
Microsoft Access MVP

 

[Tom van Stiphout]

On Tue, 31 Mar 2009 05:51:01 -0700, wallymeister

ModelID is not set to a field in CheckItems, but to the DMax
expression.

-Tom.
Microsoft Access MVP

 

[wallymeister]

Tom,
I'm still not sure what you meant to do but I have a working solution now
and can place code to evaluate whether or not to run append qry to prevent
duplicates.

Thanks again,
wally

 

[wallymeister]

I got what you meant now. Dummy me, I should have known immediately. For
some reason I was thinking I had to have fields to do append.

Thank you so much.
wally

 

[Guy]

Sounds to me like your wasting a load of space in your database. If you have
a new "Model" which uses all the same parts as several other models you
should create a BOM (Bill of Materials) table. Give each redundant parts list
an ID:

ID Part# whateverotherfields
BOM1 xyz other data
BOM1 abc more data
BOM2 wyz Different list

Now you can assign BOM1 in a single field for each relevant "Model" and pull
in all matching items using a filter or a join query on the Model table and
BOM table.

Guy