Form to display information from junction table

[MickAtlas]

Hello all,

I've established a many to many relationship using a junction table.

So I have 3 tables (A for "materials", B for "batches", and J for "junction")

Form A is linked to table A, and contains a subtable linked to a query from table J. This allows me to input materials into table A and then list all of the batches it may be used in that are in table B. I successfully got this to input all the batches and materials combinations in table J.

Now on form B, which is linked to table B, displays the batch information, with the subtable J.

My problem, is that only the materials primary key is showing, not the other information that should be linked from table A.

I've zip filed my database. Anything that would help would be extremely appreciated!

Cheers,

Mick

 

[Mihail]

I can't ope your DB because is in a newer version of Access than I use (2007).
Convert your DB in A 2003 version (if possible) and upload it again.

 

[MickAtlas]

It will not allow me to do that. Looks like I am using something that was not in earlier formats. I'm sorry. I am using 2010.

I have noticed I have many tables that are automatically populated when i make a new database, they include:

MSysAccessStorage
MSysAccessXML
MSysACEs
MSysComplexColumns
MSysNameMap
MSysNavPaneGroupCategories
MSysNavPaneGroups
MSysNavPaneGroupToObjects
MSysNavPaneObjectIDs
MSysObjects
MSysQueries
MSysRelationships
MSysResources

Would deleting any of these if not required allow me to save it in an earlier format without affect the database?

 

[Mihail]

There are System table that Access use in order to deal with your DB.
Is no danger if you remove this from the Relationships window.

Create a new DB in 2003 format and import the tables. Then upload this DB.

 

[MickAtlas]

Here. The autopopulated tables may be doubled.

 

[Mihail]

) My friend,
in table tblProductionRecordJunction you have a single field filled with data: Production Record #. Funny is that you haven't this field in your subform.
So, you can't see it.
Fill more fields with data and you will see the values in your subform. Have my word !

 

[MickAtlas]

) My friend,
in table tblProductionRecordJunction you have a single field filled with data: Production Record #. Funny is that you haven't this field in your subform.
So, you can't see it.
Fill more fields with data and you will see the values in your subform. Have my word !

Yes, my effort is with the tbl B, where I would like all of the fields in the subtable to be populated with information from tbl A. In the first form I want to put the material information in, and then add the batches individually.

Cheers,

Mick

 

[MickAtlas]

If you go to tblzzCMRCompletionTracking, you will see the subtable when clicking on the + symbol next to record. The fields in the + part are the same as this table, but when filling in the information they do not populate with the same information. Does this make sense?

 

[Mihail]

In my opinion it does.
Just you need to enlarge a bit the control (the subform).
Also take a look to the other form where I inserted a new subform

 

[MickAtlas]

Sorry, I may have given an unclear description of what I was going for.

I would like to not have to input the same information over and over again into the subtable for it to show up on the other form.

I may have gone about this the wrong way. My idea was to have a many to many relationship set up, with a junction table in the center have all of the common fields that I require, and then having them autofilled for each.

So a material is tracked by it's [WN#] "warehouse number" - primary key
A batch is tracked by it's BPR "batch production record" - primary key
the junction table has both of these set as it's primary key, linking the two tables in a many to many relationship.

There should be another way to go about it, instead of putting all of the information from both tables into the junction table. Do you know of this other way?

 

[Mihail]

Only the IDs (foreign keys) are necessary in a junction table
tblJunction
ID_Junction (PK - Autonumber)
FK1
FK2
etc.

Unfortunately your DB is too big for me to understand it at all.
What I can say is that a PK as text is not the best idea.
Also, I don't like the compound PKs.

But seems to be too late for you to change this things.

In my opinion you should rethink all your DB.
But, maybe the more skilled guys can find a better solution for you.

 

[MickAtlas]

I don't have a problem with rethinking the whole thing.

I can scrap the non foreign keys from the junction table. I tried it this way first and attempted to create a query that pulls information from both the junction table and the materials table.

Ideally if I had material A going into batches 1, 2 and 3, then the junction table would only record, [batch 1, material A] [batch 2, material A] [batch 3, material A]. How would I build a query that would give me this AND the remainder of the information for material A from the materials table?

 

[Mihail]

OK. Lets do a try.
So, what is the whole business behind your DB ?
Before starting to answer, take a look here:
Database Normalization and Table Structures - Microsoft Access / VBA
then try to split the information in small pieces, in order to design, together, the necessary tables, step by step.

 

[MickAtlas]

I see what you are saying after reading the article.

Each individual material may be used in one or more batches. But this is not always known when receiving the material, which batch it will be used in.

Is this the case where the batch number does not relate to the material's tracking number?

My intention is to have a form or report that will track the batch number, and the materials assigned to it, through their individual tracking numbers. Each material (material specification number [MS#]) may have many warehouse receiving numbers [WN#]s which track individual orders. However each WN# will only be used once per order and this is why I choose it as the primary key. Each batch may have many materials associated with it, including multiple shipments of the same material, hence tracking the materials that go into the batch with their WN#.

So that would mean I need to use a JOIN/junction table? Effectively relating them to each other.

My issue now, after reading this article is: Do I put a foreign key [Batch #] field into the individual WN# table and relate with a JOIN table or use another way? Also, each WN# may be in multiple batches.

 

[MickAtlas]

This is the table simplified and recreated in Access 2003.

My goal is to have the form that shows the many WN#s (materials) in the subform, also list the other fields that are part of that specific WN#, since none of these should show up twice on this form.

 

[amerifax]

Remove-Label box after text is removed

There is a light border that is still there after I remove the text that was in the box. I want to remove the ghost image, for lack of a better name, that is left.

Also all the images, fields, are locked together. I want to remove some individually,without moving everything.

I'm in the "LayOut View" of Access 15.0.4551 Office 365 Home Premium

Bob
P.S. Whats the most recent Access?

 

[MickAtlas]

Sorry Bob, I'm not understanding your question. Was it posted in the correct thread?

 

[amerifax]

Sorry. I had to use Design View for the forum.
BOb