merge data

[keessieb]

I'm working with a 90 MB Access file looking like this:

Book1
	A	B	C	D	E
1	Article	Name	Ingredient	Price	Amount
2	123	Cake	k1000	23,5	4
3	123	Cake	k2999	10	5
4	123	Cake	k3332	12,03	5
5	568	Pie	k2722	25,33	8
6	568	Pie	k1000	23,5	2
Sheet1

Is there a way to get it like this?

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Article	Name	Ingredient	Price	Amount	Ingredient2	Price2	Amount2	Ingredient3	Price3	Amount3	Ingredient4	Price4	Amount4
2	123	Cake	k1000	23,5	4	k2999	10	5	k3332	12,03	5	0	0	0
3	568	Pie	k2722	25,33	8	k1000	23,5	2	0	0	0	0	0	0
Sheet1

Would be a nice method to make it a "real" database! (And a lot smaller too!!

Many thanks in advance!

 

[Joe4]

Actually, they way you have the data stored now is not only a "real database", it is actually in a better Access format than the second one you wish to switch it to (it is considered to be in better form, and allows for more flexibility).

For example, let's say you wanted to search the table for everything that uses ingredients k2999. In your first table, you would only have to search one field. In your second, you would have to search multiple fields. Also, what happens if have to add new items that have 5,6,7, etc ingredients? You would have to keep adding fields to your table, and your any queries would have to be modified. Not good design!

The great thing about Access is that you can take a table that is in normal form, and get almost anything you want out of it. If you have the table in the original form you have displayed, you can simply create a Cross-Tab query to display the data in the form you wish to show it in.

You want to reference Access help or Access books for more information on "Normal Forms" of data, which talk about the best way to design tables, and check out the different types of queries you can create (especially cross-tab queries, in this instance).

 

[keessieb]

Thanks for replying, go try make a cross tab query....

 

[SydneyGeek]

I agree with the first post -- you already have the data in the correct format. To get what you want, build a report. You'll have a slightly different layout when you finish -- something like

Article 123 Name Cake

Ingredient Price Amount
k1000 23,5 4
k2999 10 5
k3332 12,03 5

etc.

You can also insert fields for Ingredient Count, total value of ingredients, and more.

Hope that helps.

 

[bat17]

I am going to disagree with the others The first table, if it is a single table, has not been normalised. It looks as if it should be split into at least two or three separate table judging by the sample given. The file will be bloated because it contains a lot of repeated data that should be stored once in a lookup table.
Unless there is a lot of data 90mb still sounds very large. In Access pictures can rapidly bloat the file size so if you are storing them it is worth looking at storing them separately and referencing them form the DB.

Have you tried repairing/compacting the DB to reduce its size?

This link on Normalisation is not Access specific but will give you the basic ideas of table structure

HTH

Peter

 

[Joe4]

Yes, it is true that the first table could be normalized further, especially with Name seeming to be dependent on Article. The point I was trying to make is that the poster's proposals were moving away from normalization, not towards it, and that could be a limiting factor down the road.

I also asked them to check out the information on normalization for table design.