Simple warehouse data entry

[aa26i]

Hi members.

I hope someone will help me out on this requirement of mine.

I have to excel files GRN (Good Received Note) and DN (Deliver Note) with columns headers named. I need a access database which when i open the file, it will open the form page with options like, Good Received Note and Delivery Note.

1- if I click GRN it will show below the fields for data entry for GRN

2- if i click DN it will open below the options for DN entry.

3- one button to generate reports as per any search field like GRN reports, DN reports, Supplier reports, products reports etc. the reports output view/table will be simple and same for any report.

4- GRN form (material enter to store) quantities and DN form ( material out from store) quantities always add ot subtract from total item quantities to know the available stock for each item.

5- any item is low on stock when you entering DN data must be shown by change of colour or by any easy way to bring it to the attention of the user.

I have sample access file for this purpose but it does not do few missing requirements like reports by search criteria or adding or subtracting the total item quantity.

Thank you all in advance for your support and valuable time for my request.


Attached:
1- GRN and DN files screenshot (xlsx extension files are not allowed to attach)
2- Access inventory sample file

 

[bob fitz]

I have no wish to offend you. I am only trying to help. IMHO what you have posted is an Access file but it is not a database; You have some tables but none have Primary Keys. You have no Relationships. There are Look Up fields in tables. Spaces in the Names given to some objects.

I think it would be wise to read about relationships and good database design before you do anything else. You will find lots on this forum and google with a simple search.

A database with poorly designed tables is like a building with poor foundations. They are both a disaster waiting to happen.

 

[Gasman]

Also no one is going to create it for you.
We are here to help you do that.

 

[aa26i]

I have no wish to offend you. I am only trying to help. IMHO what you have posted is an Access file but it is not a database; You have some tables but none have Primary Keys. You have no Relationships. There are Look Up fields in tables. Spaces in the Names given to some objects.

I think it would be wise to read about relationships and good database design before you do anything else. You will find lots on this forum and google with a simple search.

A database with poorly designed tables is like a building with poor foundations. They are both a disaster waiting to happen.

Thank you for you reply.

I am here to learn and to take all sort of advices and guidance to do it. I never had done anything like that in Access but i believe that such request were made here before and it is already done before. My access file was just an example for what i need so that one can guide me properly.
can you share some easy database design and relationships tutorial which can help me in my requirement of stock inventory.

thanks

 

[aa26i]

Also no one is going to create it for you.
We are here to help you do that.

Thank you.

I know, no one is free to do it for free. if i can pay i should have gone to a freelancer website.

any help will be appreciated.

 

[Gasman]

Perhaps start here?

Solved - Normalisation

Micron on here posted a few links on another site, the first one being for Normalisation, which Excel users to come to Access have never heard of. He also posted a good few other links. Rather than post to his original post (as it is on another site) anymore, I am posting them here and so will...

www.access-programmers.co.uk

 

[bob fitz]

Thank you for you reply.

I am here to learn and to take all sort of advices and guidance to do it. I never had done anything like that in Access but i believe that such request were made here before and it is already done before. My access file was just an example for what i need so that one can guide me properly.
can you share some easy database design and relationships tutorial which can help me in my requirement of stock inventory.

thanks

There's a host of references suggested in this post: https://www.accessforums.net/showthread.php?t=65220

 

[GPGeorge]

Thank you for you reply.

I am here to learn and to take all sort of advices and guidance to do it. I never had done anything like that in Access but i believe that such request were made here before and it is already done before. My access file was just an example for what i need so that one can guide me properly.
can you share some easy database design and relationships tutorial which can help me in my requirement of stock inventory.

thanks

There are, as has been noted, many good resources for learning about Normalization. One way to find them is to search for "Database Normalization" because the term normalization is used in other, unrelated contexts.

Also, you may find this playlist of YouTube videos helpful. It's not technically as precise as some might like. They do offer newcomers an accessible way to approach the subject for the first time.

Also, you are putting the cart before the horse. You must have properly designed tables in place before starting to work on forms or reports. Put your forms on the back burner for now. Get your table design set up appropriately first. Then return to the forms, reports and VBA required.

 

[GPGeorge]

Also, I see that you've tried to implement Lookup fields in your tables, with Value Lists. Bad juju. At the risk of offending anyone, Lookup fields are used primarily by newcomers who have no solid understanding of relational database design, and they all-too-often get into difficulties as a result.

Resolve these Lookup fields into proper tables. For example, tblUnit should be a table with a proper Primary Key and a field for the values currently in that Value List.

 

[Gasman]

I think you might have the gist by now.?
Get your structure correct first, then worry about the interface?

 

[The_Doc_Man]

According to Niklaus Wirth, the creator of the Pascal programming language, 80%+ of all programming problems derive from bad data design. Unfortunately, I can't find the exact reference at the moment, but he was very much a believer in proper data design. Spend some time up front in your project by deciding what kind of data you want/need. Here are some simple ideas for project design.

Old Programmer Rules #1: If you can't do it on paper, you can't do it in Access.

What you should do is, on paper or perhaps on a dry-erase white-board, lay out your data tables that you think are needed for your project. Modify that layout according to what you learn about DB Normalization. Identify the processes, procedures, rules, etc. about what you do with the data in those tables. You will eventually identify most if not all of the actual tables you need. Go on to identify data flow issues that let you decide the steps you need to take - and WHEN. Your goal here is to build a road-map of what you want to do, based on the simple idea that if you have no idea where you are going, how will you EVER know when you got there?

Old Programmer Rules #2: Access won't tell you anything you didn't tell it first, or at least tell it how to compute it.

As part of #1, you will identify reports & forms that you might need. You will want to see certain things in those reports and forms. A specific subset of #1 is to identify every desired output and trace backwards through your processes to determine where you will find or create or compute the desired outputs. If you need to see X, Y, and Z in the output, you must back-trace to the origins of X, Y, and Z (including derivation of WHICH X, Y, and Z you need). If you need to see XYZ in the output, you must back-trace to the origins of X, Y, and Z - and THEN must verify that you have the formula that computes XYZ from X, Y, and Z.

Old Programmer Rules #3: The tail must never wag the dog.

We've seen it multiple times on this forum. Remember that you are building a logical/digital model of your work process. If your DB says to do something that isn't part of the process or DOESN'T do something that IS part of the process, then your DB is like a dog with its tail standing still but the dog itself is swinging back and forth, i.e. the tail is wagging the dog. That is just plain wrong. There is an old rule in the physical sciences that says if reality and your model disagree and the measurement of that disagreement is proved to be real, the model is wrong. You can never forget that you are trying to make a map of the (business) territory, and a map that doesn't match its territory is kind of useless. So if you don't have a good understanding of the details of your business process, don't go headlong into program development. See #1 again.

 

[Pat Hartman]

Virus' are the bane of human existence. Poor data schema design is the bane of software. If your schema isn't right, you will be fighting with it evey day you have to work on an application for possibly years. Get it right before you start. Make sure you and your users understand how the data ties together. They don't need to understand how to design tables but you do. And once you are able to do that, you can talk with your user in layman's language about what goes where.

At the most basic level, if your schema starts out "right", you will have far less trouble over the years adding features. It is always possible that at some point the business will change significantly and outgrow your foundation but if you have a good foundation, you can probably expand by adding wings. It is only if you somehow need to add that 21st floor to a foundation rated for 20 stories that you end up in trouble.

 

[arnelgp]

the sample db comes from microsoft template.