Question Major help needed. (1 Viewer)

[andyaerial]

Hi guys,

I'm nearly all the way through creating a database for the new company I work for and I have hit one major stumbling block.

My database is designed as a stock control/invoicing and collection system. It has been a steep learning curve as I am fairly new to access and have never used VB before.

The problem I have is this....

My main table is an inventory table, consisting of items of furniture. All products that come into our warehouse are unique therefore I have to assign each one a uniqe ID number with a description, price and weight. This I have managed. Heres where I get stuck.

I need to create a column in that table that is a 'status' column. The possible values would be:

'Stock'
'Sold'
'Written Off'
'Scrapped'

To make things a little more interesting I need to be able to update this column from 3 different forms. 1 Form is a 'booking in' form for when the item enters the warhouse which would deliver a 'Stock' value, the 2nd is on an Invoice when the item is 'sold' and the 3rd is possibly a returns form to change the value back to stock.

Is there anyway of doing this without having to re-design my database?

I'm using access 2007 and appropriate runtimes.

 

[gemma-the-husky]

it isnt so much, whether you can redesign your database - its whether you should - ie how should you manage the changes you require within a normalised data model

I think the important thing here is that each item is unique and distinct. Normally in an inventory system, you have several items of a given product, and therefore it is not correct to try and store details relating to an individual item inthe product table. However, in your case as long as each item is different, and separately managed, then it is probably appropriate to just add extra fields to the product record to manage all these other things.

In many ways, your system is behaving more like an fixed asset management system, than an inventory system - and you might get some ideas from thinking about that type of system

 

[andyaerial]

am i right in thinking then that the column i am asking for help with is not possible? This is the third site I have tried this post on and no-one seems to be able to help me.

As i sais, everything else in the database is working fine, it's just this one 'function' if you like. i also have a new year deadline for V1 so any help would be greatly appreciated, otherwise I can see myself working all over xmas.... joy!

thanks guys

 

[Boro]

You could do it very simple by update record. But, be carefull. I dought that realy all products should be with different id. In that case You have to redesign data base.

 

[gemma-the-husky]

no - what you are doing is quite normal

i would be inclined to use numeric values rather than text for your status values - you never know when you want to change a text description

but then each of the forms that modifies or reacts to the the status setting will have to be coded as required.

and you need to add your own "sense" checking - so eg, you dont/cant raise an invoice for a scrapped item - or you cant scrap an item if it HAS been invoiced - stuff like that

if this is for others to use then bear in mind that often you write about 1/2 your code (or more!) to provide robustness, and stop things like this happening.

but it shouldnt take long, depending on what exactly you want to do

 

[Lightwave]

I think i've understood but generally speaking it is not unusual to update information in a table from multiple forms. It is more unusual to update multiple tables from a single field in a form.

If I were you I'd probably just have two forms.
Purchase Ledger
Sales Ledger

Chances are both forms will be referenced to the stock account with some kind of flag field. The flag field will have three possible basic values (possibly more when you get into it)

Purchase
Sales
Sales Return
(you might find you want Purchase Return as well)

One way of doing it is that every stock item could have a purchase and sale fields. On purchase a stock item is recorded and the status is set to purchase or sale the next sale field is flagged up.

Your stock is therefore everything without a sale and your sales are those only with sales flag. And your purchases are just everything. You can then set up three forms one to show stock one for purchases and one for sales but the underlying table is the same.

Please note I'm just thinking on my feet here - this may or may not be how professional systems do it. This would work reasonably well where your stock is quite self contained units but I think you would have to be more imaginative if it was say material non unitary type

 

[andyaerial]

Thanks guys for all your replies. I love it when severa people start to spitball ideas out...

I've read your comments and things are a little clearer now, but I have something else to add to the mix.

When I used excel to track and manage my stock I did it this way:

A1=Formula
B1=Received Date
C1=Sold Date
D1=Returned Date
E1=Sold Date 2
F1=Returned Date 2
G1=Sold Date 3
H1=Returned Date 3
I1=Scrapped
J1=W/ OFF

Formula:=IF(J1,"W/ OFF",IF(I1,"SCRAPPED",IF(G1,"SOLD",IF(F1,"STOCK",IF(E1,"SOLD",IF(D1,"STOCK",IF(C1,"SOLD",IF(B1,"STOCK","UNKNOWN")))))))

Can I do this a similar way with access? It would be great if on my 'Booking In' form I could have a button that says 'Book In' and this would then add a date to the booked in column and change the status column to 'Stock'. Then when my invoice form is saved or printed have a date added to a 'Sold' column and change the status to 'Sold' and same for written off and scrapped. I am a coding noob so any help would be appreciated.

Thanks guys.

And by the way.... lovin access world forums!

 

[neileg]

Yes you can achieve the same thing but you'd be better off using another table that has your code in one field and the text equivalent in a second. You can then join this table with your main table in a query to retrieve the text values. This means that if you want to add another code, you simply add it to the table and you don't need to change formulae.

 

[andyaerial]

At the risk of you guys tellin me to p*** off and learn it for myself, do you think you could spell it out for me?

 

[Lightwave]

Andy have a look at the queries in the following attached database file and specifically at the stock account query.

It would seem to me that if you have a separate column with dates for the different status you could completly ignore the status section and design the forms around specially designed queries. Because you automatically know that stock is anything that has not been written off or sold.

As a test go into the stock query and input a sale date for an item there. Now close the stock query and open it again. You will see that the stock item has already disappeared and it will now appear in the sales query.

So if you sit down and define what the different combinations are you will get the sources for your forms

EG - All items in table - Purchase ledger
All items without sale or items with a sale and a return = Stock
All items with purchase and sale = Sales Ledger

In this way Purchase ledger query will be the source for your purchase ledger form
Stock query will be source for your inventory form

When you want to move an items say from stock to sales if you enter a date of purchase next time you open up the inventory it will no longer appear but should appear in the sales ledger.

You could do it with flags but why when the flags are just triggers on the dates anyway??

Attached file is Access 2003 format and is made up of one table - the stock (actually thinking on that I should have named it all purchases which is a more accurate description of what it is) - which has similar but not the same fields as you have listed.

 

[andyaerial]

Lightwave that is brilliant! Thank you. Simple design, and effective. Thanks very much!