Date Calculations (1 Viewer)

[JonnieMorgan]

Hi.

I have a question regarding dates.

I’m building a database for a local bakery.

Orders are placed daily for next day delivery; invoicing is done weekly.

The working week is Sun-Sat

I need to get the Date right on this first time which is why I am asking for the best way to do the calculations please.

I have a feeling I haven’t explained myself very well so my apologies there.

Many thanks.

Jon

 

[arnelgp]

you will be needing this function to get the Sunday (starting day of the week) of any date:

Public Function WeekFirstDay(ByVal dtVal As Date, Optional ByVal FirstDayOfWeek As VbDayOfWeek = vbSunday) As Date
    WeekFirstDay = dtVal - Weekday(dtVal, FirstDayOfWeek) + 1
End Function

so invocing is done weekly, saturday i supposed, say today 26-feb-2022,
you invoice should cover:

20-feb-2022 upto 26-feb-2022.

you can easily create a Query that will return all orders from 20-26 feb:

select * from your order_table where order_date between WeekFirstDay(Date()) And Date();

 

[Gasman]

Look at DateDiff() and DateAdd() functions

 

[gemma-the-husky]

I would have a load of queries, other than the date management, which is really a trivial and easily managed issue. The big problem is knowing what your client REALLY wants., because I doubt very much that they always deliver next day.

How big is the bakery.
Your work is going to be quite similar irrespective of the size of the bakery. If this is the system, you need a system that works whether there are 4 orders a day or 4000 orders a day, but the size of the enterprise must have an effect of what you do.

Does the bakery work seven days a week,
Are there any days off, such as bank holidays, eg Christmas Day?

If you take an order on a Friday, Saturday or Sunday, when do you deliver it?
How do the orders arrive? Phone, email, etc. How do they get into the database?
Can a customer request a later delivery. eg order on Monday for delivery in 2 weeks time?
Is it just bread varieties, or do you do cakes, and special order cakes. (hence the above question)?
Are certain types of bread only baked once a week, or at least not every day?
Can customers collect?
Do you really deliver next day. What if you only have a small order for a certain sort of loaf, and the customer is 15 miles away. Are they really going to make a small batch and deliver next day, every day?
Do they have a retail shop(s). What lines do they need to make for retail sales ie - not ordered as such (but maybe internally ordered)?
Do they have delivery areas, for different areas on different days.
So other than that, is there a cut-off time on an order. Does an order have to be placed by 12noon say, to get next day delivery.
How do they manage prices?
Might a single order be delivered in different deliveries?

I have a feeling the ordering system and production planning will not be quite as easy as you think it might be.

You then need to have a way to print out the delivery documentation, and confirm delivery has taken place.
Maybe you need a way to adjust the orders for short /over deliveries and so on.


In any event you generally store the order date and time, and requested delivery date, and calculate your expected delivery date from that.
Invoicing is easy. Just invoice all completed (delivered) orders not yet invoiced. If you invoice on a Tuesday, maybe invoice all un-invoiced orders older than the previous Saturday. Whatever works for you.

 

[arnelgp]

I have a feeling the ordering system and production planning will not be quite as easy as you think.

i think the bakery is already operational, so there must be some sort of system going on.
only need to identify the current system (system analysis) and translate that into code.
you need to know how the system works for them rather than how it will work for you or say
how you want it to work for them.

 

[JonnieMorgan]

Let me tell you a little history of this job. When I walked in the first time I was presented with a MS Works 7 spreadsheet 4000 rows deep.
Each customer (approx 90) had a few lines of this with what might loosely be termed a "template"

Each invoice was different in some way, very little consistency. Formulas were A1+B1+C1 and so forth for the 7 days. Over time many of these formulas got altered and very often one cell would be missed out. I discovered that for instance one of their biggest customers wasn't getting charged for a product on a Wednesday, this was not the only instance of this. There were mixed fonts, sizes, erroneous gridlines, bit of a muddle.

No data has ever been collected from this every Sunday they go through the entire SS manually and change the dates for next week then overwrite the file. I know hard to fathom in 2022. The whole system is basically what could be done on a typewriter with a calculator.

I quickly knocked up an Excel workbook to manage the whole thing a bit better as a temporary measure.

I spent a lot of time researching something "off the shelf" small bakery centric but they are all so over featured for the needs of the client that they found them over whelming and that part had to be put to one side just because it was such a quantum leap for them.

So I got the job.

Plan is proper simple, I dont need a lot of the data that you would imagine. So it's 3 modules, Customer Manager, Product Manager, Order Manager. Simple as that.

I know the only bit that will get complicated (for my skills) is the ordering section. That's why the date question.

Hope this gives you a bit of background.

Thanks everybody for your input.

Jon