7 Replies Latest reply on Jun 26, 2009 1:04 PM by RickWhitelaw

    Multiple sequential numbers per record?



      Multiple sequential numbers per record?


      My challenge is that I want each individual record to have a unique number, AND I want up to 5 fields within each record to be numbered sequentially, starting with record 1 and continuing to the last record. But, I must ignore any of those 5 fields that are empty, with the sequential numbering continuing to the next field. Is this possible?


      Thanks, Stokesy

        • 1. Re: Multiple sequential numbers per record?

          Can you explain the purpose of this? I have a feeling a better method can be found to accomplish your goal.

          • 2. Re: Multiple sequential numbers per record?

            OK. I'll try.


            This is for a community art exhibit. Each artist who enters has a unique number; artist 1, artist 2 and so on. I'm using a number field with a automatically generated serial number for this purpose.


            Then, each artist can enter up to 5 pieces; each piece needs a unique exhibit number starting at 1 and continuing up. There may be 100 peices of art by 20-30 different artists, but they would be numbered 1 through 100 sequentially as they are entered. Also, each artist may enter any number of paintings up to 5, and the numbering system must ignore those entries that are empty.


            Is that clearer?


            I know I can have a exhibit number field for each artwork entry, and hand number them as they come in-- but, I just wondered if there was a way to automate it?



            • 3. Re: Multiple sequential numbers per record?

              You should use separate tables for Artists and Exhibits, related by ArtistID. This way the exhibits will be automatically assigned a unique ExhibitID (auto-entered serial number) in the Exhibits table, as they are created.


              I'm afraid I didn't catch the difference between an exhibit and a painting.

              • 4. Re: Multiple sequential numbers per record?

                Thanks--that's what I was afraid of. I really don't want two tables.


                But, I've figured out a way to manually number the entries and I have only one glitch left. When I print out the report in table format, I don't want the empty fields to print. I know how to suppress the printing for regular fields, but it doesn't seem to work for the checkbox field, since it already has content. Is there any way around this problem?


                Thanks again for your help. I'm really not a power user. I can construct a simple dbase, but don't have enough knowledge for the more advanced concepts.

                If you would be willing to contact me offlist at stokesy48 at yahoo.com, I could e-mail my file to you so it's clearer what I'm trying to do.



                • 5. Re: Multiple sequential numbers per record?

                  I assure you it's much easier to do this correctly (i.e. with two tables) that in any other way.


                  And no, I will not assist you to "make it work somehow" with a single table. The problem is simply not interesting (to me, at least).

                  • 6. Re: Multiple sequential numbers per record?
                       OK. Thanks anyway. I do appreciate your input.
                    • 7. Re: Multiple sequential numbers per record?



                      Just to put in my two cents . . . Comment is correct. The easier way is the two-table approach. You don't have to be a power user to achieve this. I don't think there's another way to do what you want other than manually entering all the data which is just the sort of tedium a relational database can help you avoid.