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

captgnvr

EAGER LEARNER
Joined
Apr 27, 2010
Messages
144
Dear All

First table with names. second table with store items.

I read the northwind for preparing invoices which does not fit my requirement.

Pls give me links for me to read up on how to prepare report on persons who have made purchases by linking these two tables.

Pls give any links where I can read on how to go about to prepare a report on persons who made purchases; report of stores opening stock, quantity sold and stock balance etc.
 

HiTechCoach

Registered User
Joined
Mar 6, 2006
Messages
4,266
Dear All

First table with names. second table with store items.

I read the northwind for preparing invoices which does not fit my requirement.

Pls give me links for me to read up on how to prepare report on persons who have made purchases by linking these two tables.
With only two tables, I do not see how it will every be possible.

Normally there is at least third junction table.

Pls give any links where I can read on how to go about to prepare a report on persons who made purchases; report of stores opening stock, quantity sold and stock balance etc.
Without seeing all the fields in the two tables, it will be almost impossible for use to give you any kind of direction other than look at the Northwind example.

If you will post more details about the tables, then maybe we can help.
 

captgnvr

EAGER LEARNER
Joined
Apr 27, 2010
Messages
144
THANK U COACH. I have staff names and their details;

i have some store items with price and quantiy;
i will sell the store items and so i want to enter against each names who purchase the items purchased and the amount.



At the end of the month i want to prepare report with all those who have purchased names and the total amount;



I also would like to prepare a report of items in store and balance quantity and amount.


Pls guide me links to read up or u can give me some sample access file which will serve as a guidance to me. I am new to relational database; not getting the hang of how to use staff tables and link it to stores data base and create a third table . I will be eagerly waiting for ur guidance by return. thnks
[FONT=&quot][/FONT]
 

HiTechCoach

Registered User
Joined
Mar 6, 2006
Messages
4,266
THANK U COACH. I have staff names and their details;
Again, that tells us nothing about the table structure and the field. We need to see the table structure.

i have some store items with price and quantiy;
i will sell the store items and so i want to enter against each names who purchase the items purchased and the amount.
You will need to create an invoice.


I also would like to prepare a report of items in store and balance quantity and amount.
Again, You will need to create an invoice. You will also need so way to track purchases of stock, This is usuallky a Purchase Order.

Pls guide me links to read up or u can give me some sample access file which will serve as a guidance to me. I am new to relational database; not getting the hang of how to use staff tables and link it to stores data base and create a third table . I will be eagerly waiting for ur guidance by return. thnks
You use an Invoice like in the Northwind.

Hopefully you are not needing this anything soon. It can take years to learn to create an Inventory Control system properly.

See:

Inventory Control: Quantity on Hand

That is about as simple as it gets.
 
Last edited:

captgnvr

EAGER LEARNER
Joined
Apr 27, 2010
Messages
144
D/Coach: Thought you gone for the day and thank you for the time. ok here it goes:

1. table1: names; passport; date of joining; staff code.
2. table2: item names; price; quantity

At present I am doing it easily in excel97. I want to migrate to access. Our company still has only office97.

So in excel, I have the staff details in one sheet, and store items in another sheet.

In third sheet I link the names in A4 and in top row from B1 I link the products; in B2 I link the price. Against each name I have two row for a person. When I enter the quantiy the amount for that item is shown and i sum it off at the end of the columns.

I would like to do it in access. I have the table1 and table 2 as i have mentioned. From here I want some guidance how to make the third table taking the names of the staff and store items etc.

Pls tell me when u will be logging off. I am on a ship in black sea. Pls give the initial push to take it from there. Thanks for the link of allenbrown. Will be reading it in meant time.
 

HiTechCoach

Registered User
Joined
Mar 6, 2006
Messages
4,266
D/Coach: Thought you gone for the day and thank you for the time. ok here it goes:

1. table1: names; passport; date of joining; staff code.
2. table2: item names; price; quantity

At present I am doing it easily in excel97. I want to migrate to access. Our company still has only office97.

So in excel, I have the staff details in one sheet, and store items in another sheet.

In third sheet I link the names in A4 and in top row from B1 I link the products; in B2 I link the price. Against each name I have two row for a person. When I enter the quantiy the amount for that item is shown and i sum it off at the end of the columns.

I would like to do it in access. I have the table1 and table 2 as i have mentioned. From here I want some guidance how to make the third table taking the names of the staff and store items etc.

Pls tell me when u will be logging off. I am on a ship in black sea. Pls give the initial push to take it from there. Thanks for the link of allenbrown. Will be reading it in meant time.
I will be online for maybe at hour more. I am jsut posting as I have time between tasks.

Since Excel is not a relational database, I would urge you do not try to compare what you have done in Excel to how you should do it in Access.

I have done conversions from Excel to Access. I did not even look at the design of the spreadsheets to help design the Access application. I normally had already written the Access application before I ever looked at the spreadsheets. I learned just enough about the spreadsheet to get the data out.


What you need some type of transaction. This is typically an invoice (header and detail tables). I still recommend that method. I can not thing of any reason why you would even not want to do it that way. It has proven to work well for me in over 30+ years of specializing in Accounting/Inventory Control systems.
 

captgnvr

EAGER LEARNER
Joined
Apr 27, 2010
Messages
144
D/Coach

Thanks for fitting in between ur busy tasks. I only gave excel example what I was doing but I am very keen on doing it in access as I have the crew data in access and it will be easy to manipulate data. I have been reading up a lot but not getting the basic. Even your link to Allen Browne is straight away after the tables are made. I am stuck at table3. So pls tell me how to combine the table1 with crew info with table2 with stores to make table3 with info of crew and the store items so that I can call for the names and get to enter the quantities of items purchased. As a matter of fact I was going thro so many articles but unable to get the basic one to start with. Hope u wl give me that link.
 

HiTechCoach

Registered User
Joined
Mar 6, 2006
Messages
4,266
D/Coach

Thanks for fitting in between ur busy tasks. I only gave excel example what I was doing but I am very keen on doing it in access as I have the crew data in access and it will be easy to manipulate data. I have been reading up a lot but not getting the basic. Even your link to Allen Browne is straight away after the tables are made. I am stuck at table3. So pls tell me how to combine the table1 with crew info with table2 with stores to make table3 with info of crew and the store items so that I can call for the names and get to enter the quantities of items purchased. As a matter of fact I was going thro so many articles but unable to get the basic one to start with. Hope u wl give me that link.
I still recommend:

What you need some type of transaction. This is typically an invoice (header and detail tables). I still recommend that method.

Why do you not want to create an Invoice?
 

HGMonaro

Registered User
Joined
Apr 22, 2010
Messages
61
really simply...

1. table1: name; passport; date of joining; staff code.
2. table2: item name; price
3. table3: name; item name; quantity

table3 holds who purchased what and how many.
 

captgnvr

EAGER LEARNER
Joined
Apr 27, 2010
Messages
144
D/Hgmonaro
Thanks for the start. The main road block in making the third table is:

1. Do I have to manually enter all the names again in the new table3??

2. If I enter the 'item' field in table3, does it have to be a lookup field?? Otherwise what 'item' is purchased by the person how to enter. One person might buy 5 items.

3. Is there way to use a make table query using table1 and table 2 and make table3? Once again how the 'item' field going to be??

4. For example if I have 100names and the store items 200 variety, how to go about making the table3?

Once I get this I can make good progress.
 

HiTechCoach

Registered User
Joined
Mar 6, 2006
Messages
4,266
D/Hgmonaro
Thanks for the start. The main road block in making the third table is:

1. Do I have to manually enter all the names again in the new table3??

2. If I enter the 'item' field in table3, does it have to be a lookup field?? Otherwise what 'item' is purchased by the person how to enter. One person might buy 5 items.

3. Is there way to use a make table query using table1 and table 2 and make table3? Once again how the 'item' field going to be??

4. For example if I have 100names and the store items 200 variety, how to go about making the table3?

Once I get this I can make good progress.
There are many design/data normalization issues with what HGMonaro posted. It probably is over simplified for a good relational database design

First thing:

1) Every table should have a primary and it should be system assign. This usually an Autonumber data type.

2) Use the primary key to create realtionships.


To eliminate the duplication of data, you should use two tables to create the junction in your case. A transaction header table and a transaction details table. You need the transaction details table because there can be multiple items per transaction. This is using a relational model which is very different than it would look in a spreadsheet where data is duplicated a lot.

See the attached Order Entry Template.
 

Attachments

captgnvr

EAGER LEARNER
Joined
Apr 27, 2010
Messages
144
D/Coach, thanks and finally getting nice guidance to start with. we are to use only office97. However I am seeing the order entry file sent by you and getting much wanted directions. If possible can give the ordersentry zip for access97.

Now have put n_code as primary key for staff info and put i_code as primary key for item codes for store items. Now pls tell me do I have to make a new table combining the fileds like n_code, i_code, items, quantity, price?? If so can I make the 'items' field as look up field?? The doubt is how will I enter items purchsed bcos each person might make multiple purchases and so feel the items field must be lookup field so that one can select from the drop down list.

Very enthusiastic for making progress but frankly dont know how long this is going to last as fear hitechcoach might suddenly back off?

So sincere request to be with me till I get the first hurdle of making this form to enter purchases against each person and make a report of each person purchases and a consolidated sales for the month.
 

captgnvr

EAGER LEARNER
Joined
Apr 27, 2010
Messages
144
D/All

From the guidance I got, I have made a headway but miserable progress.


Sorry to say I have read many links but none clearly state how to connect staff data with stores data and to get sales etc. Even in Northwind "show me" the steps are tooooooooo brief for a new comer to understand.

I have attached the file and pls see and tell me how to get name of the crew and the purchases they make in a month; how to get individual report and also total sales of each item and stock balance etc.

Pls help.

brgds/captgnvr
 

Attachments

HiTechCoach

Registered User
Joined
Mar 6, 2006
Messages
4,266
No need to worry. I will not back off.

I might have to take short Tornado and Hail Storm breaks as they pass on by.

we have already had over 20 Tornadoes this season and lots of with Softball/Grapefruit size hail. recently had Hail so much it was almost a foot deep.
 
Last edited:

captgnvr

EAGER LEARNER
Joined
Apr 27, 2010
Messages
144
:) HA HA. So eagerly I opened this thread to see any new classes or guidance. I guess I have to wait more. Pls see the file attached and guide me. I will put in all my efforts to get it right. Pls guide me. Once I get to this stage of opening a form to get the name of the crew and enter the product and purchase and send it to the table to store then onwards I can make huge progress.
 

HiTechCoach

Registered User
Joined
Mar 6, 2006
Messages
4,266
I did forget that you were using Access 97.

As soon as I can get to a computer with Access 97, I will take a look at you attached database.
 

PNGBill

Win10 Office Pro 2016
Joined
Jul 15, 2008
Messages
2,269
captgnvr, I won't try and break into the assistance HiTechCoach is giving you as my level is quite basic but just to add to your vocabulary.

You have Tables (lists) to store data. You may well have many tables as your database and requirements grow.
All Tables should have as it's first field a Primary Key field - often an autonumber. Just like your employee number to identify individual employees. And usually these tables are linked to one another by Relationships.
Imagine a table of the companies ships would have VesselID as PrimaryKey field and the Vessel Name as the next field followed by date launched, tonnage etc.
A Seaman Table would have EmployeeID as the PrimaryKey followed by FirstName, LastName, BirthDate, etc and likely include a field VesselID which would have "0233" entered as data being the PrimaryKey number for the Queen Mary. You could then do a query to search Seaman Table to get a list of crew members currently serving on this vessel and VesselTable to get the vessel Name and other details.
In this example, There is a relationship between the Vessel Table Primary Key and the Seaman Table VesselID field. This is shown on your screen as a line between the two tables. Relationships are very important.

Sometimes you will Make a Table "on the fly" so to speak and this could be a permanent or more likely temporary table.
If you do this again, "make a table" then the first table and all it's data is lost forever. ie the new table over writes the old table.

How do you get information into a table and out of a table and how do you compare, manipulate, calculate and sometimes mess :eek: up the data in a table?

This is where you should spend some time and be aware of the different tools and there correct name and use.

You have Queries. These can search one or more of your tables and collect data that is related either directly or in a roundabout way depending on how complicated your needs are. You can have queries nested so to speak. ie one query collects some data and then another query will use that data and do something with or too it and then another query will do another task. Say you 3 queries. You normally run the 3rd query and the other two will automatically provide the data as required.
Types of Queries include Select, MakeTable, Append, Update, Crosstab and maybe more. MakeTable would make your Temporary Table. Append would add data to a table, Update will change some or all of the data in your table, Crosstab can give a spreadsheet like view of your data and the most common is select which just selects your data but i shouldn't use the word "just" as it can do a lot of work but it won't disturb existing data.


You have Forms. Forms allow you to view data in one or more of your tables and most often these use a query to supply the data as in the case of looking at a customers invoice. The data on the invoice is not all in one place. It has been assembled by queries and is just displayed in a format that makes sence to you but when you close the form, all is gone.
Forms can allow you to Edit, Delete or Add some data to your tables - stored information - or to a query which would be information that is required to complete the next task a query is attempting to do.

You have Reports which are similar to Forms but can be Previewed on your screen or printed onto paper or emailed.

To get back to your Database task. You will need to use Table, Queries, Forms and Reports to both construct your Database and to use it.

What I have found is that a Database may take longer to setup then say an excel project but once it is done it will provide a better facility for both entering data (eg buying and selling items) and then to have Reports and Balances of the events and end of period reconciliations.

Enjoy your project and trust the above assists.
 

captgnvr

EAGER LEARNER
Joined
Apr 27, 2010
Messages
144
D/Bill, your detailed post and time devoted is well aprreciated. The confession is I am comfortable with one data type tables like ship stores or ship crew data bases. The ignorance came to light when I decided to use the crew table with personal items database.

So pls see the file attached and help me in how to make the third table so that I can use the names from crew table to use it along with personal store items and make a form to enter the sales against the name selected from a combo box.

I keep watching for the guidance and very eager to cross the first hurdle --i.e to get a form to enter the purchases agains crew names and make list of persons and the amount for the month and also a stock list of items sold and amount and stock balance.

Looking forward to n wrgds/captgnvr
 

PNGBill

Win10 Office Pro 2016
Joined
Jul 15, 2008
Messages
2,269
I will try explain why you have more then one table in 99.99% of databases.

Conventional ledgers or record books would likely have a heading "Ships Crew", "Ships Stores" etc.
Ships Crew would have All the data you would need on every person on board and Ships Stores, likewise.

How would you keep track of what crew member had what ships stores drawings on what day and how were these paid for. Also, how many of each item remains, or should remain, in the ships stores and how much was paid for them, where were they purchased from etc.
This gets very cumbersome in the hand written method where you would need all sorts of records be they cards, invoice books, recp't books etc and many of these would have duplicate information.
eg the old handwritten invoice includes Full name, Address, etc

When this is handled by a database you still need some resemblance of the old method but you no longer need to keep the full information in each "ledger" as the power of a computer can quickly re assemble this information for you as if it was there all the time.

This means the Invoice you want to view does not really have to exist as the system will recreate this when ever you need.

The list of crew on the "Dog Watch" is no longer written down but again created should a request to view the screen (form or report) or hold a printout (report) arise.

The system will create these reports and forms for you by eg a Select Query looking at both the CrewTable and the WatchTable and selecting data from the crew table where the DogWatchID is used.
A form will allow you to update shift changes as they occur - change one field only in the crew table. Advanced systems can do this automatically.

Out of the 25 crew members it will only collect data for the 4 that are on the requested watch and then put this data into the pre defined Form or Report.

For your Ships Stores you may well have a new table that collects the data of each Transaction. This data could be CrewID, TransDate, StoreItemID, SaleQty and SalePrice.

This information will be added to the TransactionTable one record or line for each item. No item description is required because the database will get this from the StoresTable as required. No Names are needed because this will be found in the CrewTable as required. No calculations are required because this can be done each time it is needed. The price is normally stored because this can change in the StoresTable but the qty and price will always give the same for each invoice amount.

I trust this is of assistance and may help you to understand why you would need more then one or even two tables to achieve the result.

The forms and reports you will need can be created or at least started by the wizard once you have assembled the data which is normally done by a query.

Be prepared to spend some time on getting the forms and reports looking and working just as you want and this may change as you use the system.

When you open access from the program list you get an option to create a new database. Create a few of these and see how the forms and reports work.
You know how to view the reports and forms in design view??

Also, when in the database, click Tools and Relationships and you will see the view of how the tables are joined.

No reason you could not copy a form or report from one of these and then edit it to suit your needs. Remember that when it looks too messy, delete it and start again - for the sample databases that is.

On your own database, keep copies as you go into each step so you can start again if, when, a major foul up occurs.
 

captgnvr

EAGER LEARNER
Joined
Apr 27, 2010
Messages
144
D/Bill, again thanks and saw u online while I was working and got ur second post. The ledger concept is understood and a good anaology. With reg to ur question if I am familiar with reports and forms and design mode, I confirm well aware of it to handle it to a certain extent. I am only stuck at this third table. Like I said I have attached my initial progress in this and hope u hv seen it in this thread.

What I am going to do is like u said - redo based on the ledger concept and will come back. In the mean time pls see my mdb file attached in this thread. brgds/captgnvr
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom