Question help in some calculations and searching

[Nokia N93]

Hi to all
Hope you are fine and in a good health

i need your help in this database
this small shop, when a customer come, we entered his data, he will come with maintenances , let us say he came 3 days ago, he make a maintenance of 100 $, but he paid only 40, so the remaining is 60, today he came to make another maintenance that cost 200, he paid only 100 (60 the remaining and 40 the new one) , so we want from him now 160.

how can i make a report showing all his transactions , SHOWING the Total money needed from him?

another thing i want to search for a specific customer to show all the previous transactions and to add a new one, how can i do this search ??
I tried to do a combo box, but it didn't retrieve the transactions in the subform :banghead:

I tried to make it, but i think there is something wrong in it

Can you please help me in that
Thanx in advance

 

[apr pillai]

I have made some changes (on Tables) and created three new Forms for easy querying of data from your database. The modified database is attached.

Open the form frmMain and select a Customer Name from the Combobox on the Header of the Form. By default Customer ID number 1 will be selected.

When you a select a customer name on the combobox that customer's Maintenance & Detail records will appear on the Sub-Forms.

Maintenance Form is related to the Customer ID (on the combobox), MID of Maintenance Form is related to the Maintenance Details Form.

Sub-Forms cannot be linked directly. Sub-Forms can be linked to the Main Form only. But, when we need to establish relationship between sub-forms on a Main Form we can do this with a small trick.

Transfer the related information from the first Sub-Form (Sub_Maintenance) into an Unbound Textbox on the Main Form and use this Unbound Textbox Name (txtMID) to use for Link Master Field reference.

 

[Nokia N93]

hi thank you apr pillai very much

But i'm feeling LOST :banghead:
excuse me for bothering you, i attached this database, all the calculations are okay. but can you please tell me how to search for a specific customer showing his records ?? i tried but it seems like their is something missing

also i want a report to be generated for all the transactions to a specific customer, i think i must make a query

thanks again for your help

 

[Estuardo]

G'd Afternoon All,
I saw your db, First of, your Orders form is not properly related.
You have to differentiate between a combo box to select values and a combo box to find values.
Build another table for Maintenance Types otherwise you'll be duplicating data.
As you already notice you can't sum a grid like in excel. you may use the vba function dsum or a DAO/ADO recordset.
See the attached db and understand what and how it does. then build your report.
G'd luck

 

[Nokia N93]

thanx Estuardo
what i want is to search for a customer , so that i can add more details for him :/
the problem is that when i search for Adam , his personal info. appears but not the subform and his details

 

[Estuardo]

I'm confused... what details? i see no table with details. Your db has three tables Customers, Orders and Orders Details.
Have a look at the db on my previous post, you'll see the combo box implementation to find a record record in the form.
By the way which one is the your db sample: The last one you posted (as i supposed) or the one posted by Pillai? Keep just one version or let us know in which stage is your project.

 

[Nokia N93]

I'm confused... what details? i see no table with details. Your db has three tables Customers, Orders and Orders Details.
Have a look at the db on my previous post, you'll see the combo box implementation to find a record record in the form.
By the way which one is the your db sample: The last one you posted (as i supposed) or the one posted by Pillai? Keep just one version or let us know in which stage is your project.

Hi Estuardo , im so sorry , i was a little busy the last days
i attached my DB, the problem is when i search the frmCustomer for john, it retrieves his records in the subform successfully , but if i want to enter details (maintenanceType, cost, ... ) it enters is but don't save it

i tried and tried but there is something wrong
sorry for bothering you

 

[Estuardo]

First of all you are not bothering any one. If we're here is because we want to .
I guess that you're missing something here.
Your Customers form is just to enter data about customers no to add orders
The Orders form is to add orders to existing customers.
Why?
Let's suppose you add orders to your customers like you add any other type of detail.
In a short while it would be impossible to add, edit, find and delete orders. Why?
because you will have an endless list of orders in a single place. That's fine for viewing like a report.
Please take a look at my db sample (post N4) It is almost the same db you originally posted, plus the features you required.

 

[Nokia N93]

Esturado your Db (post N4) it deals with the orders number
but in my db the ID of the orders is not important, what is important is to add orders for the same customer ( after adding him or searching for him)
also a full report of the customer with all of his orders must be shown
according to your previous reply that this thing is difficult, So the alternative what would it be ??

 

[Estuardo]

I guess we're going in circles . In the Orders form you can add as many orders to the same customer as you need. That's what orders form is for.
Add Orders to the same customer doesn't means that should be the same order. In fact orders are not made the same date and not necessarily sequential, also if you want to bill a given order you need just that one order not all of them.
You can't have one form to do it all. You should separate every task or entitiy. like:
Forms
One for customers
One for Orders
One for Payments
One to search
One to view
Reports
For totals
for customers
for orders
for billing
for payments
etc.

Back to the modified db. If you open the orders form, you can add orders to the same customer. Click the add new button and add the order. Or
Search for the order and you can add/edit/delete rows.

In the customers' form you can see the last 12 orders (you can change that) in a read-only mode. You can add a form/report or both to search/view all orders for a given customer or all customers with all their orders, or any other combination you may need.

In your last DB you're adding orders in the customers form. How are you gonna do to bill just orders for this month? or totals by month?
I don't want to make any assumption but i guess you're thinking like an excel sheet and access is not at all like that.

I'll work on the modified this weekend to show you what i mean.

 

[Nokia N93]

Estuardo May i ask for the modified Db :$ :$ ,,, really i don't know what to say to thank your help

 

[Estuardo]

Hello!
I'm sorry i couldn't get back to you before, i just had more work of what i'd expected. I didn't quit . i'll try to make time today and come back with something.
cheers!

 

[Estuardo]

Hello,
I just finished prepared the input data stage. With this i think you can move on to work in the output and all the helper forms.
I don't know if this is what you're looking for, but at least will give you some basic guidelines to build your own.
Keep the images folder in the same directory where you save the mdb file, otherwise it will break. The reason is that this demo has no embedded pictures, because most of the times they loose quality and make the db file bigger.
I hope it helps and i'll wait for your feedback.

 

[Nokia N93]

Hello,
I just finished prepared the input data stage. With this i think you can move on to work in the output and all the helper forms.
I don't know if this is what you're looking for, but at least will give you some basic guidelines to build your own.
Keep the images folder in the same directory where you save the mdb file, otherwise it will break. The reason is that this demo has no embedded pictures, because most of the times they loose quality and make the db file bigger.
I hope it helps and i'll wait for your feedback.

thanx alot Esturado, this helped me alot
You are Great, thanx