Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Good Day,

I am a C# programmer but I have a problem with a database model on SQL Server 2005. I have a client who told me to rebuild his database from Access to SQL Server 2005 and actually I did but I found out that the database model for the Access can't work on SQL Server 2005. Its a retail management database mainly for his small business.

The database in access consists 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 SQL Server and have failed.

share|improve this question
    
What exactly failed? Did you get an error message? –  JNK May 30 '12 at 12:38
    
Have you created the tables in SQL-Server? Have you defined the Primary Keys? And the Foreign Keys? –  ypercube May 30 '12 at 13:03
    
Is this related to your other question? –  JNK May 30 '12 at 13:28
    
I have created all this tables and all its constraints on MSSQL server 05, the problem am having is joining GRN table to Selling price table using (GRN.PRODUCT = [SELLING PRICE TABLE].PRODUCT) AND (GRN.MAKE = [SELLING PRICE TABLE].MAKE) AND (GRN.MODEL = [SELLING PRICE TABLE].MODEL); –  Peter May 30 '12 at 13:30
    
@Peter that doesn't answer my question. What problem are you having with the JOIN? Bad results? An error message? No results? –  JNK May 30 '12 at 13:47

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.