Results 1 to 5 of 5
  1. #1
    Join Date
    May 2012
    Posts
    1

    Unanswered: Database model problem from access to MSSQL server 2005

    Good Day,

    I am a C# programmer but i have a problem with a database model on MSSQL server 05. I have a client who told me

    to rebuild his database from access to MSSSQL server 05 and actually i did but i found out that the database model

    for the access can't work on the MSSQL server 05. Its a retail management database mainly for his small business.

    The database in access consist of the following tables and columns below:

    Customer Registration

    Customer_id

    Customer_surname

    Other_name

    Phone_number (P.K)

    Address



    GRN

    DATE datatype date

    PRODUCT datatype varchar (F.K Product registration.product)

    MAKE datatype varchar (F.K MAKE registration.make)

    MODEL datatype varchar (F.K MODEL registration.model)

    SERIAL datatype varchar (F.K Receivable.SERIAL)



    MAKE registration

    make_id indexed accept nulls. datatype int

    make (P.K.) datatype varchar



    MODEL registration

    Model_id indexed accept nulls. datatype int

    Model (P.K.) datatype varchar



    Product registration

    product_id indexed accept nulls. datatype int

    product (P.K.) datatype varchar



    Receivable

    STAFF datatype varchar (F.K) STAFF REGISTRATION.NAME

    DATE datatype date

    PHONE datatype varchar

    SERIAL (P.K.) datatype varchar

    CHECK (P.K.) datatype varchar

    Selling price

    PRODUCT datatype varchar (F.K Product registration.product)

    MAKE datatype varchar (F.K MAKE registration.make)

    MODEL datatype varchar (F.K MODEL registration.model)

    PRICE datatype int

    Staff Registration

    STAFF ID datatype int Indexed allows null.

    NAME datatype varchar (P.K.)



    This is the database structure of the Access database, now the problem am having is a relationship model that could

    query this data :

    SELECT [CUSTOMER REGISTRATION].SURNAME, [CUSTOMER REGISTRATION].[OTHER NAME], [CUSTOMER

    REGISTRATION].ADDRESS, RECEIVABLE.Date, RECEIVABLE.PHONE, RECEIVABLE.SERIAL, RECEIVABLE.CHECK,

    GRN.PRODUCT, GRN.MAKE, GRN.MODEL, [SELLING PRICE TABLE].PRICE, RECEIVABLE.STAFF

    FROM (([CUSTOMER REGISTRATION] INNER JOIN RECEIVABLE ON [CUSTOMER REGISTRATION].[PHONE NO] =

    RECEIVABLE.PHONE) INNER JOIN GRN ON RECEIVABLE.SERIAL = GRN.SERIAL) INNER JOIN [SELLING PRICE

    TABLE] ON (GRN.PRODUCT = [SELLING PRICE TABLE].PRODUCT) AND (GRN.MAKE = [SELLING PRICE

    TABLE].MAKE) AND (GRN.MODEL = [SELLING PRICE TABLE].MODEL);

    because I have tried creating a relationship on it on MSSQL server and have failed.

    I would be joyous and greatful if anyone could answer this sql problem am having so I can continue with the programming

    aspect of this project.

    Thanks and God Bless.

  2. #2
    Join Date
    Jul 2011
    Posts
    22
    I'm only a bit beyond novice when it comes to SQL, so I am not fully understanding your help request.

    Is there a problem with your SQL Select statement, and you need help fixing it? If so, what error is being thrown?

    My confusion comes from a lack of understanding on what you mean by a "the problem am having is a Relationship Model" in the context of your help request. In particular, if that differs from you needing help with your SELECT statement.

    I'm also confused by your use of () within the FROM portion of your SELECT statement. Can you do Joins (without a SELECT) within the FROM clause?

  3. #3
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    I guess all those extra ('s and )'s are from Access. Access likes to obfuscate the SQL code this way.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  4. #4
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    Access does add extra parenthesis to the join clauses of queries, but it takes about five seconds to remove those parenthesis and then you have a perfectly usable SQL statement. I also do a quick search and replace to alias the table names, and that's it.

    The parenthesis are not your problem.

    I am not sure what your statement "i found out that the database model for the access can't work on the MSSQL server 05" means. I am not sure who you "found that our" from, because have hundreds of Access databases that I have upgraded to SQL Server with no problems.

    You do need to spend a bit more time describing what the issue is 'cause I can't make heads or tails of your request.
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Agreed. An Access database of that simplicity should up-size easily.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •