Posted 5/19/2021 19:35 (#9014324 - in reply to #9010959) Subject: RE: Has anyone made a grain inventory database in Microsoft Access?
South of the River
Over the last 12 years I have been building and using an Access database for my in town job. Electrical construction. It tracks customers, projects, labor, rates, payroll, materials, equipment, creates invoices, work tickets, materials orders, etc. It contains 800 queries (including update, append, delete, make table), 200 forms, 100 reports, 75 tables. It is networked over several computers and users. It works great. It has become somewhat of an odd hobby. I am not a programmer or designer, just an electrician/farmer computer hack. I have thought about making a database for our small farming operation, but have not had the time yet.
I find the programmer purists online hate Access for various reasons. Arrogance, I think, because anyone can build a db with it. Access has a size limit. With a single (even very large) farming operation I don't think you will ever reach size limitations unless you have a table record for each bushel. Access can be ornery on a network, but likely not an issue either for a farm.
With above said, my humble opinion would be keeping all grain transactions within one table. Access allows 255 fields (columns) in each table record (line) to enter data tied a specific grain transaction. A grain transaction could be a dumped trailer load (credit) including - date, field, bin, elevator, ticket number, bushels, moisture, date etc. etc. On the other hand, a transaction (debit) would be grain sold - utilizing the some of the same fields of data (those that apply) plus additional fields used for the financial data of the sale. One of the fields of the table would indicate the transaction as "in" or "out" Bushels in inventory can be calculated via query as well as bushels sold, from a specific elevator, bin, field, year, etc.
Additional db tables could be field data (as in crop field not db table field). Fields in the field table would be linked to fields in the grain table. Field tables could include acres, landlord, rent, inputs, soil type, etc. BPA per field, per landlord, etc. can the be calculated, charted, etc,. Forms can be built to select data to display in reports. The sky is the limit to what you can do with the data. Google, Access forums, and sample databases are your best fried.