"Allen Browne" <AllenBrowne@SeeSig.Invalid> ha scritto nel messaggio
news:4189b894$0$6546$5a62ac22@per-qv1-newsreader-01.iinet.net.au...[color=blue]
> Laura, what bothers me is the non-normalized data structure, where you[/color]
have[color=blue]
> one record dependent on another. That *is* the problem[/color]
Oh J thought the the problem was be or not to be!!!
Damn J've to study better Access...
[color=blue]
> that you are trying
> to solve by forcing the update of the other record when one changes. To[/color]
me,[color=blue]
> that approach would be unacceptable, e.g. if the write fails for some[/color]
reason[color=blue]
> (e.g. another user is editing that record), your database gradually[/color]
becomes[color=blue]
> more and more wrong over time.
>
> Some kind of junction table(s) between 2 instances of the company table
> seems to be a more reliable approach.
>
> Thanks for the comments re the website. Like many of the regular
> contributors here, I enjoy empowering people to achieve things for
> themselves.[/color]
Sorry for jocking, J'm completly agree with you answer
MAssimiliano
[color=blue]
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia.
> Tips for Access users -
http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "Laura" <musicloverlch@(removethis)yahoo.com> wrote in message
> news:0Rhid.11238$IE1.2300@fe1.texas.rr.com...[color=green]
> > There isn't a need for an employees table. I just need to know if[/color][/color]
common[color=blue][color=green]
> > employees may exist between the two companies. I don't need a list of
> > them.
> >
> > Here is the actual update query that occurs when the check box is[/color][/color]
checked:[color=blue][color=green]
> >
> > UPDATE tblCombinedPlans SET tblCombinedPlans.CommonEEs =
> > [Forms]![frmgroups].[Subform BasicCase].[Form].[AddlPlans
> > Subform].[Form].[CommonEEs], tblCombinedPlans.Census =
> > [Forms]![frmgroups].[Subform BasicCase].[Form].[AddlPlans
> > Subform].[Form].[Census]
> > WHERE (((tblCombinedPlans.GA_Number)=[Forms]![frmgroups].[Subform
> > BasicCase].[Form].[AddlPlans Subform].[Form].[C_GA_Number]) AND
> > ((tblCombinedPlans.PlanNum)=[Forms]![frmgroups].[Subform
> > BasicCase].[Form].[AddlPlans Subform].[Form].[C_PlanNum]) AND
> > ((tblCombinedPlans.PYE)=[Forms]![frmgroups].[Subform
> > BasicCase].[Form].[AddlPlans Subform].[Form].[C_PYE]) AND
> > ((tblCombinedPlans.C_GA_Number)=[forms]![frmGroups]![GA_Number]) AND
> > ((tblCombinedPlans.C_PlanNum)=[forms]![frmGroups]![PlanNum]) AND
> > ((tblCombinedPlans.C_PYE)=[forms]![frmGroups]![PYE]));
> >
> > The fields on the table are:
> >
> > These three denote the company
> > GA_Number
> > PlanNum
> > PYE
> >
> > These three denote the linked or 'common' company.
> > C_GA_Number
> > C_PlanNum
> > C_PYE
> >
> > These are the two checkboxes.
> > CommonEEs
> > Census
> >
> > As you can see from the query above, I made GA_Number = C_GA_Number,[/color][/color]
etc.[color=blue][color=green]
> > This allowed me to reverse the values.
> >
> > I was thinking that maybe I should use vb. The table is relatively[/color][/color]
static[color=blue][color=green]
> > except for the 2 checkboxes. I haven't worked it out completely yet,[/color][/color]
but[color=blue][color=green]
> > I think I could store the 6 values of a record, then reverse them, and
> > create a dynamic update query.
> >
> > Do you think that would work?
> >
> > Thanks,
> > Laura
> >
> > P.S. I love, love, love your website. I found it more than 6 years ago
> > when I was forced to teach myself Access and couldn't get my mind around
> > how to do a dynamic dlookup. You are wonderful at explaining things.
> > Thank ypu so much for all the help over the years.
> >
> >
> > "Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
> > news:4189a0cb$0$6554$5a62ac22@per-qv1-newsreader-01.iinet.net.au...[color=darkred]
> >> Laura, this sounds like a maintenance nightmare!
> >>
> >> You need Access to calculate this for you at runtime, and you could
> >> achieve that if you had these 3 tables:
> >>
> >> Company table:
> >> CompanyID primary key
> >>
> >> Employee table:
> >> EmployeeID primary key
> >>
> >> CompanyEmployee table:
> >> CompanyID foreign key to Company.CompanyID
> >> EmployeeID foreign key to Employee.EmployeeID
> >>
> >>
> >> Now create two queries:
> >>
> >> 1. Create a query into the CompanyEmployee table.
> >> Drag a 2nd copy of the CompanyEmployee table into the grid.
> >> Access will alias it as CompanyEmployee_1.
> >>
> >> 2. In the upper pane, create a join between CompanyEmployee.EmployeeID
> >> and CompanyEmployee_1.EmployeeID. If you see a join between
> >> CompanyEmployee.CompanyID and CompanyEmployee_1.CompanyID, delete that
> >> line.
> >>
> >> 3. Drag CompanyEmployee.CompanyID into the query grid.
> >> Type this into the Field row:
> >> CommonCompanyID:[CompanyEmployee_1].[CompanyID]
> >> In the Criteria row under this field, enter:
> >> <> CompanyEmployee.CompanyID.
> >>
> >> 4. Save the query with the name qryEmployeeBoth. Close.
> >> This query shows each matching company that has shared employees, and
> >> lists who they are.
> >>
> >>
> >> 1. Create a new query into the Company table and also the query you[/color][/color][/color]
just[color=blue][color=green][color=darkred]
> >> saved. If you don't see a line joining Company.CompanyID to
> >> qryEmployeeID, then drag the one field onto the other. Double-click the
> >> join line. Access pops up a dialog offereing 3 choices. Choose the one
> >> that says:
> >> All records from Company, and any matches from ...
> >>
> >> 2. Depress the Totals button on the toobar.
> >> Access adds a Total row to the grid.
> >>
> >> 3. Drag Company.CompanyID and qryEmployeeBoth.CommonCompanyID into the
> >> grid. Accept Group By under these fields.
> >>
> >> 4. Drag qryEmployeeBoth.EmployeeID into the grid. In the Total row,
> >> choose Count.
> >>
> >> This query lists every company matched against any other companies that
> >> have shared employees, and the number of shared employees.
> >>
> >> This willbe the SQL for the 2 queries:
> >> SELECT CompanyEmployee.CompanyID, CompanyEmployee.EmployeeID,
> >> CompanyEmployee_1.CompanyID AS CommonCompanyID
> >> FROM CompanyEmployee INNER JOIN CompanyEmployee AS
> >> CompanyEmployee_1 ON CompanyEmployee.EmployeeID =
> >> CompanyEmployee_1.EmployeeID
> >> WHERE CompanyEmployee_1.CompanyID <> [CompanyEmployee].[CompanyID];
> >>
> >>
> >> SELECT Company.CompanyID, qryEmployeeBoth.CommonCompanyID,
> >> Count(qryEmployeeBoth.EmployeeID) AS CountOfEmployeeID
> >> FROM Company LEFT JOIN qryEmployeeBoth
> >> ON Company.CompanyID = qryEmployeeBoth.CompanyID
> >> GROUP BY Company.CompanyID, qryEmployeeBoth.CommonCompanyID;
> >>
> >>
> >> "Laura" <musicloverlch@(removethis)yahoo.com> wrote in message
> >> news:fOfid.9910$IE1.6745@fe1.texas.rr.com...
> >>> Here's the situation:
> >>>
> >>> I'm trying to use an update query to copy data from one row to[/color][/color][/color]
another.[color=blue][color=green][color=darkred]
> >>> Here is the situation:
> >>>
> >>> I have 5 companies that are linked to each other. I need to show all[/color][/color][/color]
5[color=blue][color=green][color=darkred]
> >>> companies on a form. 3 of the companies have common employees. I have[/color][/color][/color]
a[color=blue][color=green][color=darkred]
> >>> table that looks like this:
> >>>
> >>>
> >>> Row ID Company Common Company Common EEs (checkboxes)
> >>> 1 A B
> >>> 2 A C
> >>> 3 A D
> >>> 4 A E
> >>> 5 B A
> >>> 6 B C
> >>> 7 B D
> >>> 8 B E
> >>> 9 C A
> >>> 10 C B
> >>> 11 C D
> >>> 12 C E
> >>> 13 D A
> >>> 14 D B
> >>> 15 D C
> >>> 16 D E
> >>>
> >>> and so on.
> >>>
> >>> If I were to look at Company A in a form, in the form header I have:
> >>>
> >>> Company A
> >>>
> >>> In the detail section (as a continuous form) I show it's linked
> >>> companies:
> >>>
> >>> Company B
> >>> Company C
> >>> Company D
> >>> Company E
> >>>
> >>> With a checkbox next to each name in the detail section.
> >>>
> >>> In this scenario, company B and D have common employees with company[/color][/color][/color]
A.[color=blue][color=green][color=darkred]
> >>> So, when you check the box next to B, I have a query that will check[/color][/color][/color]
the[color=blue][color=green][color=darkred]
> >>> row with Company A and Company B (row 1) and the row with Company B[/color][/color][/color]
and[color=blue][color=green][color=darkred]
> >>> Company A (row 5). The same with Company A and Company D. When you
> >>> check the box next to Company D, the update query with update row 3 (A
> >>> and D) and row 13 (D and A).
> >>>
> >>> So, now company A and company B have the same info. Company A and
> >>> company D have the same info. BUT, company B and company D (rows 7[/color][/color][/color]
and[color=blue][color=green][color=darkred]
> >>> 14) also need to be checked and this isn't happening. How do I update
> >>> those rows??
> >>>
> >>> Thanks in advance,
> >>>
> >>> Laura[/color][/color]
>
>[/color]
