Results 1 to 11 of 11
  1. #1
    LornaM is offline Novice
    Windows XP Access 2003
    Join Date
    Mar 2009
    Posts
    15

    Self Join in a Table

    My sons are starting an Internet business and they have resurrected me from retirement to develop the backend for the Web site. The Web developer will be using MySQL on Unix. I have no experience using MySQL or Unix, so I'm using Access which will be converted to MySQL when I'm done.



    So here is my question:

    I have a table called tblUsers that contains all the usual User related fields such as strUserName, strUserFirstName, etc., etc. It also has a field called lngReferralSource that indicates how the user was referred to the site. If the user was referred by another user (whose name would also be stored in the tblUsers table) then the referring user's strUserName is stored in a field in the called strReferringUser. I have accomplished this by creating a lookup (using an SQL statement) which links the strReferringUser field to the strUserName field (both of which are located in the tblUser table). This, of course, results in a dropdown box in the table for the strReferringUser field which allows the selection of the referring user's strUserName to be stored.

    Next I need to track the number of times each user refers another user to the site. I have done this by creating a query that groups on the strUserName and counts the strReferringUser.

    All of this works just fine in Access, but will it work in MySQL? Will MySQL recognize the self-join I have created for the referring user? Can the query be converted? What limitations do I need to be aware of as I'm creating the database so that the conversion will go smoothly.

    Any help or suggestions would be so greatly appreciated.

    Thanks in advance

  2. #2
    Matrix's Avatar
    Matrix is online now Admin
    Windows 10 Access 2010 64bit
    Join Date
    Jan 2005
    Posts
    517
    I'm a little confused about your project. Unlike Access, MySQL only stores data, which language will you use to develop the web pages? Only table structure and data can be transferred into MySQL, what about the front-end?

  3. #3
    LornaM is offline Novice
    Windows XP Access 2003
    Join Date
    Mar 2009
    Posts
    15
    We have a web programmer who is handling the front end. All I'm responsible for is developing the back end. I don't know what language he will be using. Do I need to know this in order to properly develop the back end?

  4. #4
    Matrix's Avatar
    Matrix is online now Admin
    Windows 10 Access 2010 64bit
    Join Date
    Jan 2005
    Posts
    517
    Sorry, I'm still not clear. Do you create some forms with Access for inputting/editing data? Since you can only transfer the data into MySQL, if you have any user-interface and controlling code, how do you deal with them?

    Let me ask in a different way, your Access application has two parts:

    Part 1: All user interface (forms, button, database queries, controlling code, etc. )
    Part 2: Data (Table structure and data)
    Part 1 will be used to input/edit Part2.

    After you finish your project, Part 2 will be transferred into MySQL (Part 3). How do you deal with Part 1? Simply discard it or change it to another language which can modify Part 3?

  5. #5
    LornaM is offline Novice
    Windows XP Access 2003
    Join Date
    Mar 2009
    Posts
    15
    I am not peersonally creating any of the forms or code in Access, because I have been assuming that they can not be converted from Access to MySQL. Is that right? I have only created one query for counting the number of referrals, but I don't know if that can be converted over either. I guess my question lies in the fact that I'm not sure how much I can do on the Access side that can be converted to MySQL. I guess since you said that MySQL only stores the data, then all the data manipulation that might be done through a query would be done through code on the Web pages. If so, then I wouldn't need to worry about creating any queries at all from within Access. Is that right? Sorry I've had such a hard time making myself clear. I hope you understand now what I'm wanting to know. Thanks for being so patient in working with me.

  6. #6
    LornaM is offline Novice
    Windows XP Access 2003
    Join Date
    Mar 2009
    Posts
    15
    Our Web guy will be creating all of the user interface.

  7. #7
    Matrix's Avatar
    Matrix is online now Admin
    Windows 10 Access 2010 64bit
    Join Date
    Jan 2005
    Posts
    517
    Right, you shouldn't do anything except defining the tables, they won't be transferred anyway.

    Since you will only define the table structures, I suggest you work on MySQL directly. Install MySQL on your desktop, write a SQL script file with 'create table' queries. When you need to setup your database on the server, you only need to execute a MySQL command against your SQL file, all tables will be created.

    Post here if you need any further assistance.

  8. #8
    LornaM is offline Novice
    Windows XP Access 2003
    Join Date
    Mar 2009
    Posts
    15
    Thanks for you help

  9. #9
    Matrix's Avatar
    Matrix is online now Admin
    Windows 10 Access 2010 64bit
    Join Date
    Jan 2005
    Posts
    517
    I forgot your original question . You don't have to worry about the query, as long as the tables are reasonable, there won't be any problems with MySQL. Even if a query in Access can't be accomplished with one MySQL query (Very probably the other way around), you can always use more than one queries. Actually many popular web programs, such as vBulletin used by this board, often have several even a dozen queries for one page, amazingly they can be loaded very fast.

    Regarding the table fields, I suggest you store the referrer's id instead of user name, something like:
    ...
    integer user_id,
    integer referrer_id,
    ....

    The drop-down list doesn't seem to be practical unless you only have a dozen users, just let the user to input the referrer's user name, but this would be the web programmer's work.

  10. #10
    LornaM is offline Novice
    Windows XP Access 2003
    Join Date
    Mar 2009
    Posts
    15
    Actually the field called strUserName is the key field for the Users table. It is a unique username which the user creates and will be used, along with a password, to access the site. I did it this way to ensure that the username will not be duplicated in the system, if it is, they will be prompted to create a different username.

    Thanks again for all your help.

  11. #11
    Matrix's Avatar
    Matrix is online now Admin
    Windows 10 Access 2010 64bit
    Join Date
    Jan 2005
    Posts
    517
    Oh, I got that. But I strongly suggest you always use an Integer field as your primary key, your web programmer will appreciate that, using a String to identify a record may give you lots of trouble in the future. A company I worked for has a rule that every table must have one and only one Integer primary key.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Join Expression Not Supported
    By ubsman in forum Queries
    Replies: 3
    Last Post: 04-30-2009, 08:36 PM
  2. Update with LEFT JOIN
    By mcarthey in forum Access
    Replies: 1
    Last Post: 08-27-2008, 10:49 AM
  3. Replies: 1
    Last Post: 09-19-2006, 11:07 AM
  4. Dynamic Query Outer Join Problem
    By mjack003 in forum Queries
    Replies: 0
    Last Post: 07-21-2006, 01:07 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums