Don't know what to do next? (1 Viewer)

[doyler78]

I have a database which I have designed in Access 2000 which is a pretty simple one. We have a sandwich labelling system which uses a flat file to store data however it is a pain to update as all the sandwiches with a particular ingredient need updating everytime we have a change in an ingredient. This is a very slow process. Setting up a relatonal database would solve the problem in that we would only have to change the ingredient itself and it would update all the sandwich recipes rather than doing this individually. I hope you get the idea.

My problem is that I have set up a relational database which I think I have setup properly however I am stuck as I don't know how to get all the ingredients in a sandwich to be combined to provide and ingredient declaration on the label. This needs to be single text field for our labelling software.

The tables and their fields are as follows:

Table 1 - tblIngredientsList
fldIngredientCode (primary key)
fldSaffronCode
fldIngredientName
fldIngredients

Table 2 - tblSandwichList
fldSandwichCode (primary key)
fldSaffronRecipeCode
fldSandwichName
fldSandwichPrice

Table 3 - tblSandwichIngredients
fldSanwichCode (composite primary key)
fldIngredientCode (composite primary key)
fld%OfIngredientInSandwich

Example:

I have sandwich RSA002BT which is a Ham Sanwich on White Bread

In this sanwich there are 3 ingredients:

BRE002 - White Bread - ingredients (white bread, white bread, white bread)
BAC011 - Gammon - ingredients (gammon, gammon, gammon)
FAT003 - Margarine - ingredients (margarine, margarine, margarine)

If I do a query on this showing sandwich name, ingredient name, ingredients, percentage, price

I get this:
Ham Sandwich, White Bread,(white bread, white bread, white bread),50, 1.60
Ham Sandwich , Gammon,(gammon, gammon, gammon),45,1.60
Ham Sandwich, Margarine,(margarine, margarine, margarine),5,1.60

How do I get to:
Ham Sandwich, White Bread 50% (white bread, white bread, white bread), Gammon 45% (gammon, gammon, gammon), Margarine 5% (margarine, margarine, margarine), 1.60

The Ham Sandwich Name is one column, the ingredients in their interity is the second column and the price the 3rd column.

Its the ingredients part that really gets me. How do I get from seperate declarations to one listing.

I have no database or programming experience. I have read a little about database design over the last couple of weeks before setting up the database but that's the height of it.

All help gratefully appreciated.

 

[gemma-the-husky]

I'm not sure why your ingredients are repeating three times (eg whitebread, white bread, whitebread)

But:

Your query shows all the data you need, formatted slightly incorrectly.

Assuming your query has only the one sandwich in it, you can open it as a recordset, and iterate every row, gradually formatting your final text. I assume your query is sorted in descending order of percentage content. Format that column in the query as percentage, and no of decimals needed.

Then use the following code

function sandwichstring() as string
dim rst as recordset
dim returnstrg as string

set rst = currentdb.openrecordset("myqueryname")

if rst.eof then
sandwichstring = vbnullstring
exit function 'in case there wer no ingredients
end if

returnstring = "Ingredient List: "
rst.movefirst
while not rst.eof
--- you are now at an item - first time through its the first item, ie the largest ingredient
--- you reference the query columns by rst!columnname
--- you can format them into your returnstring however you want
rst.movenext - sets the recordset cursor to the next item
wend -the loop finishes when you have reached the end of the file
rst.close - close the recordset

- at this point returnstring has your formatted label string
sandwichstring = returnstrg
end function

if you have more than one sandwich in the query its a bit more complicated as you would not be able to use rst.movefirst, you would have to do an rst.findfirst, and set a search parameter, and then test for the end of matches to exit the label string loop.

if the label isnt right, play around with the formatting of the various elements until you get the right answer.