The Alpha Software Forum Participation Guidelines

The Alpha Software Forum is a free forum created for Alpha Software Developer Community to ask for help, exchange ideas, and share solutions. Alpha Software strives to create an environment where all members of the community can feel safe to participate. In order to ensure the Alpha Software Forum is a place where all feel welcome, forum participants are expected to behave as follows:
  • Be professional in your conduct
  • Be kind to others
  • Be constructive when giving feedback
  • Be open to new ideas and suggestions
  • Stay on topic

Be sure all comments and threads you post are respectful. Posts that contain any of the following content will be considered a violation of your agreement as a member of the Alpha Software Forum Community and will be moderated:
  • Spam.
  • Vulgar language.
  • Quotes from private conversations without permission, including pricing and other sales related discussions.
  • Personal attacks, insults, or subtle put-downs.
  • Harassment, bullying, threatening, mocking, shaming, or deriding anyone.
  • Sexist, racist, homophobic, transphobic, ableist, or otherwise discriminatory jokes and language.
  • Sexually explicit or violent material, links, or language.
  • Pirated, hacked, or copyright-infringing material.
  • Encouraging of others to engage in the above behaviors.

If a thread or post is found to contain any of the content outlined above, a moderator may choose to take one of the following actions:
  • Remove the Post or Thread - the content is removed from the forum.
  • Place the User in Moderation - all posts and new threads must be approved by a moderator before they are posted.
  • Temporarily Ban the User - user is banned from forum for a period of time.
  • Permanently Ban the User - user is permanently banned from the forum.

Moderators may also rename posts and threads if they are too generic or do not property reflect the content.

Moderators may move threads if they have been posted in the incorrect forum.

Threads/Posts questioning specific moderator decisions or actions (such as "why was a user banned?") are not allowed and will be removed.

The owners of Alpha Software Corporation (Forum Owner) reserve the right to remove, edit, move, or close any thread for any reason; or ban any forum member without notice, reason, or explanation.

Community members are encouraged to click the "Report Post" icon in the lower left of a given post if they feel the post is in violation of the rules. This will alert the Moderators to take a look.

Alpha Software Corporation may amend the guidelines from time to time and may also vary the procedures it sets out where appropriate in a particular case. Your agreement to comply with the guidelines will be deemed agreement to any changes to it.

Bonus TIPS for Successful Posting

Try a Search First
It is highly recommended that a Search be done on your topic before posting, as many questions have been answered in prior posts. As with any search engine, the shorter the search term, the more "hits" will be returned, but the more specific the search term is, the greater the relevance of those "hits". Searching for "table" might well return every message on the board while "tablesum" would greatly restrict the number of messages returned.

When you do post
First, make sure you are posting your question in the correct forum. For example, if you post an issue regarding Desktop applications on the Mobile & Browser Applications board , not only will your question not be seen by the appropriate audience, it may also be removed or relocated.

The more detail you provide about your problem or question, the more likely someone is to understand your request and be able to help. A sample database with a minimum of records (and its support files, zipped together) will make it much easier to diagnose issues with your application. Screen shots of error messages are especially helpful.

When explaining how to reproduce your problem, please be as detailed as possible. Describe every step, click-by-click and keypress-by-keypress. Otherwise when others try to duplicate your problem, they may do something slightly different and end up with different results.

A note about attachments
You may only attach one file to each message. Attachment file size is limited to 2MB. If you need to include several files, you may do so by zipping them into a single archive.

If you forgot to attach your files to your post, please do NOT create a new thread. Instead, reply to your original message and attach the file there.

When attaching screen shots, it is best to attach an image file (.BMP, .JPG, .GIF, .PNG, etc.) or a zip file of several images, as opposed to a Word document containing the screen shots. Because Word documents are prone to viruses, many message board users will not open your Word file, therefore limiting their ability to help you.

Similarly, if you are uploading a zipped archive, you should simply create a .ZIP file and not a self-extracting .EXE as many users will not run your EXE file.
See more
See less

Union Query Equal?

  • Filter
  • Time
  • Show
Clear All
new posts

  • Union Query Equal?

    Hello Group,

    I have 2 tables with identical field layouts (except for auto-increment starting values) that I need to Join together for certian forms. They need to stay separate due to the fact that 1 table gets shared with other users and the other doesn't.

    In Access I do this with a union query that puts both tables together through a query.

    Uppending and Joining wont work. Does Alpha have a union feature?

    Thanks, Newbie Bill

  • #2
    RE: Union Query Equal?

    If I understand your need, you want to make a set, then base the form(s) on the set.

    - Steve


    • #3
      RE: Union Query Equal?

      Hi Steve, Thanks for the reply.

      This is hard for me to explain, I'll try to illustrate.

      File 1:
      ID Name
      1 Bill
      2 Tom
      3 Charlie

      File 2:
      ID Name
      1001 George
      1002 Harry
      1003 Mike

      Combined: THIS IS WHAT I'M AFTER
      ID Name
      1 Bill
      2 Tom
      3 Charlie
      1001 George
      1002 Harry
      1003 Mike

      The only way I can see to do this is to perform a Join operation(?) (creating a new table) each time a new entry in either file 1 or file 2 accures.

      In Access my form would be based on a union query so the 2 tables would always be 'Joined' for the purpose of the form but still be separate entities. Any Adds or Deletes or Edits, is seen in the resulting Union Query without any further interaction by user or program.

      The reason why I need to do this is due to the fact that file 1 is 'Shared', sent to other off-site users of the db. While file 2 always stays with that individual user.

      I hope I haven't confused the issue even further.

      Thanks for the interest in my probem.


      • #4
        RE: Union Query Equal?

        I've been thinking about this on and off all day, and don't have a solution to offer to you. I can envision a form that works with arrays and variables, drawing off of and being able to update either/both tables, but it wouldn't be a very simple solution.

        A sort of half way solution would be to display two forms -- one from each table -- side by side. You could pretty easily add some XDialog control to help the user find records, regardless of which table they're in.

        One last thought: I have a feeling there's a way you could make a set, with some atypical linking that might make it appear that the records from both tables look like they're in a single table. I'd have to experiment a bit, but something tells me there's a way to do this here.

        Let us know / good luck.

        - Steve


        • #5
          RE: Union Query Equal?

          Why not have one table with an additional field (perhaps logical) which allows you to indicate which records get sent, and which do not?

          That way you only have to enter data into one table, and when you copy the records to send the table, you only copy those with the additional field marked T

          Jay Talbott
          Jay Talbott
          Lexington, KY


          • #6
            RE: Union Query Equal?

            Hi Bill,

            Let me be the first to show my ignorance here. I guess I am not following exactly what you are trying to accomplish with the form itself.

            Is the form supposed to display all the records in the two tables? Wouldn't that be more of a report than a form?

            Is the form supposed to be used for data entry purposes? If the tables actually have different users then how would I know which of the name fields I want to add to? If this is for data entry, edit/delete purposes I am to assume the person doing the data entry is aware that they are two completely different tables and would know which field they are going to input the data in. If that is true, why do they need to be on one form?

            With the different users in the two tables that you are describing, there is nothing that actually links the two tables together. One table really has nothing to do with the other table if I am understanding you correctly.

            Sorry for my ignorance, but I am confused with this one :(

            #1 Designs By Pagecrazy


            • #7
              RE: Union Query Equal?

              How about this?

              1) Create a third table with a single field. Enter one record. Put a dummy value in it.

              2) Add new fields to File1 and File2. Put the same dummy value in the new field throughout both tables.

              3) Create a new set in which File3 is primary. Link 1:N to File1 and link 1:N to File2. Arrange this so the set has two child tables and zero grandchild tables.

              4) Create a form based on the new set. Put the dummy field on the form but hide it. Drop embedded browses for File1 and File2 on the same form.

              -- tom


              • #8
                RE: Union Query Equal?

                Let me thank everyone for there thoughts on this. It is a difficult process, it was difficult in Access as well.

                Let me try to explain why the suggested solutions will not work.

                file1: IN-Region Speakers
                file2: OUT-of-Region Speakers

                The reason for the 2 files is to prevent the posibility of conflicting ID's. When the program was a stand-a-lone, there were no conflicts as each user created and maintain his own data.

                The problems begin when 'sharing' the file1 data between users. This happens when user A gives a diskette created by the program to user B who imports that data and it overwrites his file1. File1 contains the majority of the like data. This happens periodically when new IN-Region lists are compiled.

                During the interum, all users may add speakers to File1 (IN's) and file2 which contains a list of additional speakers that come from outside the region. This list is unique to each user and never leaves his machine.

                Can you start to see the problem? The probability is very real of conflicting Auto Increment files. Thus the 2 files. The problem goes deeper than that tool. Each speaker has related files that must maintain their unique relationships.

                When a user brings up the scheduling form I had a subform that showed all the speakers that gave that talk either IN or OUT of the region (the union query result).

                The actual assignment record would become 'static' because I could not know when a user would update his File1 (IN region) list thus getting different ID/ auto increment numbers....Instead of 'linking' to the speakers record I read the data and write it to the asssignment record, thus becoming static.

                Here is what I'm thinking for possible solutions, please let me know what you think.

                1 - abandon the subform of unioned speakers and just go with 2 subforms both IN and OUT speakers for selection. This is too much like giving up so it is a last resort.

                2 - Go with 1 file but not use auto-increment fields. I have tested random numbers in range of 10^15 power (see code below). The odds of creating a duplicate should virtually be null. Add a logical field for IN/OUT. I think this should provide the uniqueness I need to prevent ID conflicts while keeping the 1 subform of speakers. It would now be fairly easy to export only the IN's records (as James has suggested).

                I have probably totally confused the issue so much that nobody even made it to the bottom of this response. But I do thank everyone for your reply's.

                I'm new to Alpha (obviously) and appreciate the help this forum gives.

                Thanks, Bill

                dim loops as n
                dim t as p
                dim record_num as n

                'goto the current record #
                record_num = recno()

                'if not empty (true), then do not calc
                if t.Key " 0 then
                end if

                'Calc the random number 1 to 10^16
                loops = val(right(str(int(toseconds(time()))),3))
                for i = 1 to loops
                randoutput = rand() * 100000000000000000

                'assign to field


                • #9
                  RE: Union Query Equal?

                  Hi Bill,

                  I am still a bit confused but I am going to take a stab at a possible solution. I have 5 different users that are updating file1 and file2. It is possible that all 5 users will be updating information for a specific speaker in their own table so they need to import the data from the new table without overriding their table.

                  Why not use a different auto increment sequence for each user for the file that is being copied and moved between users, ie user A auto increment value starts with A0000001, user B auto increment value starts with B0000001, etc.

                  Import the mobile table as a 3rd table without appending the users 2 files. On the individual users machine set your query to display all records in file1, then display all records in file2, then compare the speakers names in file3 and if it does not match any from the first 2 files, then display those results.

                  You could also append the records with this filter to the local users file2 if you wanted them all in the one table rather than maintaining a 3rd file.

                  By using different auto increment starting values for each user you will not have duplicate id's, therefore you will not lose the additional data that is tied uniquely to that speaker by the auto increment field. You may still have the duplicate speakers but you can filter them out.

                  Your key here of course will be that you will have to setup the queries on the various users machines that are specific to their auto increment seed values, but this should be a one time setup.

                  Good luck.
                  #1 Designs By Pagecrazy


                  • #10
                    RE: Union Query Equal?

                    I would do something like what Cheryl is talking about.The differentiator would be associted with a user ID and placed in a variable at login time.Forget the autoincrement field rule - write your own routine for this ID creation getting the next number from a table.
                    I would have only one file with an "in" or "out" indicator.That would make it a trivial matter to query and only see one's own entries if desirable.


                    • #11
                      RE: Union Query Equal?

                      I would have put all into 1 table and had a designaion for in or out spesker to differentiate. users would only see their own input data as they would sign on at the beginning and their sign on (checked by their own record in a user table) would make a variable as muser and it would have to correspond with the same user name field in the table with the speakers.

                      I do this very thing with my biggest current program. 3 user network with many more actual users. none can see anything they are not supposed to.

                      Dave Mason
                      Skype is dave.mason46


                      • #12
                        RE: Union Query Equal?

                        Hello Steve, James, Tom, Cheryl, John and Dave,

                        I really do appreciate the the time you guys have spent concidering this problem.

                        You have given me some good points to examine. I'm sure I will be able to get through this problem with some of your suggestions.

                        Without this group, it would be very difficult to get started using Alpha, which I am determined to do, if I can get enought time away from Access to progress.

                        Thanks again,