Can I make an Outer Join a one way relationship?

[johnjmcnaughton]

I have a Purchase Order Database. The "Purchase Orders" form automatically makes the appropriate transaction in the "Inventory Transactions" form. (each of these forms are based on their respective tables)



From what I understand this is able to happen because of an Outer Join.

The problem I am having is that when I want to create an inventory transaction to reduce inventory it automatically creates a Purchase Order to correspond with that transaction. Which obviously doesn't make sense. This is what I mean by making it a "One Way" Relationship.

I just want the purchase orders to create transactions in "inventory transactions" and "inventory transactions" to NOT create a purchase order.

Is there a different type of relationship I should be using?


Any thoughts?

[orange]

You may want to look at a few data models involving Purchases and see if your table structure is somewhat similar.
http://www.databaseanswers.org/data_..._mgt/index.htm

Perhaps you should show us a jpg of your tables and relationships.

[johnjmcnaughton]

I attached a jpg of my relationships.

I am unable to zip any files due to restrictions on downloads and installing software on my work computer, but is there anything else I can provide that would help?

The company does not sell any products. We have items in inventory (on the shelf). And those items get used (reduced from inventory) and new items are purchased (added to inventory).

[johnjmcnaughton]

The reason I think it is an outer join is because when I have a blank field in "Inventory Transactions" I cannot add anything after the fact.

Case in point: I created a transaction for "Beginning Inventory" for all items on the shelf. I did this in "Inventory Transactions" directly. I entered the amount in "Units Received" so that those items would be in inventory. I didn't enter an employee in the employee field for those transactions. If I try to go back and enter an employee on those transactions now I get the error "Cannot enter value into blank field on 'one' side of outer join".


I attached a jpg of the "inventory Transactions showing some of the transactions with "Units Received" and "Beginning Inventory"

[johnjmcnaughton]

ok.... If I go to the Property Sheet for the form "Inventory Transactions" the Record Source for the form is the following:

SELECT [Inventory Transactions].TransactionID, [Inventory Transactions].ProductID, [Inventory Transactions].UnitsReceived, [Inventory Transactions].UnitsUsed, [Inventory Transactions].TransactionDescription, [Inventory Transactions].PurchaseOrderID, [Inventory Transactions].UnitsOrdered, [Purchase Orders].EmployeeID, [Inventory Transactions].TransactionDate
FROM [Purchase Orders] RIGHT JOIN [Inventory Transactions] ON [Purchase Orders].PurchaseOrderID = [Inventory Transactions].PurchaseOrderID;


So there it is called a "Right Join" not an "Outer Join".

Still just as confused...
Anyone have any ideas???????

[johnjmcnaughton]

When making an Inventory Transaction it does not create a Purchase Order Number until you enter an employee. When looking at the Record Source I see that EmployeeID is coming from Purchase Orders.

Shown here:

SELECT [Inventory Transactions].TransactionID, [Inventory Transactions].ProductID, [Inventory Transactions].UnitsReceived, [Inventory Transactions].UnitsUsed, [Inventory Transactions].TransactionDescription, [Inventory Transactions].PurchaseOrderID, [Inventory Transactions].UnitsOrdered, [Purchase Orders].EmployeeID, [Inventory Transactions].TransactionDate
FROM [Purchase Orders] RIGHT JOIN [Inventory Transactions] ON [Purchase Orders].PurchaseOrderID = [Inventory Transactions].PurchaseOrderID;

I have tried to change that from Purchase Orders to Employees and to Inventory Transactions but neither of those work.
I feel I am getting closer to solving this. But I also feel like I am not giving enough information for any of the experts to comment or suggest a way to solve it. Or is it that no one has come across this situation?

[orange]

Please describe how Supplier relates to PurchaseOrder and not to Product. It seems to me the generic case would be
-- a Product is supplied by 1 or more Suppliers-

see this data model for ideas
http://www.databaseanswers.org/data_..._mgt/index.htm

these are the related business facts/specs
http://www.databaseanswers.org/data_..._mgt/facts.htm

[johnjmcnaughton]

I suppose that is just how I set up the database. On the Purchase Order Form you choose the supplier from the list, and there is a subform for the products ordered. My Boss wants only 1 supplier set up for each item. But I set it up so that if that supplier does change you can just pick a different one from the list. I do see your point though. If the Supplier was related to Product instead of Purchase Order, when I choose a product the supplier would automatically be filled in. I think that will be a rather drastic change to the database and will take some time to accomplish. The database is in use and I am now trying to go through and "fix" the things I see that could make it more user friendly all the while not screwing up any of the data already saved. At this point, that relationship is technically working so I don't want to address it yet.

My question is why are you focusing on that issue? If I set it up that way would my current problem be easier to address? It seems as if that would only streamline the making of the Purchase Order process. This would still leave me with Inventory Transactions creating purchase orders when trying to reduce inventory.

Here is a jpg of the Purchase Order Form.

[orange]

My question is why are you focusing on that issue?

My purpose for focusing on the tables and relationships is because your database should be reflecting/representing your business.
That's the whole point - to make sure your database is a representation of your business rules.
It is the business rules/facts that determine the major "things" in your database, the attributes of those things and how the things are related. It isn't a boss' choice or a random pattern.

If you go through many posts - here and in other forums- or look up info on database design generally- you will find that getting your tables and relationships defined and tested with some "test scenarios" is key to a useful and maintainable database. If you start off with 'poor table design' (I'm not suggesting you are- just making a general statement) you will continually be trying to build "work arounds" for things that don't quite work. Experience shows that normalized tables and relationships built on business facts lead to databases with ease of use, ease of maintenance and ease of change (if and when required).

Do you have a list of specs?
Do you have some test cases set up to "test the model"?

I don't think it's a question of

If the Supplier was related to Product instead of Purchase Order,

.

Does the Supplier supply a Product, or does the Supplier supply a Purchase Order?

[johnjmcnaughton]

The whole purpose of this database is to track inventory and create purchase orders.

Inventory:
The purpose of the inventory is to know what exactly we have on our shelf. If there is something on the shelf and we don't know it, that item is purchased again when it is needed. We are trying to avoid this problem. A lot of these items are high dollar items. Also, we want to be prepared for emergency needs. If something could go wrong we want the parts to fix it. Inventory will aid in this process. We could see that items that we could need are not in stock so they need to be purchased.

Purchase Orders:
The Purchase Orders are for our company to send to a supplier to approve the transaction. They are also for our records so we can look back at what was purchased, from which supplier, and by which employee. We are a maintainance center so we do not sell any products we only use them. So when items are used we need to reduce them from inventory. When we need to buy items we create a purchase order then email it to the supplier. When the purchase order is created it puts those items in the inventory transactions list as "ordered". When they are delivered, we mark them as "received". Once received it is added to the count in inventory.

Here is a Test Purchase Order: This is what we would send to a supplier when we want to buy items from them.



As a whole this database is working quite well for our purposes. It is accomplishing everything we want it to. It is working great. I of course do not want to need "work arounds" in the future. Although, I really wouldn't categorize what I am doing as a work around.... more like fine tuning.

Because I am not an expert in Access, I have spent weeks creating and then fine tuning the database as I learn more about relational database theory, and how to use Access in general. I have come a long way in understanding all of this. A lot of trial and error. The end product may not be exactly what someone else would have come up with, but it works for our company.

[orange]

The key point you have made - to me anyway - is
[QUOTE]it works for our company[/QUOTE].

That is key. If it works for you and the business -- great.
I would like to make one comment however.

From experience with inventory, it is advisable ( if not an SOP in most orgs) to do a stock taking from time to time.
As we all know sh** happens and something could be used and not recorded etc. So an annual or quarterly or whatever "stock taking" where you actually do a physical count may be in order. You can adjust your "computer transaction counts" using your most recent physical stock taking counts.

[johnjmcnaughton]

That is a good point. I would agree. Especially in the beginning, it has proven difficult to get everyone on board with the inventory system. So re-counting inventory is going to be necessary from time to time.

now, as far as my "fine tuning" goes lol..... do you have any suggestions on how to create a transaction in "Inventory Transactions" to reduce inventory when an item is used - without it creating a purchase order to go with that transaction?

I have tried looking into the Join Properties. There are three options and I have tried each of them.
Take a look at this jpg:

I circled the join properties with the three different settings.
I also circled where the "employeeID" is pulled from the "Purchase Orders" table.
I feel like I am on the right track.... or at least looking in the right direction.

[orange]

I'm getting Invalid Attachment when I tried to open the attachment.

Here is a good reference for Inventory -- Allen Browne
http://allenbrowne.com/AppInventory.html

[johnjmcnaughton]

I'm sorry...I think I screwed that up. Is it working now?

[orange]

Did you try this?

Here is a good reference for Inventory -- Allen Browne
http://allenbrowne.com/AppInventory.html