Mutiple records into a single record

[lwhatford]

Hi, this may sound really silly but I can't work out what to do!



I have two tables, one called "Sales" and the other called "Food".
The Food table records a Food ID, Food Name, Price and category.
The Sales table records a sales ID, the location and a customer loyalty id.

I want to link the tables so that I can show the food items sold in a specific sale, so one sale might have been a piece of cake and a coffee.

Any help on how I can do this??

Thanks!

[Robeen]

In order to do this, you will have to have a common field in the two tables with which to link them.
For example, you might include a FoodID field in your Sales table.
Then you will be able to get the results you want.

Let me know if you need more help with this.

[lwhatford]

Ok thanks, that sorts the relationship, but when I am entering the data into the Sales table, how can I add multiple food items to each sale?

[RayMilhon]

Read up on Data Normalization. But real quickly you would have the following tables

Sales
SalesDetail
Customers
Locations
Food

Sales would contain the data for each sale. Customer, location, date
Sales Detail would contain the detail information for each sale.

Sales would have a 1 to many relationship to sales detail
Sales would have a many to 1 relationship to customers and locations
Sales Detail would have a many to one relationship to food items