Relations and criteria - Need help

[MachoGamzer]

Hi


I'm new to Access and I'm helping a neighbor create a database that will act like a place he can enter orders, get reports etc

They have several boats that execute the orders, and I'm looking to create reports based on criteria in the main database. So if an entry is ticked off as "Not Completed" and is assigned to "Boat nr. 1" then I would have a report he could easily double-click to open and send to that boat via email.

Is this possible ?

Thanks!

[CJ_London]

anything is possible. suggest you google 'normalisation' to get a better understanding about how tables and relationships should be constructed. Not enough information provided to suggest what you need - other than to comment that you normally flag something as completed, not the other way round

[MachoGamzer]

Hi, Thanks for your reply. I have watched a few videos on normalising data now, and I believe this is how I set it up from the start. I can try to explain what my goal is and what I've done so far.

I have created seperate tables with Customers, Items they order, and boats. Those are all connected to the main table which is a Order table. The Idea is that when he gets a new order, he can go into this and fill out the order, selecting customer, item they ordered, assign a boat to deal with the order. Now what I'm struggeling with is getting a report that he can send to that boat that gets the assignment. So what I was thinking is that which I mentioned, customize reports to each boat. So Boat number 1 has his own report that when my neighbor double-click on it, it will update with the recent order assigned to that boat, and it needs to be only showing orders which are not completed.

This might be a bad way of doing things, but one of the criteria for that he is gonna be able to use it is that it has to be really easy to deal with.

Hope this clears it up a bit, if not let me know

[orange]

How about stepping back and describe the "business" in simple terms, plain English -no database jargon?
We don't often see "assign a boat" in a Customer/Order application; so please help readers understand.
Even giving us a day in the life of the business would be helpful.

[MachoGamzer]

Hi, thanks for the reply. The business is selling gravel, sand etc in large amounts. They have 4 boats which they load the gravel on to and ship it to the destination. The original problem my neighbor came to me with is that he is on the phone all day, 200-400 calls each day, calling the boats to inform them of the orders, them calling back to make sure etc etc. And they are 2 people dealing with this, and when the main person is away, it gets too complicated for the other person to deal with his handwriting in the notes. So they want a system to keep track of everything, and preferably a system that can send order details easily in black & white, without needing a phone call (or 5).

[CJ_London]

that helps but raises other questions to judge the turnaround time.

how long is a boat out delivering? an hour or 2, days?

is the email manned at the boat end? i.e. if you send an email saying 'take 20,000 tonnes of gravel to this place' how quickly do you expect to get a response - a few minutes? hours, days? - and can those responses be categorised? Are they receive emails via their phone, is someone sitting in front of a laptop? is it sent to the boat captain or someone else on shore?

if you get a 'will do' response, how do you know (or what is your level of confidence) that it is actually going to happen.

In principle from your description, you need a minimum of four tables

customers
products
boats
shipments (use selects a customer, a product and a boat, plus a quantity, required delivery date, etc - and also ultimately populated with an actual delivery date)

perhaps more - maybe the customer has multiple addresses, maybe a boat can do multiple shipments or deliver multiple products, maybe products have different characteristics depending on the customer/boat. maybe boats can only carry certain products, or go to certain destinations. Maybe boats are out of commission for servicing or other reasons. Maybe lots of other things. Suspect you need to look at some logistics applications to see what is required.

In terms of the report, I would expect to see a single report - but populated with the required data from the shipment table which would include the boat details

[Micron]

@machogamzer; might seem like a lot of trivial or unnecessary questions to you, but trust us; the best result will come from us understanding the business. As I read this I wonder if each boat has a device that they are going to be able to view an Access report (or pdf on) or whether you might find this more efficient if you simply sent a formatted email. Without knowing what (little) I now know about the business, I probably wouldn't even have thought of it. I think it would simplify things at the receiving end, at least.

Or are you expecting each boat to be able to feed back into the db that a load is complete? That would be a big problem for Access.
Hope that sheds some light on why all the questions.

EDIT: if this is 3 tables,

Customers, Items they order, and boats

I think you're missing one.
tblCust, tblBoats, tblOrders AND tblProduct
If "items they order" can only be one per delivery, and each delivery is one order, then OK with the rest. Otherwise, you should also have tblOrderItems. It may be that you understand normalization but need to study db design (no slight intended).

[MachoGamzer]

Hi, thanks for the reply. No, I really understand the questions. It's just that I already know all that background stuff so I forget to mention it.(I have worked IT-Service so I understand the struggle) Regarding the boats, I was thinking just sending a formatted email directly to the boat whenever new orders come in (if that process could be automated, that would be interesting)

Regarding the order table, I was thinking that if a company orders 200T of gravel and 200T of sand, my neighbor would just add 2 orders (because the job might need 2 different boats, so I think that would simplify things)

But just to add this, I haven't learned a lot of Access yet, so is there a tool for customizing criteria. For instance the mentioned above; custom reports for each boat that can tell the difference between unfinished and finished orders, or forexample doing a math equation in the last cell, that would use the information of 2 other cells to add up. (Amount of Sand(Pr T)*Price(Pr T)) (4% of total price (commission)) ?

Thanks for the reply's, I really appreciate it!

[CJ_London]

is there a tool for customizing criteria

criteria can reference forms

custom reports for each boat that can tell the difference between unfinished and finished orders

yes providing you are storing the relevant data - down to table and relationship design

doing a math equation in the last cell, that would use the information of 2 other cells to add up. (Amount of Sand(Pr T)*Price(Pr T)) (4% of total price (commission)) ?

databases have fields, not cells but yes you can calculate values by reference to other fields/tables

I was thinking just sending a formatted email directly to the boat whenever new orders come in (if that process could be automated, that would be interesting)

it can - plenty of threads on this subject on this and other forums, either using outlook or CDO. You can send SMS text messages as well. If you use outlook (and perhaps some other email clients) you can also have access process responses as well.