y only show 1 record?

Tech

Registered User.
Local time
Today, 14:56
Joined
Oct 31, 2002
Messages
267
hi there.

making a query where I can get details:

Customer number, name, address
Employee number, name
product details (product number, description, price, total, order amount)


but it shows nothing. if I only put in the customer number, employee number/details and customer name, and order number, it works fine.

what is the problem?
 
Have you set up a relationship betweeen customers and products?

Have you set the join correctly?

If there are no products records relating to Customers then you won't see any records.

Post more details on your table structure.
 
You seem to have 'lost' some records in the 'ProductsPurchased' table.

The query shows 3 records, which is correct as you onl have 3 orders in the Orders table. Yet there are several more Order Nums in the 'ProductsPurchased' table.

This is what happens when you dont enforce 'referrential integrity' in your relationships (which you haven't!).
 
ok...thanks :)

so if I do this in the relationships, will it work? and also, will it have any effect on the current way of working on the database? in terms of operations, will it do the same thing as now after I do this enforce integrity thing
 
You won't be able to enforce ref int as it as the moment, as you have Order nums in the Purchased table that don't exist in the Orders table. How will you track who these belong too?

You will need to either recover these records and put them back in the orders table or remove them from the Purchased table.

Then, you can set ref int.

Do the same on ALL tables.
 
um..ok, thanks :)

so, how would I do this? I am a newbie btw..and need help with this as it's part of the "coursework" for uni..and they really didn't explain anything apart from. here is the work..get on with it
 
Well you've already made a start in the relationships window.

Click on the joins to set ref int.
 
access complains about the order form...the order number..saying like:

"date in the table productspurchesed violates integrity rules"......

??
 
Then read my previosus post. I explained this there.
 
Better still, as this isn't critical data, delete ALL records from Orders and Purchased and then set the ref int.
 
i've done that and it still don't work.

i removed ALL the entries in the purchased and order form

enforced integrity on ALL relations/lines in the relationships

entered new orders

done the search and still the same, shows 3 of the same customer number
 
You have other tables in the query that can affect the results.

It depends on what exists in one and maybe not in the others.

You have to set the join types in the query to what you want.

i.e. 'Show All Records' from Customers and 'Only those in Orders where records are equal' will give you a list of ALL customers regardless if they've made an order or not.
But the joins on the other tables may need to be changed too.

These join changes are only for this query and do not affect the original relationships you set up.

As this is coursework then you should be researching on how to do this rather then dive in at the deep end.
Have a word with your tutor.
 
the tutor don't know anything...she said herself to everyone.

and i am tryin to get help...part of research..
 

Users who are viewing this thread

Back
Top Bottom