How to track Opening_stock, transactions n Closing stock? (1 Viewer)

[captgnvr]

D/All

I hv made lots of progress thanks to this forum and 'hitechcoach'.

I have staff tables, items table and effected some sales and purchases etc.

Can I have a link to read up about:

1. How to keep track of opening stock?

2. How to keep track of monthly sales and purchases?

3. How to get month end stock?

4. To get all the data like opening stock, purchases and sales and closing stock at any time.

From the material I found on net, I am to use, group by, sum etc. I am on the look out for a sample to read up and implement.

The northwind example does not have the reports to keep track of opening and closing stocks and how to transfer closing stock to opening stock for the next month and so on.

Now I want to finish the project I have on hand and would appreciate any links or database samples or guidance is being looked forward to.
brgds/captgnvr

 

[JamesMcS]

Don't have any links for you, but I guess it depends on how you've got the data structured.

I think you can do the lot with queries, just using conditions on dates really - stock carried forward from one month to the other would just be selected based on date criteria, monthly sales and purchases could easily be kept in two tables, and for ad hoc queries you wouldn't need any dates at all - just use a query to take a snapshot of what's in the tables at any one time...

 

[captgnvr]

Thank you James.
I just started on it with all the northwind and other reports available etc.

My main requirement is some initial start up or some tips on how to go about it.

Weather it is better to have all info under one query by joining staff, stores, purchase, sales and work on the report and the like.

 

[JamesMcS]

Ah right - well, what you'd want is separate tables for everything - so one for staff, one for stock numbers and descriptions and whatnot, and one for transactions which would contain, say, staff number, date, stock number, customer number, and what's been bought or checked in - but in this table you wouldn't have staff name or stock description, just their respective numbers.

Then, when you create a form based on this transaction table, you could have something like a combo box to pull back details from the staff table, one that would pull back details from the stock table, and a quantity in or out.

The combo boxes do the job of displaying meaningful information (staff name, stock description) but they also populate the staff_ID and stock_ID fields in your table (look up bound columns in combo boxes), so as you add transactions you'll see the table filling up with loads of numbers.

Then, when you want to report on stock levels, you can create a query from the transaction table, and link in the staff and stock description table through the ID numbers, and base your report on that. Add in criteria such as date (for stock levels at a particular time), staff ID (to report on staff activity), stock ID (to report on how a product is performing) - anything you want really.

 

[captgnvr]

D/JAMES
Thank you for the guidance. Thanks to hitechcoach I have managed to do as mentioned in ur para 1 to 3. Now I will make one more table for products coming in. After which I will be trying out ur para 4 instructions. With regard to this what is going on in my mind is:

"are there any ways like some VB steps to use one query to do multiple choice like 'update', 'filter records', 'do calculations' and to transpose one column value to another column?? For example once you have for month end stock and amount for each item, I want to transfer end stock to begining of month stock and opening amount for each item.

Looking forward to n brgds/captgnvr

 

[JamesMcS]

One query won't do all those things but you can use VBa to either run a query (docmd.openquery) or a piece of SQL (docmd.runSQL). I use a series of runSQLs to clear a table out, then append from 12 other tables, then update certain fields.... you can have as many as you like (well, almost - I found out recently that subs and functions in VBA must compile to 64k or less, but that's a sh*tload of code!)

 

[JamesMcS]

You might also consider not having a "stock in" table, but having a "transaction type" field in your transactions table - sale, stock in, refund etc. That way you keep everything in the one table, and it's easier to work with - creating queries, reports and such.

 

[captgnvr]

D/JAMES
Noted reg not to have separate table for items-in. Today will venture into final lap of making these reports. Already I tried on what u said but some items are not being shown bcos of the tables joined for customer id and so on. So I am trying to read up with regard to how to make all records to be shown. If u could tell me, it might save some time for me to go ahead with the rest of the report.

Note: seeing ur greenlight go off, I was dejected and minutes later I see u online and gives immense encouragement to proceed with my project. Anyhow mine is not too complicated that needs 64k code etc. It is just about 35 crew members with about 30 items.

 

[JamesMcS]

Don't worry chap, I check back regularly! So - in query design, when you join two tables together by their ID, you can double click on the link and make it show all records from one table, and matching records from the other, or (the default) only show records where there's a mtach in both tables - so for example if you've got a customer who hasn't ordered anything, and you create a customer sales report (by joining transactions and customers together, drag down customer ID and, say, sales value from transactions), if a customer hasn't ordered anything, he won't come up in the query's results.

If you change the link in the query to show all records from the customer table, and matching records from the transaction table, it'll show all customers, and those who haven't ordered anything will have blanks in the sales field of the query. Hope that makes sense - have a go and post back.

 

[captgnvr]

D/JAMES
Very humbled at the detailed explanation. Yes very clearly understood with regard to double clicking on the link etc and at present I am having one export tanker operation going on but very tempted to squeeze in some time to get over the nagging thing in mind to complete it. Thank u for such quick clarification and as directed I will work on it and post the feedback.

 

[JamesMcS]

Good luck, and no worries, that's what these forums are for. I'll stay subscribed to the thread so we can go over any problems later.

 

[captgnvr]

D/JAMES
Have made some progress. I got the report to show names, purchases and amount.

I got the sales grouped under name and then grouped under item-wise.

I got the total quantity for purchases of the same item and the sale_amount. This is placed in item-group footer by using a text box control source as sum([quantity]), av_price and another text box with control source as =sum([sale_amount])

Where I am stuck for long is unable to get the total amount if the crew has purchased various items. I placed the text box in name-group footer and gave the control source as 'sale-amount' and used running sum over the group. The totals of various items sold is giving wrong figures.

Pls help.

 

[JamesMcS]

You might just need to group in a different way - you can add multiple grouping levels, but if you're grouping by staff (to show what items they've bought), AND by item (to show who has bought what item), you're going to run into problems - it's two different ways of looking at hte same data, which can't happen in the one report.

To show the different items by who bought them you'll want to group by staffID. Try it in a query first, I find it easier to see how things are grouped up.

 

[captgnvr]

D/JAMES

HURRAY. Big break through. I got the grouping under name of the staff, second group as the items and finally the total amount for each staff I got it by trial and error method by removing the running sum and instead used the text field in names-group-footer and control source as =sum([sale_amount]).

Then got stuck to get the page total as it was not working. Leaving it aside went on to the report footer and used the running sum on 'sale_amount ' and used to 'over group' and/or 'over all' both giving same required result.

So now I have to figure out why the page total is not coming in page footer. May be a bit of vb which I read must be tried.

Thanks for being here and pls advice if special steps required for totaling the amount for the page. I am reading about 'on print' in detail section etc. But I have not put anything in detail section and so wondering how to go about.

 

[captgnvr]

D/JAMES
Good eve. Nice to see u green . I am happy today for the progress I made. So in a way thanks to the start u gave, got the report for the sales done up. Now can u pls give me some links to read about VB so that I can use it for updating a query, to run a sql and to run a filter. I am also in the mean time checking in the forum and also googling.

 

[captgnvr]

D/JAMES

Found the required sql help in this site and going lesson by lesson. http://www.fontstuff.com/access/acctut17.htm

 

[JamesMcS]

When you're running SQL in VBA, it's pretty easy really. What I usually do is design the query as normal, then switch to SQL view, copy the SQL code, and paste it into VBA - so in your VBA you'd have "docmd.runSQL ..........", pasting your SQL code into the ... bit.

This has the advantage of you being able to put variables into your SQL code. So, if you've got a text box on the form, you can put it in your SQL like this:

....[SQL statement]... WHERE [StaffID]=" & me.staffidtextbox & "......

Have a mess around with it and see how you get on!

 

[captgnvr]

D/JAMES
GM. Thanks for Todays class n will try as u hv sugested and also trying to figure out a way for closing stock to opening stock. Will it be best to use update query or do it thro VBA like the usual set db as database, rs as recordset etc on clicking a command button. Or is it advisable to run the update query on clicking the command button?

 

[JamesMcS]

Honestly I'm not sure of the difference. I've not used the recordset method before, but I suspect it'll be quicker than running SQL in VBA.... who knows??? not me