Creating Forms in Database

[LucianoPena28]

Hey guys,



I'm a bit rusty at Microsoft Access, but I've been tasked to create a database through Access for a very small department where I work for but I'm having difficulty creating forms and calculating my Total SUN on the Query "Total Sum of All Items".

I want to have my main access UI Page to have the user choose a fiscal year, dating between April 1 - March 31 of the following year through a DropDown option that would display from Year - Year.

I want to calculate the total amount that has been generated for all items being ordered from a specific date period selected in the dropdown "Fiscal Year" mentioned above also.

I have no idea how to query these functions for all my attempts give errors.
Can someone show me how to make this a bit better?

If someone can help, that would be amazing! I will include the database in an attachment.

Please Find Attachment named "GI3 PROCUREMENT"
Attachment 47940

[CarlettoFed]

I think he has a lot of confusion about how to properly structure the tables for correct data recording.
In order to help you it would be necessary to have a step-by-step description of what you should do with this database.
Example:
- do I order items of a certain type (what you have called Code) from a Supplier?
- Does the Supplier send me the items in order, all in a single supply or even in several supplies?
etc ...

[ArviLaanemets]

Based on what you did describe:

In your DB you need tables like:
tblItems: ItemID, ItemName, ...;
tblOrders: OrderID,OrderDate, ...;
tblOrderRows: IrderRowID, OrderID, ItemID, ItemQty, ...

Then you need forms:
for registering/editing items;
for registering/editing Orders;
a subform of Orders form for registering/editing order rows;
a form where user can select reports and set conditions for them, and then run them to print/preview/e-mail/etc..

For reports, you probably need various saved queries, like:
a query returning yearly totals;
a query returning monthly totals;
etc.

Based on those queries, you need matching reports. Reports are called when according button is pressed on reporting form. The button's OnClick event runs the command to open the report, and adds filter conditions according users choices on report form. E.g. User selects in Report sheet Monthly Totals report, sets the start and end months as January 2022 and Mai 2022, sets the report to be previewed, and clicks Report button - a Monthly Totals report for this period is opened in user's computer. User can look at info, or print the report. In case user selects on report form the report e.g. to be printed instead, the report is send to printer immediately - without displaying it in user's computer.

[orange]

Please describe the business involved in simple (non database) terms. Start at the 30,000 ft level and gradually add detail (3-5 sentences).

I looked at your relationships. What do procurement, procurement1 and procurement2 represent? Where do they fit in your business process?

There is no referential integrity set up on your relationships. You may be missing some inherent validation performed by Access/ACE.

Strongly suggest you get your tables and relationships designed and tested before jumping to forms and UI.

[LucianoPena28]

Maybe if I show you my flow diagram it would help all. I also received a UI layout request in which they preferred.

Would you consider a flow diagram or an explanation of 30,000ft?

[orange]

Post whatever you can to help.

[LucianoPena28]

I have included the spreadsheet that is used on a daily basis to record records.

Please find attached file below:
GI3 PROCUREMENT.zip


Please note that the Procurement table is the main table that will control the flow of data being stored and will also store data on it's own when displaying records.

Please see attachments below for a concept.

[orange]

How will Access fit with your current process?

[LucianoPena28]

Access will be able to better provide a more user friendly approach for new an existing users in regards to entering data.

Since licensing of additional software is more costly, we wanted to use what we already have now, and since Access supports Forms, it's an easy way to use the resources one has to keep track of the records.

You can contact me directly through skype at: https://join.skype.com/invite/vRkosTCUZVHO

[orange]

Richard Rost, Access MVP and Youtube person just announced the following yesterday:
Go to this link and read the instructions

OK YouTube Fans... by popular demand, I've decided to post my entire Access Beginner Series right here on my channel, absolutely free of charge. Yep, that's right. All 9 levels, which is over 60 lessons, totaling over 15+ hours of video, completely free here on my YouTube channel. Here's what I'm going to do: You can already watch Beginner Level 1 for free. It's over 4 hours long, and you can find it here: 599cd.com/ACB1 Next, I'm going to post the introduction and first lesson from Beginner Level 2. I normally charge $1 for all of Level 2, but I'm going to post it free here on my YouTube channel. ACCESS BEGINNER 2, LESSON 00: INTRODUCTION https://youtu.be/zzYUA9KRk-A Now here's what you need to do: When you finish watching each lesson, If you want to see the next lesson after that posted, you need to... - LIKE the video - Make sure you are SUBSCRIBED to my channel (which is free) - Post a COMMENT saying, "I'd like the next lesson, please." That's it. As soon as each video gets enough views, likes, and comments, I'll post the next one. Obviously my goal here is to get more view on my channel, so if you want to help me out, be sure to SHARE my lessons with your friends and colleagues on social media. YouTube makes it easy. Just click on the share button below each video. The more views, likes, and new subscribers I get, more free videos I'll keep posting.





He has lots of videos on youtube. His site and this new offering should get you help with your Access.

[LucianoPena28]

Okay thanks