6 Replies Latest reply on Oct 19, 2016 5:00 AM by executor

    Something like Relationship ?!

    executor

      HI

      I have database with several tables. I have table with 1200 seals for water meters. First table is named SEALS have 2 fields – “serial number” and “name”( Нame of an employee of the company. Several different names).

      Serial Number     Name

      123456                    John

      324558                    Kevin

      123456                    Ivan

      586664                  John

      321444                  John

       

      Second Table is named Measurement point and have 2 fields. ID, MP name. Each Measurement point can have 3 seals – Before, After, Door.

      At any one time must be able to show list of MP and the seals (serial numbers and name of employee) mounted on this point.

      I tried various options, but does not work.

        • 1. Re: Something like Relationship ?!
          wimdecorte

          Not enough information I'm afraid.

           

          executor wrote:

           

          Each Measurement point can have 3 seals – Before, After, Door.

           

          Where do you store these 3 seals?  As 3 fields on the measurement point table?

          If so, change that to a join table between seals and measurement point.  Each measurement point will then have 3 related seal records.

          • 2. Re: Something like Relationship ?!
            executor

            I tried to put the three seals in the table with MP but nothing. In all three fields must have the same number, ie it does not work for me.

            • 3. Re: Something like Relationship ?!
              wimdecorte

              I was suggesting to NOT put the three seals in the table with MP but use a separate table for it.  So you would have 3 tables:

               

              1) Seals

              2) Measuring Points (MP)

              3) Seals for the Measuring Point (3 records per MP)

              • 4. Re: Something like Relationship ?!
                philmodjunk

                What's the "paper relationship" between your two tables?

                 

                Is ID in Measurement point the same info as "Serial number" in Seals?

                 

                If so, a simple relationship linking ID to Serial number will allow you to add a portal to your measurement point layout that lists the seals and names for the matching records in Seals.

                • 5. Re: Something like Relationship ?!
                  executor

                  No.

                  In Serial Number in Measure point in field SN is number of Water Meter. In Seals SN is Serial number of the Seal.

                  I will try wimdecorte's way. Or something like that. Because at a time any of the seals will be replaced. I'll put fields reason for replacement and by whom is a draw.

                  • 6. Re: Something like Relationship ?!
                    executor

                    I made 3 tables.  Fist – Seals, Second - MP and Third – Used Seals. The realationships are

                    ID of MP (table MP)  -> ID of MP ( table used seals)

                    Seal serial number (table used seals) -> Seal serial Number (table seals)

                    So far so good.

                    But, I want to do something like status Seal. When the seal is already used (inserted or removed) I must not allow to enter Seal serial number in table Used seals оr to create a field that tells me  „this seal is already used”.

                    One problem is that when you join seal to the measuring point and then remove it from there. The seal is used and can no longer be used. But I have to do so that it can not be reintroduced into the table used seals.

                    I hope you understand what I ask. Sorry for my English.