Alpha Software Mobile Development Tools:   Alpha Anywhere    |   Alpha TransForm subscribe to our YouTube Channel  Follow Us on LinkedIn  Follow Us on Twitter  Follow Us on Facebook

Announcement

Collapse

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

Join operation with no common field

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

    Join operation with no common field

    Can I set up a Join or other type of operation if there is no common field? I have a table with a date field and another table with customer information. I would like to create a table with all of the dates for each customer. I cannot find the right genie to set this up.

    DatesOfEvents
    Date Event....
    1/2/2010
    2/3/2010
    4/7/2010

    Customers
    CID Name....
    1
    2
    3

    New Table
    Date CID ...
    1/2/2010 1
    1/2/2010 2
    1/2/2010 3
    2/3/2010 1
    2/3/2010 2
    2/3/2010 3
    4/7/2010 1
    4/7/2010 2
    4/7/2010 3


    Thanks,

    #2
    Re: Join operation with no common field

    If there is no field in common, then how is alpha to know (or you for that matter) which records from the first table to marry (or join) to which ones from the second table?

    At best, this will result in a haphazard joining, but theoretically speaking, if it can be done this way, it could end in an endless loop of joining each record from table A to each record in tableB. Not endless.. but close.

    Got to have something in common or else don't even think about marriage.

    Sounds to me, your first table needs an additional field: CID.
    Last edited by G Gabriel; 08-06-2009, 04:02 PM.

    Comment


      #3
      Re: Join operation with no common field

      Try my attachment. Run the script and check the joined table.
      There can be only one.

      Comment


        #4
        Re: Join operation with no common field

        G. Gabriel:
        Thanks for the comment.
        I can run a query in Access
        SELECT tblDatesOfEvents.Dates, tblCustomers.CID
        FROM tblDatesOfEvents, tblCustomers;
        and get what I want.

        Comment


          #5
          Re: Join operation with no common field

          Originally posted by howardmeyer View Post
          Can I set up a Join or other type of operation if there is no common field? I have a table with a date field and another table with customer information. I would like to create a table with all of the dates for each customer. I cannot find the right genie to set this up.

          DatesOfEvents
          Date Event....
          1/2/2010
          2/3/2010
          4/7/2010

          Customers
          CID Name....
          1
          2
          3

          New Table
          Date CID ...
          1/2/2010 1
          1/2/2010 2
          1/2/2010 3
          2/3/2010 1
          2/3/2010 2
          2/3/2010 3
          4/7/2010 1
          4/7/2010 2
          4/7/2010 3


          Thanks,
          Use a join where the linking fields are expressions that are equal.

          Or build a set with the linking fields are expressions that are equal.

          ie
          "A" - customer table
          "A" - datetable
          Al Buchholz
          Bookwood Systems, LTD
          Weekly QReportBuilder Webinars Thursday 1 pm CST

          Occam's Razor - KISS
          Normalize till it hurts - De-normalize till it works.
          Advice offered and questions asked in the spirit of learning how to fish is better than someone giving you a fish.
          When we triage a problem it is much easier to read sample systems than to read a mind.
          "Make it as simple as possible, but not simpler."
          Albert Einstein

          http://www.iadn.com/images/media/iadn_member.png

          Comment


            #6
            Re: Join operation with no common field

            along with Al's suggestion see if this helps.

            Comment


              #7
              Re: Join operation with no common field

              Howard:
              Don't know how Access figures out the matching process, so I can't comment on that but just looking at the sample you provided, there seems to be a constant pattern there where each one of the customers is represented in each date. I am not sure if this is reflective of your actual data. If it is, then yes, you can join these tables without a field in common because here you have sort of an "expression" in common.

              You could certainly write a script wherein for each one of these dates you add each one of the customers.

              I don't want to keep building assumptions on top of assumptions, But assuming again the above is correct, here is a very simple way to accomplish what you want:
              Create a set to link both tables. First, add a one character field to each table and leave it blank then link the two tables with this common blank field. What happens now is each record from the parent (each date) will bring all three customers.
              In the Control Panel, right-click that set and create an ad-hoc browse. This browse is a composite browse that will have three records for each date: one for each customer. Click the design button then save that browse and here is your final "table" with the records joined.
              Last edited by G Gabriel; 08-06-2009, 06:17 PM.

              Comment


                #8
                Re: Join operation with no common field

                Thanks for the help. I used your suggestions, adding a new field MATCH in both tables and then joined.

                Comment


                  #9
                  Re: Join operation with no common field

                  Originally posted by howardmeyer View Post
                  Thanks for the help. I used your suggestions, adding a new field MATCH in both tables and then joined.
                  Are these tables dbfs?

                  The expression works - no need to add a field to each table.

                  Did you try that?
                  Al Buchholz
                  Bookwood Systems, LTD
                  Weekly QReportBuilder Webinars Thursday 1 pm CST

                  Occam's Razor - KISS
                  Normalize till it hurts - De-normalize till it works.
                  Advice offered and questions asked in the spirit of learning how to fish is better than someone giving you a fish.
                  When we triage a problem it is much easier to read sample systems than to read a mind.
                  "Make it as simple as possible, but not simpler."
                  Albert Einstein

                  http://www.iadn.com/images/media/iadn_member.png

                  Comment


                    #10
                    Re: Join operation with no common field

                    I am curious ....how can there be no field in common when
                    for each customer
                    is a criteria??? Seems to me that Customer is the common field---why else even do the join?? May have missed something but think not.
                    Mike
                    __________________________________________
                    It is only when we forget all our learning that we begin to know.
                    It's not what you look at that matters, it's what you see.
                    Henry David Thoreau
                    __________________________________________



                    Comment


                      #11
                      Re: Join operation with no common field

                      Originally posted by MikeC View Post
                      I am curious ....how can there be no field in common when is a criteria??? Seems to me that Customer is the common field---why else even do the join?? May have missed something but think not.
                      There is not a customer/id in the date table.

                      So every customer record needs to link to every record in the date table.
                      Al Buchholz
                      Bookwood Systems, LTD
                      Weekly QReportBuilder Webinars Thursday 1 pm CST

                      Occam's Razor - KISS
                      Normalize till it hurts - De-normalize till it works.
                      Advice offered and questions asked in the spirit of learning how to fish is better than someone giving you a fish.
                      When we triage a problem it is much easier to read sample systems than to read a mind.
                      "Make it as simple as possible, but not simpler."
                      Albert Einstein

                      http://www.iadn.com/images/media/iadn_member.png

                      Comment


                        #12
                        Re: Join operation with no common field

                        Thanks Al,
                        Looking closer at his example of what was wanted fits now...and we both know linking by expression will do as is wanted.

                        I was thinking about and then got confused by the why or of "what benefit" this could possibly be, when if I had simply just looked at the surface of what was asked, the solution made sense. :)
                        Mike
                        __________________________________________
                        It is only when we forget all our learning that we begin to know.
                        It's not what you look at that matters, it's what you see.
                        Henry David Thoreau
                        __________________________________________



                        Comment


                          #13
                          Re: Join operation with no common field

                          This matter is open for debate. I used to use a dummy expression in such a situation then reverted to linking with one character blank field. The reason being, it's much more streamlined to lookup blank fields as opposed to an expression no matter how dummy it is. Alpha does not know dumb from dumber. You give it an expression, it will evaluate it regardless and evaluates it for each and every record. Seems to me linking with a field value is much more efficient than with expression, but I wouldn't say Vanilla is better than Chocolate.

                          Comment

                          Working...
                          X