Automatically fill/update fields in linked query

[Gaztry80]

Goodmorning,

I am struggling with one of my queries. Basically their are two tables, which are used to build a query to populate data.

Table A:
ProductID = PK
CodeId
Description

Table B:
Id = PK
ProductID > multiple ProductID of the same can be exist in this table
CodeId
Color
Active > Their can only be one "True" value by ProductID. The combination productID (tableB) and Active ("True") should be unique.

First question:
I have create a inner join with the two tables.
I would prefer to use a left outer join from A to B.
However the performance of Access would be reall slowed down. Could somebody explain why this is?

Second question:
When opening the query (Please see attachement - Query1), I want to achieve the following:
1.) Filling in a new CodeId (TableA) > TableB is automatically filled with the corresponding ProductID and CodeId.
2.) When inserting the new record in TableA, the active column of Table B needs to get a standard value of "True".

I have tried to explain my problem in simplified scenario. After looking a half day yesterday, I have the feeling that I need to have fresh look on the solution
Hopefully somebody could guide me a little bit in the right direction.
If their are questions, please let me know and thank already very much for the help!

 

[silentwolf]

Hi,

first of all you should link the tables like the attached pic.
With referential integrity.

Then you don't need CodeId in the TableB!
You just keep CodeId in the TableA. The relationship link will take care of all this for you no need or better you should NOT have redandence Data.

In the queries then you get all information out of one two or more different tables just as you need them.

And to be able to assist you with the correct answers you should try to explain what you need to do with your data TableA and TableB are not really of any meaning so you should call them as what data is ment to store in those.

Like Product or Colors or what ever it may be.

P.S. I guess all of the Information should be in one table if I understand it right

Somthing like

ProdID
CodeId
Description
Color
IsActive

Not really a need for two tables..


HTH

 

[arnelgp]

a Single query will not do it, because when you add record in the query, it must complete all entry first
before all fields are saved to their respective Tables.

to automatically create new record to tableB when you add record to tableA, add a Datamacro to TableA
in design view, to add record to tableB.
you call this Datamacro on the BeforeInsert macro of tableA.

see TableA in design view, i made both datamacro1 and Beforeinsert macro.

 

[Gaztry80]

a Single query will not do it, because when you add record in the query, it must complete all entry first
before all fields are saved to their respective Tables.

to automatically create new record to tableB when you add record to tableA, add a Datamacro to TableA
in design view, to add record to tableB.
you call this Datamacro on the BeforeInsert macro of tableA.

see TableA in design view, i made both datamacro1 and Beforeinsert macro.

Maybe it is a stupid questions, but i don't see datamacro1 in the sample file?
Edit: a wait, that is a new function for me, i see it now. I am going to investigate !

 

[Pat Hartman]

Your relationship is incorrect.

REMOVE CodeID from tableB. It is redundant.

Then define a relationship between the two tables using the relationship window. Choose the option to Enforce RI.

If you want Active to default to True, then using the design view of tableB, set the required property to yes and set the default to true. If you already have data in the table, you will get an error if the existing data does not conform to the new rules.

I would prefer to use a left outer join from A to B.

I would prefer to have 15 hours of sunlight every day.
Relationships are NOT a preference. They are what they are. If the Foreign key is required, you use an inner join. If the foreign key is optional, you use a left join.


When you want to update tableB, you do NOT need to include tableA in the query. Typically we would do this with a form anyway. The main form would be bound to tableA. The subform would be bound to tableB. You would set the master/child links on the subform control to ProdID. You cannot add a record using the subform without first selecting or adding a record in the main form. Then because you have properly defined the master/child links, Access will automatically populate the ProdID field in the tableB record.

And that is the way relationships work.

 

[Gaztry80]

Thanks for helping me out, I have played arround a little bit and forgot to tell you that all my tables are linked tables.
So the Enforce RI I should arrange in SQL server, moreover the Datamacro also don't work with linked tables what I see.
Eventually I have created the following VBA code to add a record to table B after a value is submitted into the form.

Private Sub Codeid_AfterUpdate()

Dim db As Database
Dim rs As Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("dbo_TableB", dbOpenDynaset, dbSeeChanges)

rs.AddNew
rs.Fields("Active") = "-1"
rs.Update

rs.Close
Set rs = Nothing
db.Close


Me.Form.Requery

 

[arnelgp]

Datamacro also don't work with linked tables

ofcourse they won't, they are specific to Access Tables.

 

[Pat Hartman]

Referential integrity can ONLY be enforced in the database where the tables exist. NEVER where they are linked.

Sounds like you didn't actually fix the tables though. Your schema is wrong and needs to be fixed in the source database.

 

[Gaztry80]

Referential integrity can ONLY be enforced in the database where the tables exist. NEVER where they are linked.

Sounds like you didn't actually fix the tables though. Your schema is wrong and needs to be fixed in the source database.

Yes that is correct, i have now fixed it within the database !

 

[Pat Hartman]

You have fixed the schema error, the RI, or both? Precision is necessary in your language if you aspire to be a successful developer.

 

[Gaztry80]

You have fixed the schema error, the RI, or both? Precision is necessary in your language if you aspire to be a successful developer.

I have fixed the schema, made indexes and define Primary and Foreign keys in my sql server !

 

[Pat Hartman]

Great