Update Relational Databse

[6pandn21]

Hello everyone! I am completely new to MS ACCESS, so this problem might be fairly simple but yet it is eating my head.So if I get some directions it will be greatly helpful and appreciated.
Description:
The bakery buys ingredients from number of suppliers, makes a several kinds of cakes and sells to few shops. There are about seven ingredients in the first week with the possibility to add new in ingredients later on. There’s a catch though; on any day, only a single type of cake is made, in a single batch and all of them must be delivered before the next day’s cake are finished. It is necessary to be able to rack the ingredients used in a batch back to the suppliers.
I have made tables for suppliers, ingredients, orders and order line (with fields ordereId, ingredientId, quantity, unit price).
Forms that allowing users to allow new ingredients types, suppliers, and new order for ingredient are required which I have made. These forms are to be used to add the data for which ingredients are needed in the second week of baking.
So these are my questions on which I am stuck in

1. Do I need any more Forms or do I need to edit my form?
2. Is it okey to put table as the data source or would it be better to place a query?
3. How do I keep records of my earnings and expenditure, expenditure and keep them updated with my databases?
I hope you will understand that I am completely new to these topics and its really hard for me to grasp them all at once. Merely some guiding points will be of great help. Thank you.
6pandn21

 

[The_Doc_Man]

My site goes nuts when I download files, even .JPG, so I cannot look at your schema. Take my answers therefore as being rather general.

1. Forms... you never have enough forms. You always need to consider that EVERY table should have a private form for maintenance, since forms give you visual layout advantages for editing of data. But then you should also consider forms for the work of your site (NOT table maintenance, but real work.) For beginners, it is better to have a couple of extra forms hanging around until you learn to recognize what is REALLY useful and what isn't. Trust me, that doesn't come overnight - but it WILL come.

2. Forms based on table vs query... The maintenance forms mentioned above should always be based on tables. Work forms usually should be based on queries because you almost ALWAYS have some particular order or treatment you want to apply. Or you might wish to see joined tables, which means "QUERY" right off the bat.

For forms that are non-edit, it is reasonably safe to work from tables. For maintenance forms that are MEANT to allow you to muck tables, it is reasonably safe to work from tables. There IS a school of thought that says, if it is a complex filter or sort, it is better working from queries rather than tables because of Julius Caesar's old rule: Divide and Conquer. Make the query do your sorting and filtering, then make the form show what the query found. That separates the workload into TWO entities, which simplifies the work you must do on either one of them. I.e. break the problem down into parts and work on the parts. Conceptually much easier.

3. How do you track earnings, expenditures, etc...? - the same way you do anything in Access. You analyze the problem, define the tables needed to track the data you want to keep, and build forms and reports to achieve your goals.

There is NOTHING IN THE WORLD that says you can't hold two aspects of your business in the same database - the financial side and the baking / recipe side. You could also create more than one database. Your Access license limits users, not databases. If you decide to keep a separate database to avoid confusion, that makes perfect sense too. You bought a licensed copy of Access, it is up to you to make it work in the way you want it to.

Now, if you want the expenses and sales and other stuff to somehow be tied to your bakery runs / batches / whatever-you-call-it, that is trickier to keep as two tables.

Here is the old programmer's rule that applies to this situation: Access can never tell you anything you didn't tell it first. So (for example) if you want to know the cost of a batch, you must somehow tie the cost of the ingredients to the batch - which means that your ingredients table must have cost factors associated therewith. THAT is what I mean about "telling Access so it can later tell you."

 

[6pandn21]

thank u

thank you doc man for a quick reply...i can only go through it on monday so i cant post more questions at the moment...but still thank you for the quick reply...it surely will be of alot help...
6pandn21