Customizing the Northwind Access Template help required

[JulieBright]

Good afternoon, and thank you for reading this. I am in the process of customising the Northwind Template to suit my business requirements which are that; 1) A customer makes an order of a number of products/activities, these products/activities have suppliers and various venues. I need to make an itinerary based on the activities ordered, numbers of guests, date, venue etc. This I have achieved to a certain extent.

However, I need to purchase those activities from the supplier and, ideally, provide that supplier with a Purchase Order which details the activitiy, customer purchasing, and date.

I have the purchase order, no problem, but cant add in the additional fields I need.

I have not yet been able to achieve this. I think my problem is in the relationships somewhere but I cant work out where or how?

I have following tables, Customers, Products, suppliers, venues, orders, order details, purchase orders,purchase order details as well as the other tables etc from northwind template which I don't need but haven't deleted. I need to add information to the purchase order from , I think, the order details table as this holds customer name,a required event date, total guests, these are the items I need to advise the suppplier?

Any ideas gratefully received.

[June7]

The activity supplier needs to know the customer this activity is purchased for? I haven't looked at Northwind in a while but I don't think it intended a direct relationship between purchase orders and sales invoices. Product is purchased as merchandise inventory. Sales invoices/receipts list the product ID of item sold, not a specific purchase order. And the reverse is true, purchase orders do not list sales invoices. What you want to do would appear to be a fundamental change in the Northwind structure. Seems you would need a field in Purchase Order Details for the Order Details ID, assuming each customer order can be fulfilled by more than one supplier, and also assumes each purchase order might be providing for more than one customer order.

[JulieBright]

Thank you for replying and yes I agree I do think Im majorly changing the layout. Yes, I think adding the Order Details ID may do it but Im not sure how to continue. Customer orders can include activities from one or more supplier, but I would want the purchase order only to deal with one customer order.

The current query for the Purchase Order works reading :
SELECT [Purchase Order Details].*, [Qty1]*[Unit Cost] AS[Extended Price], Products.[Product Name], [Order Details].[Quantity]*[TotalGuests] AS Qty1
FROM Orders LEFT JOIN ((Products INNER JOIN [Purchase OrderDetails] ON Products.[Product ID] = [Purchase Order Details].[Product ID])INNER JOIN [Order Details] ON Products.[Product ID] = [Order Details].[ProductID]) ON Orders.[Order ID] = [Order Details].[Order ID];

but when I add in the customer ID it tells me "The SQL statement could not be executed because it contains ambiquous outer joins. To force one.. create separate query...."


SELECT [Purchase Order Details].*, [Qty1]*[Unit Cost] AS[Extended Price], Products.[Product Name], [Order Details].[Quantity]*[TotalGuests] AS Qty1, Orders.[Customer ID]
FROM Orders LEFT JOIN ((Products INNER JOIN [Purchase OrderDetails] ON Products.[Product ID] = [Purchase Order Details].[Product ID])INNER JOIN [Order Details] ON Products.[Product ID] = [Order Details].[ProductID]) ON Orders.[Order ID] = [Order Details].[Order ID];

Close but no cigar...

[orange]

Can you post a jpg of your tables and relationships?
Or a copy of your database in mdb format -- where you have removed any confidential info.

[JulieBright]

March 31 working.zip

I hope this has uploaded correctly it is zipped

[orange]

I have acc2003 and can NOT open the accdb.

[JulieBright]

hope these are readable.

Thanks

[June7]

I tried the query you say works and get a join syntax error. I don't see how this query can relate Purchases and Orders.

I don't see that you have modified Purchase Orders or Purchase Order Details to include a field for OrderID.

[JulieBright]

I'm trying to save the db as an .mdb but it is proving a little difficult.. I'm trying to strip out all the new bits, and will have another go tomorrow. I do hope that will help you to get a better idea!

[June7]

With 2010, you must create a new db as mdb type and import everything. Is that what you attempted?

[JulieBright]

No but I will now, thanks

[JulieBright]

lodgetest.zip

Heres a copy of my current working database as a .mdb. It didnt copy over any new program items but hopefully will still provide sufficient information, I hope, for you to point me in the right direction.

[Access_Blaster]

Redesigning templates is a major headache; there will always be some wizard generated piece of code somewhere that keeps nagging at you or worse. I would suggest importing the tables you need into a fresh new DB. Someone else has already gone thru the process of normalizing the tables for you. Break the relationships and delete the dummy info. Add and subtract the fields you need, keeping in mind normalization. Reestablish your relationships using the template as a model. When you add VBA or macros to your new design the code might actually work. Open the template to see how the forms are constructed; if they fit your needs basically use them as a model, but don’t import them. Move on to queries, and reports. Some people will say this is redesigning the wheel, yeah but it’s you're wheel better get used to it.