Populating a query from two tables

[Peter Simpson]

Hi
I am new to Access so please bear with me.

I have two tables (1) arpsOrderTbl and (2) arpsFeedbackTbl

I want to design a query where the fields (Oder Date, ID Number, Surname, Service) from the arpsOrderTbl and the fields (Feedback Date, Feedback) from the arpsFeedbackTbl

I tried creating a simple query however the query did not populate.

Please help. I am very new to Access 2007 and am also not too good with understanding the relationships

[ranman256]

in the query, join the 2 tables on the arpsOrderTbl.OrderID field. (the key fld)
then you can bring in any fields from either table.

[Peter Simpson]

Thank you so much...That sorted it out.

I can see only one problem now in my report.

In the query there are two sets of feedback data for one customer, but in the report only one feedback is populated for that customer. the second feedback row in the query has been assigned to another customer who has no feedback populated yet.

[Peter Simpson]

the query



the Query datasheet



the report

[Peter Simpson]

Can anyone help...I am really stumped.

All I need on the report is to have the customers Order Date, Surname, Id Number, Service, Feedback Date and all the feedback listed so that I can see the history of the feedback from cradle to the grave.

I know I am probably irritating everyone but I would really like to learn

Thanks

[June7]

Your image doesn't show the tables and relationship link.

Post the query SQL statement. Switch to SQL view to see the statement.

[Peter Simpson]

Relationships

[Peter Simpson]

SQL Statement for query

SELECT arpsOrderTbl.[Order Date], arpsOrderTbl.Surname, arpsFeedbackTbl.[ID Number], arpsOrderTbl.Service, arpsFeedbackTbl.[Feedback Date], arpsFeedbackTbl.Feedback
FROM arpsOrderTbl INNER JOIN arpsFeedbackTbl ON arpsOrderTbl.ID=arpsFeedbackTbl.ID;

[Peter Simpson]

Hi June7

Thank you for your help

I have posted what you have asked to see.....Please tell me you can help me

[andy49]

Something seems wrong with your relationships Peter. You have firstname linked to ID? If this is true your database isn't set up correctly.


Ideally each table should have a Primary key which is joined to a foreign key in another table.

[Peter Simpson]

Hi Andy

hanks for that but I have no idea how to rectify that

[Peter Simpson]

Is this better? Sorry for being so slow but I am new at this and just want to learn

[andy49]

All tables need a primary key labelled something like

CustimerID_pk
Serviceid_pk
OrderID_pk

And so on. These are your key fields.

[andy49]

Then, which field in service table contains the customer number. This is your Foreign key and is labelled
ServiceID_Fk

Similar for order and feedback tables

[Peter Simpson]

Customer Table

Service Table

Order Table

Feedback Table