How to mix two tables data to prepare report? (1 Viewer)

PNGBill

Win10 Office Pro 2016
Local time
Tomorrow, 02:03
Joined
Jul 15, 2008
Messages
2,271
TBL_CREW is your Crew Data, TSLOP_LIST is your list of Store Items with description and price and TCREW_SLOP is the data on items issued to crew members - Correct?

Queries - Q_Items is just a list of items in stock with their averagre price, qty and description - Correct?? and Query Q_SALES is a selection of items sold. - correct?

Form is supposed to do what?? allow you to enter sales?
 

captgnvr

EAGER LEARNER
Local time
Today, 18:33
Joined
Apr 27, 2010
Messages
144
D/Bill, yes the form I am making is to have the flg:

1) a combo box to show all the names and select the crew who make the purchase.

2) on selecting the crew, the subform in it must show all store item with price, where I can enter the quantity and the amount to come by using the formula qty * price.

3) on closing this form, all the purchases made and entered must be saved in a table for example TBL_SALES, so that I can go to the next step to make report of collecting the sales for each person for the month and also prepare the stock balance table or report.

Till what time can u post the help so that I can look forward to eagerly?

PS: I am sprucing up the last mdb file I posted after reading the ledger concept and in the mean time ur sugestion will be incorporated as it comes.
 

captgnvr

EAGER LEARNER
Local time
Today, 18:33
Joined
Apr 27, 2010
Messages
144
D/Bill,

Based on ledger concept I just about made some changes and have attached the file. Now the amount transacted does not get stored in the T-SALES table.

I do feel I am progressing now and pls see the new file attached.
 

Attachments

  • CREW_STORES.mdb
    186 KB · Views: 80

PNGBill

Win10 Office Pro 2016
Local time
Tomorrow, 02:03
Joined
Jul 15, 2008
Messages
2,271
I have had a look at your database but I am unable to solve the problem for you.

This is partly my experience and the time I have available right now.

HiTechCoach said he will get back to you and his support is worth 10 times what I can do for you.

Sorry I couldn't wave a magic wand.
 

PNGBill

Win10 Office Pro 2016
Local time
Tomorrow, 02:03
Joined
Jul 15, 2008
Messages
2,271
If T_AMT is the Average Price on the day of the sale then yes, this about all you need to store. SalesID, Customer, Item, (P_Date is sales date??), QTY and Price.

Question now is how to add data to this table each time a sale is made.

essentially this is quite easy but as I mentioned, I can't get my head around the issue right now and your more expert assistance is not far away.
 

PNGBill

Win10 Office Pro 2016
Local time
Tomorrow, 02:03
Joined
Jul 15, 2008
Messages
2,271
I just had a look at Northwind.

If you have this, look at Form Orders which uses Query Order Query for its record source.
Query Order Query uses Tables Customer and Orders for it's record source.

Form Orders has a subform Orders Subform and it uses Query Order DetailsExtended for it's record source and this query uses tables Order Details and Products for it's record source.

here is a stripped down version of Northwind that may help you.
 

Attachments

  • Northwindexampledb1.mdb
    782 KB · Views: 98

captgnvr

EAGER LEARNER
Local time
Today, 18:33
Joined
Apr 27, 2010
Messages
144
D/Bill, I just got back after rounds and I am going thro the northwind file. In my opinion, you dont seem like the guy to give up also bcos of the pains u hv taken and time to pen down so much material. For this help itself I will put in continuous effort to achieve the desired.
 

HiTechCoach

Well-known member
Local time
Today, 08:03
Joined
Mar 6, 2006
Messages
4,357
I had re-intalled Access 97 now. Took a while to find the CD.

I took the Order Entry Template that I have posting and imnported your data into it.

See the attached.
 

Attachments

  • CrewStores97.mdb
    654 KB · Views: 93

captgnvr

EAGER LEARNER
Local time
Today, 18:33
Joined
Apr 27, 2010
Messages
144
D/COACH
All of a sudden the problems are solved and I am very pleased. But disappointed bcos I have not gone thro the mill to learn in detail.

Since I have the finished product Thanks to you, I will now back track and leisurely figure out each and every step of it. I really do appreciate your time for loading access97 and doing this help for me. I am grateful and thanks.
 

HiTechCoach

Well-known member
Local time
Today, 08:03
Joined
Mar 6, 2006
Messages
4,357
D/COACH
All of a sudden the problems are solved and I am very pleased. But disappointed bcos I have not gone thro the mill to learn in detail.

Since I have the finished product Thanks to you, I will now back track and leisurely figure out each and every step of it. I really do appreciate your time for loading access97 and doing this help for me. I am grateful and thanks.

You're welcome!

You can still go through the learning process. You could still try to build the same forms, etc in your database using mine as a guild.

You can use the database I posted as a starting point or as a guide. Your choice.

Like the old saying "A picture is worth a thousand words", I find that it usually helps to grasp the concepts of a relational database when you can see a working example.


You might also want to look at this:

Access Basics
 

captgnvr

EAGER LEARNER
Local time
Today, 18:33
Joined
Apr 27, 2010
Messages
144
D/Coach

Absolutely, you have given me a very good platform and a lader to climb. From despair to hope. I am so happy and confident that I have a model to rely on for each step and that too a professional one, all under one roof and at one go. Thank you very much yet again.

In short ur nick is very apt and I must repeat "THE PROBLEM SOLVER" and you are indeed.:D
 

captgnvr

EAGER LEARNER
Local time
Today, 18:33
Joined
Apr 27, 2010
Messages
144
D/COACH

I did make a good headway on sunday. Now the new doubt is, when I have the report on sales, the price may not be same for the whole month on account of new stock coming. So how to overcome this. For example 10 staff purchased at the month beginning price of 10usd and after 20th of that month the price is 10.5usd?

Bcos in the equation it will quantiy x price; and the price is not steady factor.
 

HiTechCoach

Well-known member
Local time
Today, 08:03
Joined
Mar 6, 2006
Messages
4,357
D/COACH

I did make a good headway on sunday. Now the new doubt is, when I have the report on sales, the price may not be same for the whole month on account of new stock coming. So how to overcome this. For example 10 staff purchased at the month beginning price of 10usd and after 20th of that month the price is 10.5usd?

Bcos in the equation it will quantiy x price; and the price is not steady factor.

This is already handled.

How it works:

Every transaction actually store the unit price. If you look in the table [Order Details] you will see that the price is stored along with the quantity.

Price Changes:

When the price changes for a product, you can update the price in the Products table. This new price will be the default for NEW transactions only. It will not change the price in existing transactions.

Bcos in the equation it will quantiy x price; and the price is not steady factor.

If you base your sales report using the quantiy x price in the [Order Details] table, you will have the correct price for that transaction.

The key is that you must store the price and quantity for every transaction in the [Order Details].
 

captgnvr

EAGER LEARNER
Local time
Today, 18:33
Joined
Apr 27, 2010
Messages
144
D/COACH
Got the point wrt quantity x price aspect n thanks. Two days gone by studying the file u gave. Feel should be figuring it out soon.

At present I am deeply trying to figure out to get the sales to be seen on clicking the staff name.
 

captgnvr

EAGER LEARNER
Local time
Today, 18:33
Joined
Apr 27, 2010
Messages
144
D/COACH

I can feel that I am almost there but some thing going wrong and I get the below error message.

Seeing your example where u superimposed my data into ur orders.mdf and renamed it as crewstores97.mdf, I have incorporated most of it in my main program.

So when I pick the name, go into the subform to add the sale items, I get the below message:

"""""The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again. (Error 3022)""""

So I went and changed in the table design to accept duplicates and still same message when I add more sales for the selected person in the FORM WITH SUBFORM.

The problem is I cant even send the file where I am working bcos it has bcome 50mb bcos of adding some printed forms and attaching it as jpg. Yes fifty mb, even after compacting. If I zip it then it is about 4mb.

Tried on my own and still trying but unable to locate the fault. Also feeling bad that for so many days I am on this and troubling u. Pls help me since I am almost there.
 

captgnvr

EAGER LEARNER
Local time
Today, 18:33
Joined
Apr 27, 2010
Messages
144
D/COACH

I am trying to learn and use your terminologies etc. For example I have also stripped down my 50mb mdb file and created a new one copying only the needed tables, queries and forms to locate why I am not able to enter in form F_SALES after selecting the name and then into the subform. I will get some peace as I have been on it non stop to get it going.

Pls see the file attached.
 

Attachments

  • FOR COACH.mdb
    142 KB · Views: 78

captgnvr

EAGER LEARNER
Local time
Today, 18:33
Joined
Apr 27, 2010
Messages
144
D/COACH.

HURRAYYY. Pls ignore/disregard my last post. I located the fault which was in the table saledetail where one field called saleid had no duplicates and once I changed it to yes-duplicates ok, it seems to be fine. I cant thank you enough. My first hurdle is over to get the name and purchase items table and form. Will move onto to make reports based on it.
 

HiTechCoach

Well-known member
Local time
Today, 08:03
Joined
Mar 6, 2006
Messages
4,357
D/COACH.

HURRAYYY. Pls ignore/disregard my last post. I located the fault which was in the table saledetail where one field called saleid had no duplicates and once I changed it to yes-duplicates ok, it seems to be fine. I cant thank you enough. My first hurdle is over to get the name and purchase items table and form. Will move onto to make reports based on it.

Great Job! :)

By allowing you figure it out on your own, you have learned a lot and you will probably retain it.

You are making great progress!
 

captgnvr

EAGER LEARNER
Local time
Today, 18:33
Joined
Apr 27, 2010
Messages
144
D/Boyd
This is to inform that ur student was held up in inspections and 24 days went by without sitting on my project. Now that all of them gone and all to myself, have started to work from where I left off. I have refreshed all what u hv guided and somethings are not right even in the ordersentry sample file also. Will bring it to ur notice and attention. brgds/captgnvr
 

Users who are viewing this thread

Top Bottom