Current stock and Calculating cost queries

[RainLover]

Thanks Nicole

I will have a look over the week end if someone else does not.

Suggestion.

Your naming conventions are incorrect. Try downloading my article on Naming Conventions.

Just click on the Blue link that is below my signature. It will take you to my SKY Drive so you can download from there.

Will write soon.

 

[RainLover]

Nicole

I had a good look at you design and believe it to be very basic..Having said that it may very well be all that you need. One thing you have not told us, is what you expect out of this Database.

Things like how much of each item was sold between two dates.
What was the stock level at a certain date.
How much spoilage did you have during a certain period.
Where did you make your purchases.

If this design can deliver everything on your list plus have the ability to do more then you don't have a problem.

The sample I posted is what I use as a template for this type of thing and I have found it very helpful. Many people dismiss it because they do not understand it and do not realise the power it has.

Perhaps you should go back to the basics and ask yourself what is really required of this. At the moment you appear to be attempting to solve one particular problem at a time without really looking a the whole situation.

I have noticed you still are using bad naming conventions against the advice you have been given. I suggest that you attend to that problem ASAP.

Hope I have opened your thinking to a bigger picture.


Some suggested reading http://allenbrowne.com/AppInventory.html

 

[nicole.skeeters]

Morning, Rain! I will correct the naming conventions today - it was a holiday here in the states so I was off work.

The purpose of the database is simply to track supplies - to see stock levels and to see what each department is ordering at the end of a quarter. It is pretty basic. I want a form to enter in "transactions" - items coming in to stock(State Government, we only have one supplier), and items requested by a supervisor. This is my main issue, as I am somewhat proficient at creating reports from scratch. For example, I get requests for supplies on a daily basis (pens, paper, white out, etc), and then once a week I order from our supplier.

I have no issue with your database sample, I just dont know how to fit my data into the model.

 

[RainLover]

Nicole,
I thought my sample might help point you in a direction. It was not custom built for you. It requires changes to suit. If you can't replace my data with Pens and Pencils etc then we have a problem which I don't understand.

You have said that all you want is something pretty basic. So please tell me if there is something I can help with or am I barking up the wrong tree.

 

[nicole.skeeters]

Rain, I didnt mean to offend or anything, I do appreciate the sample database, and of course I know it wasn't built for me. I had successfully imported basic product info (description and my item id), but that messed up the stock on hand field for everything. I dont mean to be a pain, you're just much more advanced than I am so I'm trying to understand why the expressions are what they are and what they tie back to.

Is my basic set up wrong?

 

[RainLover]

I gave you a Link in Post # 22.

Suggest you read that because that is the source I used to write my Sample.

You are given a couple of samples.

Do that then get back to me a let me know which way you want to go.

 

[nicole.skeeters]

Good morning, Rain. Can you help me understand a few things regarding your template?

*How does one create a new stock take? I understand, from reading the link, that these are done by date, and your sample has "demo" dates put in to show example stock takes - what I dont see is how to get a new one.

*Where would be a good location to import each item's price, in your opinion? I was thinking tblproduct.

*I need to be able to show "sales" by department and also by supervisor (which I can build a report, no problem), so where would you suggest importing this information? I would think it should go in its own lovely table, but I've been wrong before. If so, I would do to following:
tblSupervisors
SupervisorPK
Supervisor
Department
Division
and I would do a one to many via SupervisorPK to tblsalesdetail (I deducted this based off your relationship design). Correct?

Thats all I have for now - really appreciate your patience with me.

 

[RainLover]

Stock Take Dates. Just create a new record and add a new Data. You also don't have to do a stock take of every item you stock. You can update one item or a few items or all.

I am currently thinking about price.

Please tell me, do you want cost price of sale price.

 

[RainLover]

Nicole

Does a Supervisor work in one Department and never move.

 

[nicole.skeeters]

Good morning, Rain!

In reference to your cost question, each item's cost should likely be a stored value and then I would use the query from CJ to calculate the transaction cost (one supervisor can request several items on a single date) to create a quarterly report.

While there are multiple supervisors per department, only one department per supervisor. I did go ahead and import that in to its own table already as I described earlier.

 

[RainLover]

Good morning, Rain!

While there are multiple supervisors per department, only one department per supervisor. I did go ahead and import that in to its own table already as I described earlier.

Therefore if we create a Table with Department, Division and Supervisor we can select them all simply by selection the Department.

I have done this and will post after doing something for the cost per unit.

 

[RainLover]

I could see no reason why not to attach what I have.

 

[RainLover]

In FrmAcqSub, One calculation needs to be made now that I have changed some things.

Cost Per Line is Quantity times Cost Per Unit.
Latest is attached.
Let me know your next problem.