How to generate a simple daily report

[NewFruit]

Hi experts,
Can you kindly help me in developing the following report.

We have an Access2007 table name “TableFruit” with two columns, field names: “FRUIT” and “QUANTITY”. The table has few records, for example:

FRUIT QUANTITY
Record 1: Apples 10
Record 2: Grapes 5


We would like to print a report in the following format:

Type of Fruit Quantity Ordered in kgs
Apples 10
Bananas 0
Grapes 5
Mangoes 0
Strawberrys 0


The next day our table would look like, for example:

FRUIT QUANTITY
Record 1: Bananas 8
Record 2: Grapes 3
Record 3: Strawberrys 9


Therefore, when we print the report, it should print as follows:

Type of Fruit Quantity Ordered in kgs
Apples 0
Bananas 8
Grapes 3
Mangoes 0
Strawberrys 9


We receive the table “TableFruit” from our vendor every night and prior to supplying them the fruit the next morning we print the above report.


I have created the first column of the report but am stuck creating and displaying the second column of the report where it should display quantities from the table for the appropriate fruits. I am assuming it would need five text boxes and vba code. I have limited vba coding (starter) experience.


Any help provided is appreciated. Thank you.

 

[NewFruit]

Thanks Bob for your response.

I could have used the report wizard if the "TableFruit" would have had the five fruits listed as five records, but the problem is that the vendor only sends the table with say two fruits listed in two records (as in my example 1) or three fruits listed in three records (as in example 2).

But at our end we need to print a report where the first column with fruit names is already sorted, which I created using text boxes in the Design view of report.

So what needs to be done is, for example, referring to my example #2, from the table "TableFruit" the program should "pick" the quantity of Bananas which is "8" and display it on the report adjacent to the box "Banana" as "8", similarly the report should display "9" next to the box named "Strawberrys". Note in this example #2, "TableFruit" does not have any record for apples, hence on the report the quantity to be displayed next to Apples should be "0".

Hope the above explains it. Once again thanks for your time and help.

 

[NewFruit]

Bob, I tried the query method u suggested but the report only prints only the three or two records (as the case maybe) from the "TableFruit".

Let me try to explain it in another way. Think of the "report" as a pre-printed stationary with the following:

Type of Fruit Quantity Ordered in kgs
Apples
Bananas
Grapes
Mangoes
Strawberrys

Now when the report is run (and printed) the second column "Quantity" values should be filled from the "TableFruit" table reflecting the quantity for the appropriate fruits.

Note that the table "TableFruit" may not have all five fruits listed but only two or three distinct fruits which the vendor wants, therefore examples such as: apples and grapes; or bananas and grapes and strawberrys; or say mangoes and strawberrys.

Therefore the above report would display the actual quantities of the fruits that the vendor wants (pulled from the table), and also display the non-ordered fruits as zeroes.

Hope this explains it better. Once again thank you for your time.

 

[NewFruit]

Bob:

You are awesome. Thank you so much, this is exactly what we want. Cheers from NewFruit & my fellow staff, we are thrilled.

Now I need to figure out how to mark this message as "SOLVED".