General Query question

[Guest]

If you have 4 or 5 tables with data in them and one or more fields get
changed or updated, and you would like to up the main master table with this
data. What would be the best query to use in this instance? I have 5 tables
and approx 20 fields. If one or more of those fields get changed in the form
I would ike a query to run and update the master table. Would an Update query
be good for this use?

 

[Jeff Boyce]

Troy

I may be misinterpreting your description...

It sounds like you're saying that you want to have the same data recorded
twice, once in your "other" tables and once in your "master" table.

While that may be how you'd do it in Excel, Access, as a relational
database, offers a better way (and doesn't generally need to store the same
fact(s) more than once).

Or am I not seeing your situation...?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

What I would like to do if there is a change in one of the tables "linked"
other database. I would like to have the master table updated.

Example..

Source database
User enters the updated version 1.1 and I need it to go into the master
table. See below

Master database

1. product ID Version
12345 1
12345 1.1 new entry

 

[John Vinson]

What I would like to do if there is a change in one of the tables "linked"
other database. I would like to have the master table updated.

Well, probably you do NOT want to do this.

The whole *purpose* of the relational database model is to avoid
redundancy, which thereby avoids redundancy.

You store data ONCE, and once only, and then use Queries linking the
tables in order to bring the data from different tables together. If
you are attempting to store the data twice - once in a detail table,
and then store the same data again in a master table, you are misusing
Access! It is neither necessary nor proper to do so.

Instead, you would have a Table for each type of Entity relevant to
your data model; each piece of information would be stored once, in
its appropriate table. If you need to bring information together from
multiple tables (say for a Report) you would not copy that data into a
master table; instead, you would create a Query joining the tables
together, and base your report on that Query.

John W. Vinson[MVP]

 

[Guest]

What I would ike to do is...If their is a change in one or more of the 4
tables I have in my query. I would like it to update the master table. I know
it is redundant to do this but I am here to fix it as quick as possible (3
weeks) and have over 40 queries plus 5 forms to do. This is simple I am sure
but can not figure it out. How would you do it? Thsi should work
SELECT DEV_dbo_TblProduct.ProductId, dbo_TblProduct.ProductId,
IIf(dbo_tblProductversion.Attachments=True,"Y","N") AS Attachments,
IIf(dbo_tblProductversion.EAndOFlag=-1,"Y","N") AS EAndOFlag,
IIf(dbo_TblProductVersion.Countersignatures=-1,"Y","N") AS Countersignatures,
dbo_TblProduct.StateAbbr, dbo_tblObligationType.ProductLine,
dbo_tblObligationType.BondType, dbo_tblObligationType.CategoryID,
dbo_TblProductVersion.NewBusinessFormID, dbo_TblProductVersion.RateType,
dbo_TblProduct.ObligeeId, dbo_TblProductVersion.RenewalMethod,
dbo_TblProductVersion.BondEffectiveDate,
dbo_TblProductVersion.BondExpirationDate,
dbo_TblProductVersion.BondTermMonths, dbo_TblProductVersion.BondAmtStd,
dbo_TblProductVersion.BondAmtMin, dbo_TblProductVersion.BondAmtMax,
dbo_TblProductVersion.CancelDays, dbo_TblProduct.[Available Online],
dbo_tblSaaDocument.ClassCode, dbo_TblProductVersion.RateCode,
dbo_TblProductVersion.SpecialInstructions,
dbo_TblProduct.BondObligationDescription, dbo_TblProductVersion.RiskType,
dbo_TblProductVersion.MinCreditScore, dbo_TblProduct.ObligationTypeID
FROM dbo_tblSaaDocument RIGHT JOIN (dbo_tblObligationType INNER JOIN
(dbo_TblProductVersion INNER JOIN (DEV_dbo_TblProduct RIGHT JOIN
dbo_TblProduct ON DEV_dbo_TblProduct.ProductId = dbo_TblProduct.ProductId) ON
dbo_TblProductVersion.ProductId = dbo_TblProduct.ProductId) ON
dbo_tblObligationType.ObligationTypeID = dbo_TblProduct.ObligationTypeID) ON
dbo_tblSaaDocument.SaaDocumentID = dbo_tblObligationType.SaaDocumentID
WHERE (((DEV_dbo_TblProduct.ProductId) Is Null)) OR
(((dbo_TblProduct.ProductId) Is Not Null))
WITH OWNERACCESS OPTION;

 

[Jeff Boyce]

Troy

You've had a couple of us suggest that it would not be a good idea to
continue with the approach you've taken. My sense of your response is "I
know it isn't a good idea, but tell me how to do it."

Painting over dry rot doesn't make the wall structurally sound.

Good luck!

Jeff Boyce
Microsoft Office/Access MVP

What I would ike to do is...If their is a change in one or more of the 4
tables I have in my query. I would like it to update the master table. I
know
it is redundant to do this but I am here to fix it as quick as possible (3
weeks) and have over 40 queries plus 5 forms to do. This is simple I am
sure
but can not figure it out. How would you do it? Thsi should work
SELECT DEV_dbo_TblProduct.ProductId, dbo_TblProduct.ProductId,
IIf(dbo_tblProductversion.Attachments=True,"Y","N") AS Attachments,
IIf(dbo_tblProductversion.EAndOFlag=-1,"Y","N") AS EAndOFlag,
IIf(dbo_TblProductVersion.Countersignatures=-1,"Y","N") AS
Countersignatures,
dbo_TblProduct.StateAbbr, dbo_tblObligationType.ProductLine,
dbo_tblObligationType.BondType, dbo_tblObligationType.CategoryID,
dbo_TblProductVersion.NewBusinessFormID, dbo_TblProductVersion.RateType,
dbo_TblProduct.ObligeeId, dbo_TblProductVersion.RenewalMethod,
dbo_TblProductVersion.BondEffectiveDate,
dbo_TblProductVersion.BondExpirationDate,
dbo_TblProductVersion.BondTermMonths, dbo_TblProductVersion.BondAmtStd,
dbo_TblProductVersion.BondAmtMin, dbo_TblProductVersion.BondAmtMax,
dbo_TblProductVersion.CancelDays, dbo_TblProduct.[Available Online],
dbo_tblSaaDocument.ClassCode, dbo_TblProductVersion.RateCode,
dbo_TblProductVersion.SpecialInstructions,
dbo_TblProduct.BondObligationDescription, dbo_TblProductVersion.RiskType,
dbo_TblProductVersion.MinCreditScore, dbo_TblProduct.ObligationTypeID
FROM dbo_tblSaaDocument RIGHT JOIN (dbo_tblObligationType INNER JOIN
(dbo_TblProductVersion INNER JOIN (DEV_dbo_TblProduct RIGHT JOIN
dbo_TblProduct ON DEV_dbo_TblProduct.ProductId = dbo_TblProduct.ProductId)
ON
dbo_TblProductVersion.ProductId = dbo_TblProduct.ProductId) ON
dbo_tblObligationType.ObligationTypeID = dbo_TblProduct.ObligationTypeID)
ON
dbo_tblSaaDocument.SaaDocumentID = dbo_tblObligationType.SaaDocumentID
WHERE (((DEV_dbo_TblProduct.ProductId) Is Null)) OR
(((dbo_TblProduct.ProductId) Is Not Null))
WITH OWNERACCESS OPTION;

Well, probably you do NOT want to do this.

The whole *purpose* of the relational database model is to avoid
redundancy, which thereby avoids redundancy.

You store data ONCE, and once only, and then use Queries linking the
tables in order to bring the data from different tables together. If
you are attempting to store the data twice - once in a detail table,
and then store the same data again in a master table, you are misusing
Access! It is neither necessary nor proper to do so.

Instead, you would have a Table for each type of Entity relevant to
your data model; each piece of information would be stored once, in
its appropriate table. If you need to bring information together from
multiple tables (say for a Report) you would not copy that data into a
master table; instead, you would create a Query joining the tables
together, and base your report on that Query.

John W. Vinson[MVP]

 

[John Vinson]

What I would ike to do is...If their is a change in one or more of the 4
tables I have in my query. I would like it to update the master table. I know
it is redundant to do this but I am here to fix it as quick as possible (3
weeks) and have over 40 queries plus 5 forms to do. This is simple I am sure
but can not figure it out. How would you do it?

No, it isn't simple - because it is

W R O N G.

You will need four update queries, one from each of the redundant
tables. You will need VBA code to launch each of these queries as
appropriate.

If you want it simple and reliable, you really need to design your
tables correctly. What you are asking is "how can I make my database
bloated, hard to maintain, and inefficient - please help me do so".

Sorry, but I'll just stick to my prior advice. Spending a few hours
now will save you untold time and aggravation later.

John W. Vinson[MVP]

 

[Guest]

John,

Yes I understand exactally what you are all saying. Here is my situation.

1. I am a contractor
2. My employer gave 1 month to fix over 85 queries
3. talk about rundandancy this so-called database is it! Period!
4. I dont want to do what they are asking of me but it is there database
5. Fix it the way we ask not what or how you want to
6. It is linked to databases all over the polace then two 4 seprate SQL
servers
7. They want it fixed in the Query and no other place. They have a person
when I leave that wants to be able to pick up where she left off. TOTAL
CONFUSION I GUESS.
So please dont take me wrong. I know exactally what you are saying and agree
100%

 

[John Vinson]

What I would ike to do is...If their is a change in one or more of the 4
tables I have in my query. I would like it to update the master table. I know
it is redundant to do this but I am here to fix it as quick as possible (3
weeks) and have over 40 queries plus 5 forms to do. This is simple I am sure
but can not figure it out. How would you do it? Thsi should work

How is the data being changed in the tables? The Query as written will
not give you that information. That's the whole PROBLEM with storing
redundant information - it's all but impossible to keep the redundant
copies in synch!

All I can suggest is one of several bad options:

- Create four (or as many as needed) unselective Update queries which
update the redundant main table for all records in each child table.
Run these queries periodically.

- If the child tables are only updated using a Form (or some other
controlled manner) use the AfterUpdate event of the form to execute
the query selectively for the record currently being updated.

- If you have access to the SQL/Server instance, put Update triggers
on the tables to also update the main table. Access doesn't support
triggers (yet) but SQL does.


John W. Vinson[MVP]

 

[Larry Daugherty]

But doing it "your" way, it won't be fixed. It will instead be
thoroughly and quite sincerely screwed.

For them to tell you to "Fix" the screwed schema by putting multiple
band aids on it is kind of screwy. The design is wrong! I recommend
that you print this entire thread and take it to your boss(es).

You will very likely find that with a proper design you won't need 85
queries.

Stepping around the Sacred Cow (that person who has gotten things into
this state) should not be the primary concern. Delivering a good job
to your customer should be the concern. Tell them they were smart to
hire you in but that they should not dictate the technical issues.
They're trying to dictate that the "problem over there has to be
corrected over here".

You don't really want a job where the customer will constrain what you
can do to remediate a problem and then blame you for all the problems
they're going to have with that database as long as it retains its
present form.

HTH

 

[Guest]

Thank you for your advise. I informed this job exactly what is and will take
place after I am gone and this database got fritz. I have sat in on about 5
meeting trying to explain what needs to be done including my first meeting
after reviewing this database or should I say databases. They have 4
different ones that should only be one. Anyway thank you for your thoughts
and I will give it a try. My hands are even tied to the IT dept. Lack of
resources so we do not have access to the SQL Admin while I am here (other
projects). So I am on my own and underpressure t fix all of this in 1 1/2
months! Have 2 weeks left and about 3/4 of the way done. Supprised I am this
far!

Thanks again