Looking for ideas to solve this problem (1 Viewer)

rayape

Registered User.
Local time
Yesterday, 18:50
Joined
Dec 12, 2012
Messages
56
Hello All -

I am trying to develop an application that can track sales.

Background: We supply different construction-related products to various contractors. There are a max of 50 items in our inventory. And every sale may have 3-4 products at most. I would like to be able to have a record of (i) who the items was supplied to, (ii) date of request, (iii) date of delivery, and importantly (iv) what was supplied per transaction.

I am imagining something like this. Have dropdown menu for contractor list and two listboxes. One listbox will include all items in our inventory and the second listbox will display all items supplied. By double-clicking on the items supplied (in the first listbox), the second listbox will be populated. This information will be displayed in the second listbox per transcation. Is this a good way? How do I store this information per transaction? Please share your comments or suggestions.

Also, please free to suggest ideas that can be simple and easy to accomplish.

I am not a MS Access guy by any means. My knowledge is rudimentary.

Thank you.
 

plog

Banishment Pending
Local time
Yesterday, 23:50
Joined
May 11, 2011
Messages
11,613
Put aside your Form ideas and questions for now, you must properly structure your tables first and foremost. That process is called normalization:



The first one is a good place to start learning, the second is a tutorial. Do those then apply what you learned to your data. Start a new Access database build the tables/fields you think you need, complete the Relationship Tool and then post a screenshot of it here and we can help you work through it to get the proper structure.
 

Isaac

Lifelong Learner
Local time
Yesterday, 21:50
Joined
Mar 14, 2017
Messages
8,738
Put aside your Form ideas and questions for now, you must properly structure your tables first and foremost. That process is called normalization:
Well said.
Although it may be tempting to work backwards from the GUI you want to start with, that thinking will not yield best results. Properly modeling the data into tables will pay dividends and make the rest a dream.
 

Micron

AWF VIP
Local time
Today, 00:50
Joined
Oct 20, 2018
Messages
3,476
Consider looking at templates or even the Northwind database project (free M$ sample), as it is largely customer/sales oriented. Then you will likely get some ideas as to what the UI can look like. Make sure you normalize tables as priority #1 as plog states, and don't use reserved words for names in your project. Would help you to learn something about naming conventions as well - all are suggestions to make the journey less painful.

You just might end up wit a form/subform arrangement rather than listboxes - less code.
 

rayape

Registered User.
Local time
Yesterday, 18:50
Joined
Dec 12, 2012
Messages
56
Please take a look at the following images. I am not sure how to connect any of these. Sorry!
tbl_ContractorList.JPG
tbl_Items.JPG
tbl_PurchaseDetails.JPG
tbl_PurchaseDetails_Available Items.JPG
tbl_PurchaseDetails_Contractor Name.JPG
 

Micron

AWF VIP
Local time
Today, 00:50
Joined
Oct 20, 2018
Messages
3,476
As mentioned, learn normalization because that isn't right. Not only do you say that contractor name is a number in some table(s) you also say it is text elsewhere. IMO you need a PO or orders table with the order details and an order line items table for the various items. This is on top of your customer and items/parts table. You will see how this is done if you look at the Northwind db. One word of caution- don't assume that everything that M$ does in that db is kosher because it's not. Things like multi value fields and table level lookup fields should be avoided. Those are other topics you can research.

When you think you understand normalization, draw it out on paper and try to connect the dots. Likely then you could try building supporting tables and show them here, but I suggest you submit for comments before you go populating a lot of data in those tables.
 

Cronk

Registered User.
Local time
Today, 15:50
Joined
Jul 4, 2013
Messages
2,770
And another thing, remove the spaces in field names when you are constructing tables. You'll save a lot of time in developing the database further. Use camel case eg RequestDate, PurchaseOrder
 

Users who are viewing this thread

Top Bottom