Retrieving information for 9 different fields from a single field in another table.

[Xeph]

I'm new to Access. I have some basic knowledge, and a small amount of experience, but not enough for me to be entirely comfortable with it. Historically I've always used Excel when trying to organize my data, but I've recently began the process of trying to move some of it over to Access in an attempt to make things more efficient (as well as learn my way around Access).

Here's the issue: In Excel, I have been manually entering in data for each item. Items are reused quite often, so it's something which could easily be contained in a table and referenced. However, I would need to retrieve the data from multiple fields in one table from a single field in the reference table. For example:

Date	Name	Item 1	Item 2	Item 3	Item 4	Etc.....
2/2	3:15 Purchase	Shoe	Shoe	Book	Shoe
2/3	2:56 Purchase	Book	Shoe	Pens	Headphones

Would need to reference this type of table for information on each item:

Name	Price	Expense
Shoe	$4.99	$1.99
Book	$9.99	$5.99
Pens	$2.49	$0.89
Headphones	$14.99	$8.99

In Excel I have it set up where each row of the first table is a block containing all relevant information similar to:

Date	Name		Book	Pens	Book	Headphones	Shoe	Shoe	Totals
		Price	$9.99	$2.49	$9.99	$14.99	$4.99	$4.99	=SUM<
2/2	3:15 Purchase	Expense	$5.99	$0.89	$5.99	$8.99	$1.99	$1.99	=SUM<
		Tax	Calculate	Calculate	Calculate	Calculate	Calculate	Calculate	=SUM<
								Profit	=Calculate^

It was formatted to look visually appealing, but that's the basic idea. How can I achieve a similar result with access? The end goal is to have it set up so that I can enter the basic sale information into a form, and have the data for each item collected from the reference table and organized into a visually appealing space with all desired data and calculations. I've spent hours poking around with queries and relationships with no luck. Perhaps I need to rethink the way I have the tables set up in the first place? As I said, I'm new to Access, so I apologize if I've done something ridiculous or with a particularly easy solution. Any help would be appreciated, thank you!

[pbaldy]

You've made a design mistake often made by people with an Excel background. In a database you should not have fields for each product. The concept is called normalization:

Fundamentals of Relational Database Design -- r937.com

You'd have an order header table with the basic info and an order details table with a record (row) for each item sold. You can pull the price and expense into that table with a combo box to select product:

BaldyWeb - Autofill

[Gicu]

Not quite sure what you're after (I think you might need to use cross-tab queries to present the data the way you want it), but it would help us if you could upload a sample db with some "dummy" data so we could see your table structure and also some mock-ups of your desired output (report, invoice, receipt, etc.).

Cheers,
Vlad

[CJ_London]

your first table needs to be normalised into two tables

tblPurchases
PK..Date..Name
1....2/2....3:15 purchase
1....2/2....2:56 purchase

tblitemspurchased
PK PurchaseFK...ItemFK
1....1...……………..1
2....1...……………..1
3....1...……………. 2
4.....1...…………….1
5... 2...…………….2
6....2...…………...1
7....2...……………3
8....2...……………4

your second table needs a PK field

tblItems

PK Name	Price	Expense
1 Shoe	$4.99	$1.99
2 Book	$9.99	$5.99
3 Pens	$2.49	$0.89
4 Headphones	$14.99	$8.99

your query will then be something like

SELECT Date, Name, SUM(Price) as ttlPrice, SUM(Expense) as TtlExpense, Sum(Price-Expense) as Profit
FROM (tblPurchases INNER JOIN tblItemsPurchased ON tblPurchases.PK= tblItemsPurchased.PurchaseFK) INNER JOIN tblItems ON tblItemsPurchased.ItemFK=tblItems.PK
GROUP BY Date, Name

Remember - tables are for storing and managing data, not presentation. For presentation use forms and reports. Also as you have demonstrated, Excel tends to store data horizontally, databases store data vertically. Very little of your excel experience will apply to access.

[ssanfu]

Working through these 3 tutorials should help you.
Actually take the time to work through them, don't just look at them...


Good reading.....

[Xeph]

Thank you all for taking the time to take a look and offer some insight. I will look into normalization as this is something which several of you have mentioned, and is a topic I am unfamiliar with.

[Xeph]

Working through these 3 tutorials should help you.
Actually take the time to work through them, don't just look at them...


Good reading.....

I'll check them out, thanks for pointing me in the right direction!

your first table needs to be normalised into two tables


Remember - tables are for storing and managing data, not presentation. For presentation use forms and reports. Also as you have demonstrated, Excel tends to store data horizontally, databases store data vertically. Very little of your excel experience will apply to access.

That last line especially cleared some things up for me. I've gotten so used to using the tables themselves for presentation in Excel. That will be a tough habit to break!

You've made a design mistake often made by people with an Excel background. In a database you should not have fields for each product. The concept is called normalization:

Fundamentals of Relational Database Design -- r937.com

You'd have an order header table with the basic info and an order details table with a record (row) for each item sold. You can pull the price and expense into that table with a combo box to select product:

BaldyWeb - Autofill

That first link is broken for me, but I'm checking out the second one. Thanks for the insight!