Tables not working (1 Viewer)

[Deonh]

Hello All

First off sorry if my questions is stupid it is a reflection on my knowledge of access please bare with me :banghead:

I am trying to create a warehouse database in acees 2007 and 2010 that can track goods that come in.

i have two types of goods Specials which is not on my current stock list and Current stock of which i have a excel sheet of around 32000+ items.

I have 2 warehouse to put the stock in with over 100 locations.

I am trying to track the stock that comes in by saying it is Special or Current stock if Special and i enter the data it should add it to my Stock sheet if current I can choose it from the Current Stock list; allocate it to a location .

Lastly i need to be able to move the stock either from one location to another in the warehouses or to a customer on a order

I have created a few tables and tried to link it as best i can after reading 100's of posts and access for dummies

tblProduct
ProductPK-Autonumber
ProductCode - Text
Description - Text
Dept - Text

tblLocation
LocationPK - AutoNumber
Location - Text

lnktblPtoductLocation
LinkPK -Autonumber
ProductFK - Number(long integer)
LocationFK - Number(Long Integer)
Qty - Number
PoNumber - Text
DateIn - DateTime

i set this up as a many to many relation and that is as far as i got.
when i try and enter a location for a product to test it say i cant update location field and things like i cant enter data on the one side of the many to many relation

Any and all help will be much appreciate

 

[Mihail]

Your DB seems to be OK, except this:

i have two types of goods Specials which is not on my current stock list and Current stock of which i have a excel sheet of around 32000+ items.

This is a calculated value:

IF exist in my current stock THEN
  Good = Current
ELSE
  Good = Special
END IF

Why Access don't allow this

when i try and enter a location for a product to test it say i cant update location field and things like i cant enter data on the one side of the many to many relation

I can't say without to see your work.
I have an idea but is a wasted time to give you randomly solution.

So, upload your DB, and we'll see.
I run 2007 but, if you can convert to 2003, will be the best.

 

[Deonh]

Hello Mahail

As requested i attached a copy of my database as a 2000-2003 mdb file let me know if it is what you need

 

[jdraw]

A few points:

I see a field PONumber in tblLink ?? It appears there is a Purchase somewhere. In addition, if there is a Purchase, I would expect there is a Customer. If you "purchase 1 or more Products" on a PurchaseOrder, you would probably have a PurchaseOrderDetails table to record/store the details.

You have a good start on the junction/link table to deal with many to many --most people get stumped on that. However, you do not need LinkID, ProductID and LocationID as PK(composite). You use LinkID, or use ProductID and LocationID and then you can drop LinkID.
I prefer to use LinkID as PK, and then make a composite unique index of ProductId and LocationID to prevent duplicates of this combination.

You mentioned Waehouse, Goods and Specials but you have not described what these are or where they are used.

I hope these comments are helpful to you.
I recommend you work through this tutorial before changing anything in your database.

I also think you have to step back from your current design and see what the real scope of your issue/opportunity is --that is, look at the big picture and create a model. Let that be your blueprint; then build the piece(s) you need on a priority basis knowing they will fit into the blueprint.

 

[Deonh]

Hello Jdraw

I have taken a step back and redid my tables to include orders and customers
i did use the link you send me to get to this stage if you can see if i am doing it right i would appreciate it very much

Deon

 

[jdraw]

Seems a very good start.

A few comments/observations:
Customer address, phone, accountNumber... may be needed
Customer_Order does not have an OrderDate, you probably want one/will need one
Customer_Order_Product is linked to SpecialStock. You may want to consider abstracting SpecialStock as a Type of Product. You seem to interchange the terms Stock, Special Stock and Product. I think you should review these and determine -are they the same; what makes them different; then adjust as you see it.
Customer_Order_Product does not have a Price or AgredPrice/SellingPrice -- if you are dealing with Products/Stock where a Price can change make sure you record the Price that was paid on the Order in this table.

Table_SpecialStock has a ProductCode, but there is no indication where this comes from. I think there is a table missing that relates Products/ProductID/ProductCode.
TableSpecial_Location seems to be missing a date/time attribute, but I don't know your data. Does Location equate to Warehouse?
I don't think Qty is an attribute of Location. Seems it would be an attribute of Special_Location.

Hope this is helpful to you.
Suggest you start building some test scenarios to help review your evolving model.

Good luck with your project.

 

[Deonh]

I don't need to keep a record of the customer Address or other details only Customer Name that's why i only have that field in customer Table.

As far as getting confused on product and special stock is the fact that we deal with 2 types of stock normal stock that only get booked into one warehouse then transferred to another to top up stock levels.Secondly is special stock that is not on normal stock list it gets made for the customer then comes in stay in warehouse in a location till order get send down then gets picked and send out. As for a price field i don't deal with that so don't need it.

All i need is to book stock in as Normal stock or Special stock with a PO number, put in a location with a qty attached so that when i have to pick from or transfer it to second ware house i can keep track of stock qty in locations.

I do hope that make some sense Jdraw

 

[jdraw]

I see 2 options for stock type
1- you could have a table called tblStockType with
---stockTypeID PK
---stockTypeName
but since you only have 2 values it may be overkill

2- you put a field in your Product table
-- StockType with Values Special or Normal

and I think you can use joins as necessary to get info and summarize by StockType.

As for what you need to store for Customer etc, that depends on you and your business. Usually we store address and phone or email in order to contact the customer. But usually doesn't mean always. You record the info you need for your business.

Build some test scenarios and "play/work" them against your model.

 

[Deonh]

Thank you so much for your patience in the matter i will keep on building and ask as i run into a wall jdraw you are awesome.

 

[Deonh]

Hello Jdraw

Is see in the link you gave me they have three types of keys for a table

1: Is a Primary Key that is a Auto number (Indexed no Duplicates) PK
2: Foreign Key that is a number field with a long integer FK
3: now i don't know how to say this because i have no idea how to create it PF
It looks like a primary foraign key how do one set that up in a table design

 

[jdraw]

Where exactly are you seeing the PF? Please tell us more.

Also note that a PK does not necessarily have to be an autonumber. A PK is a field that uniquely identifies a record in a table. Autonumber represents an Access controlled means of putting a PK on a record.

 

[Deonh]

Attached File shows the data model i was trying to follow but i ran in to the PF problem

 

[jdraw]

Can you give me the url for the model you're describing?
I recognize Barry Williams' structures, but when I look at his tutorials I do not see the model you have shown.
I suggested a link to ERDs at RogersAccessLibrary, and you in turn sent me a copy of your database --related to this thread. I wasn't aware that you were not asking about something in
a ) the ERD tutorial or
b ) your database.

If you notice the symbol between Product and Amazon_Books and Starbucks_Products, that is representing the fact that Product is a supertype and Amazon_Books and Starbucks_Products are subtypes. Subtypes "inherit" attributes from the supertype (parent).

OK I've done a little more digging. He defines the PF construct on slide 5 at
http://www.databaseanswers.org/tutorial4_db_schema/tutorial_slide_5.htm

Primary/Foreign Keys.
These are indicated by 'PF' alongside the field in a Table.
In this case, the field also exists as a Primary Key in another Table.
In our example, the Primary Key in the 'Customer_Orders_Products' Table is a Composite Key, made up of two Fields.
The 'product_id' is the Primary Key in the 'Products' Table, and the 'order_id' is the Primary Key in the 'Customer_Orders' Table.

I found your picture on slide 7.
You must read his explanation below the diagram.

Small tutorial on sub/supertypes at
this sub/super link

 

[Deonh]

Thank you jdraw for clearing that up like i said in my first post please forgive me for being a bit dim i am trying to do something that i was asked to do that i do have little to no knowledge of i am trying to make the best of a bad situation and apologize for creating confusion with my questions.

 

[jdraw]

No problem. Happy to help.
Good luck with your project.

 

[Deonh]

Hello Jdraw was Wondering if you can have a look at my current Dbase as it is evolving. i am currently stuck on the fact that i want to put a order in for the stock i have it keeps saying that i am trying to enter a null value if you would be so kind to see where i am going wrong again