Solved MS Access data base for brewery

[andrew339376]

I want to create a program in MS Access, with three tables, empty beer kegs, filled ones, and kegs standing in bars with sorting. with the ability to transfer these kegs from one table to another by scanning the quar codes associated with the kegs. If you can give advice on how to implement this idea or some alternative, I would be very grateful. I'm not a programmer and don't understand this topic.

 

[Gasman]

I believe you need to read up on normalization before you even start.
@micron who used to be here provided links, but I can no longer find them here. However they are here as I have posted them previously.

 

[arnelgp]

i think you can easily "google" about inventory database (general).

 

[andrew339376]

I want to create a program in MS Access, with three tables, empty beer kegs, filled ones, and kegs standing in bars with sorting. with the ability to transfer these kegs from one table to another by scanning the quar codes associated with the kegs. If you can give advice on how to implement this idea or some alternative, I would be very grateful. I'm not a programmer and don't understand this topic.

 

[Minty]

Welcome to AWF.

Definitely read up on normalisation.
You probably need a different type of set up to what you are describing, and to think through what you are actually trying to keep track of.

Just as an example - If you have a fixed set of Kegs and just need to track their locations you wouldn't have a table per location, you have a table of Kegs and then a field within the keg which identified it's current location.

That location field can be based on a table which you can add records to, so when a forth or tenth location arrives you simple add the location to location list.

Edit - But there could be a lot more to it than that, for instance recording how long a keg was at a location. This would become a more complicated set up requiring a Keg/Location/Datein/DateOut table.

 

[GaP42]

You need to have a clear understanding of the data you need to track the status of you "keg" inventory. From what you have already described you wish to track your kegs. A keg may be in store - filled, emptied or tapped -ie standing in the bar. The kegs have barcodes that allow them to be tracked. So far this is, without further elaboration, one table which a records of kegs storing the QR code, the location of the keg (which may or may not be its Status (Untapped, Tapped, Empty?). What information is in the QR code, how will you scan it? (a dedicated scanner?), how will you transfer that to the table?
Functions on a form to ADD a keg, to FIND a keg record and UPDATE the location/status,of the keg and to REMOVE a keg from stock (really a status change).
You will likely also have a table for allowed locations and status.

 

[arnelgp]

what happens on your other post? same topic?

 

[andrew339376]

what happens on your other post? same topic

I accidentally created the same post

 

[arnelgp]

I'm not a programmer and don't understand this topic.

that is the problem.
it won't be if you hire a programmer and you can tell him exactly the manual steps you have done to accomplished the same.

 

[andrew339376]

that is the problem.
it won't be if you hire a programmer and you can tell him exactly the manual steps you have done to accomplished the same.

thank you for the help and advice

 

[andrew339376]

You need to have a clear understanding of the data you need to track the status of you "keg" inventory. From what you have already described you wish to track your kegs. A keg may be in store - filled, emptied or tapped -ie standing in the bar. The kegs have barcodes that allow them to be tracked. So far this is, without further elaboration, one table which a records of kegs storing the QR code, the location of the keg (which may or may not be its Status (Untapped, Tapped, Empty?). What information is in the QR code, how will you scan it? (a dedicated scanner?), how will you transfer that to the table?
Functions on a form to ADD a keg, to FIND a keg record and UPDATE the location/status,of the keg and to REMOVE a keg from stock (really a status change).
You will likely also have a table for allowed locations and status.

Initially, the idea was that each keg had its own qrcode and when scanning it, it changed its status to the one that I indicated in the form (type of beer, in the warehouse or in which bar it is located), and track all this information. we have already tried to do something similar in Excel, and we can scan the codes using a smartphone

 

[GaP42]

The QR code identifies the keg allowing the keg's record to be found in the table of kegs (KegInventory). It does not change its status until you take an action to update the Location or Status.
The keg record includes the type of beer (type of beer / brand etc is another table), Location is where the kegs may be located (another table), the FillStatus Empty, full, ..) is another table. Each of these tables are related to your keg table through their specific record identifier (the Primary Key) in the KegInventory table (as a Foreign Key)
You maintain the KegInventory by changing the FK in the KegInventory table to identify a change in location or a change in status.
When you acquire a keg you create a new KegInventory record with its QR Code, the type of beer(FK) and the location (warehouse?), and FillStatus (full). You may have additional things you record in your spreadsheet for the keg - eg date acquired.
When a keg is empty and disposed the record may by flagged IsDisposed/ Disposal date (rather than deleted) to remove it from current stock. You can then develop reports to show kegs available by type of beer at any date.
Do kegs go missing? Think about how this will / should be managed.
Would you use this to conduct a stocktake? If so you need to look more closely at inventory management and stocktake sample databases. There are many Access db around that can be used

 

[isladogs]

From your description, you only need one table with a Status field for your three possible keg values. When the barcode is scanned, an update query can be used to change the status value. No records need to be transferred to another table.
As others have made clear, additional tables will be needed for other aspects of the brewery database.

 

[GPGeorge]

I want to create a program in MS Access, with three tables, empty beer kegs, filled ones, and kegs standing in bars with sorting. with the ability to transfer these kegs from one table to another by scanning the quar codes associated with the kegs. If you can give advice on how to implement this idea or some alternative, I would be very grateful. I'm not a programmer and don't understand this topic.

Are you going to account for the length of time it takes to brew a batch of beer? Are you going to account for ingredients used in different styles of beer, e.g. hops, flavorings, malt? Are you going to account for inventory of beer kegs? Are you going to account for locations of the bars where your beer is being served? Do you brew at a single location, or do you have multiple brewery sites? Now or in the future?

I agree with Colin that, if all you want is the statuses listed, this could be as simple as a single table, but I was once part of a team working on a database for a craft brewery in Seattle. It was far more complex than this opening description. I have my doubts that you'll be satisfied with just that over the long run.

As far as processing goes, will bar codes be scanned on-premises only? Will the bar codes need to be scanned in the bar locations where the kegs are delivered? That can impact how you design the database, and even whether Access alone is a viable solution. Off-premises bar code scanning in a bar is not within the norm for Access database data entry.

 

[GPGeorge]

Initially, the idea was that each keg had its own qrcode and when scanning it, it changed its status to the one that I indicated in the form (type of beer, in the warehouse or in which bar it is located), and track all this information. we have already tried to do something similar in Excel, and we can scan the codes using a smartphone

Elaborate please on scanning the QR codes with a smart phone. It's not quite clear whether you are already doing so, or if you are just saying that it is possible. If it is now being done, how is that data stored when you scan it? WHERE is it stored now? In the Excel sheet?

You might want to look at a demo I created a couple of years ago for an bar code scanning process for Northwind Developers version. I used PowerApps running on a smart phone for the bar code scanning component. In some cases, the data was stored in Dataverse (don't go there; I can't recommend Dataverse with an Access interface). In others I used remotely hosted SQL Server. In all cases, the same tables are linked to the Access front end on the desktop as well.

There is a total of 4 videos on that topic, a number which, in itself, suggests that this might be a bit more complex than it appears at first blush.

<<Sorry, this one was recalled for maintenance>> And thanks for the heads up @Uncle Gizmo

This is all in addition to my comments on the companion thread you also started on this topic.

 

[Mark_]

As other's have pointed out, you'll need a lot more than one table.
Think from just your health and safety requirements. You'll need a child table that covers inspections of the kegs at a minimum. You will also want a table that covers each batch you brew then a junction table that links each batch to each keg that is filled from it. If your area has similar requirements, you'll also need to show records of when each keg is cleaned (since it may need to be cleaned more than once between fills).

Rather than having a "Location" field in your keg table, I'd have a separate "Location" table and use a junction table to link kegs to locations. That way you can track not only where a keg is, but where it has been. Mostly useful for the whole "We need every keg that was at Joe's bar when they decided to spray for bugs deep cleaned, especially the outside, to remove pesticide residue!" type situations.

Yes, this is and should be a lot more than just "Where is a keg at".

 

[pbaldy]

FYI I merged the technical posts from the OP's intro thread here to keep them all in one place. It appears some answers preceded the first post here, so I'll move the original post there to here as well.

 

[Pat Hartman]

I've seen George's sample. It is on target for what you'll need. Don't go down any rabbit holes.