10 Replies Latest reply on Jan 22, 2016 11:38 AM by siplus

    Table Help

    c0nsilience

      Hi all,

       

      So, I'm building a very basic database that will track inspections done at auctions.  There isn't a lot to it, but I'm having a difficult time with the table structure.  What I would like to be able to show is:

       

      • Number of inspections per week
      • Number of inspections per week, per auction site
      • Number of inspections per week, per employee, per auction site

       

      I would like to be able to accomplish this with the least amount of tables and complexity that is possible.  To keep it simple and clean.

       

      I've tried building it with an employee table, an auction table and an inspection table between the two (with foreign keys from both employee and auction).  I'm still not able to make it work right.

       

      Any ideas?

        • 1. Re: Table Help
          DanielShanahan

          c0nsilience wrote:

           

          I've tried building it with an employee table, an auction table and an inspection table between the two (with foreign keys from both employee and auction).  I'm still not able to make it work right.

           

          Any ideas?

          That sounds right to me.  When you say "I'm still not able to make it work right" what exactly do you mean?

           

          For the Inspections per week you can create a calc field using WeekOfYear () function and run your report sorted/subsummary on that field.

          • 2. Re: Table Help
            c0nsilience

            Daniel,

             

            I think I'm just making myself confused with the fields.  For example, the field 'Week' is just a text field and will simply say "Week 1", "Week 2", "Week 3", etc.  It isn't a date field.

             

            That being the case, would the "Week" field belong in the Inspection table or the Auction table?

             

            In Week 1, there could be 50 inspections done by 5 different inspectors at 3 different auctions. 

             

            It seems like I'm not doing a great job at getting rid of the many-to-many relationships, even though I thought I did : /

            • 3. Re: Table Help
              c0nsilience

              Screen Shot 2016-01-21 at 8.01.42 PM.png

              Here is the basic table structure.

               

              In Inspection, Week is a text field and Number of Inspections is a number field.

               

              In Auction, Site is a text field and Year is a number field.

               

              Are these three tables enough to be able to show the numbers of inspections per week; the number of inspections per week per site; the number of inspections per week, per site, per employee?

               

              I haven't had any luck making that work and I'm sure this is fairly simple.

               

              Thank you

              • 4. Re: Table Help
                DanielShanahan

                Add Auction Date to the Auction table and move Week to that table as well.  I'd have Week automatically populate with the WeekOfYear () function.

                 

                The Number of Inspections can be a summary field.  Base the report (or table view) on Inspection and do a sub summary part based on Auction::Week.  Add a new sub summary section for Auction::Site and another new one for Employee::Last.

                 

                Inspection::Number of Inspections can go on each of those sub summary lines

                • 5. Re: Table Help
                  DanielShanahan

                  See if the attached is helpful.

                  • 6. Re: Table Help
                    siplus

                    c0nsilience wrote:

                    I would like to be able to accomplish this with the least amount of tables and complexity that is possible.  To keep it simple and clean.

                    Any ideas?

                     

                    This always smells like "Use ExecuteSQL"

                    • 7. Re: Table Help
                      c0nsilience

                      Daniel,

                       

                      Yes, that absolutely helps!  I was trying to set it up with no Date field and found it impossible to do.  Kudos for breaking it down for me.  Thank you!

                      • 8. Re: Table Help
                        c0nsilience

                        Siplus,

                         

                        I've never used "ExecuteSQL", although I've read about it plenty.  I must admit, anything with "SQL" in it still seems rather intimidating to me.

                         

                        I don't want to eat up your time, but how would that be most useful for keeping the table structure minimal and clean?

                         

                        Thank you for sharing your insight!

                        • 9. Re: Table Help
                          DanielShanahan

                          Great - glad it was helpful.

                           

                          Good luck with your project.

                          • 10. Re: Table Help
                            siplus

                            instead of creating a relationship - which means a new TO and a new line in your diagram - and getting data through it, you get the same data without a relationship via SQL.