Relating products to its price in query

[Armesh]

I've done almost the whole part but stuck at 1 last query question. It says:

Display Customer Identification number, customer first and last names, and gift set (excluding flower stalks) where customers are paying more than $30 but less than $120.

I'm having problem with the underlined part. I dunno how I can equate the gifts to their respective prices, then sum it in a new field, so I can set the criteria of the new field to Between 30 and 120. ... I am blank, no idea, can't even google cause I dunno what to google also.

 

[vbaInet]

We don't normally do students' assignments on this forum but I will give you some hints/tips.

If you break down the statement into three parts you get:

1. Display Customer Identification number, customer first and last names and gift set
2. gift set (excluding flower stalks)
3. customers are paying more than $30 but less than $120

The first part should be easily identifiable as a SExxxT query.
Number 2 and 3 are criteria but number 2 sounds to me like you have a Gifts table and what the teacher wants you to do is to only include those gifts that are not flower stalks. Whilst number 3 is just a WHERE clause where the price is between those two values from your Pricing table (if you have one). Hint: search for the BETWEEN keyword.

Good luck with your assignment.

 

[Armesh]

Ok, first just Create>Query Design. Then select the fields that I wan from the tables I wan. Customer Identification number, customer first and last names taken from customer membership info table, Gift set taken from customer order record table. Then set the criteria for gift set to Not Like "*Stalk*". Now I got 1 and 2 done. So now the 3. I knoe the criteria will be Between 30 and 120. But how do I convert the gifts to the price? Like how do I link the gift to the price so I would count the total price for the gifts the customer order...?

 

[vbaInet]

If you noticed within my post I mentioned something to do with a Pricing table. You're supposed to have a table with all the prices.

 

[Armesh]

I do have a pricing table... but I dunno what to do with it.. If it put it in the query, it doesn't assign to the respective items. It will just display some prices.. but doesn't add up prices of each individual product ordered by the specific customer. Try taking a look at my database, I have it all. Just dunno how to link the price part.

 

[vbaInet]

The pricing table should relate to your items somehow via some ID.

 

[Armesh]

Ya sumting like that.... If you look at my database, the customer orders are multiple choice list. So 1 customer may order Flower basket, Chocolate and Candies. So now I want to assign the price for the items in the query, The prices are already given in a seperate table.. but its just not adding up...

 

[Armesh]

The gift set LIST in the customer order table is set as raw source to the gift set FIELD of the Gifts table.

 

[vbaInet]

There's no db to make reference to. If you want to attach anything see the forum's FAQs for instructions how.

You're using Access 2007 or 2010 right? Multi-valued fields are not very intuitive to use and they are not portable either. You will need to create a junction table to hold your list of gift items per customer.

 

[Armesh]

----------

 

[Armesh]

Its just that 1 last part of the last query question.... submission is on 4 Sep.... Been stuck at this question for days..... really need someone to show me how to do it, had to ask here as last resort. Please help me man. Its just 1 small part... Not like I'm posting my whole big assignment (95% done) here and asking people to do it for me. Its still a part of learning even if you show me.

 

[vbaInet]

Even before I saw your db I already knew it was a multi-valued field and I had already explained that you need to get rid of the field and create a junction table where each customers gift set items are stored. This will be the table you will use to link between the order and gifts tables.

 

[Armesh]

Ok, I kindda get what you said. So

1st: Should I create a separate table where the ONLY gift set orders for each individual customers are stored? The problem is since each individual customer order multiple gift sets, the fields in the junction table would be multi-valued also. So its ok to have multi-valued gift set field in the junction table?

And would the Gift Set field in the customer order record table have 1 to 1 relationship (Enforce referential integrity + Cascade Update) with the Gift Set field in the Junction table? Or should I use the "Lookup Column" option?

 

[Armesh]

Or

2nd: Should I make a junction table containing Gift Set and Price field. Then use the "Lookup Column" option for both the fields? (Gift Set lookup from Gift Set field in customer order table Price lookup from Price field in Gifts table)

 

[vbaInet]

Just avoid multi-valued fields altogether. What a multi-value field does is that same as you would do with a junction table but it's not the best of datatypes to work with. Your teacher should know about its limitations and if he asks you tell him that:

1. Multi-valued fields are not portable, i.e. if you were upscaling to SQL Server (for example) it isn't supported. It's a MS Access thing ONLY.
2. They are difficult to work with in terms how you link to fields and how to extract data from them. You've seen this already.
3. They hide real relationships in the sense that they are masking how many-to-many joins are created.

Maybe you will get some points by saying that

So the fields in your junction will not be multi-valued. You are forming a one-to-many relationship between the Customer and his/her gift sets and a one-to-many between the gifts and the customer's gift. This is your linkage.

Have a look at the following link and pay close attention to the section titled "many-to-many relationships":

http://r937.com/relational.html

 

[Armesh]

My lecturer is useless, she comes to class late, doesn't teach, she's a slide reader, poor experience in Access, even I'm better. I did the whole assignment using google on my self.

Ok so now I understand why things weren't working, it was the multi-valued Gift Set field in the Customer Order table. So now... I have to keep the multi valued Gift Set field in the Customer Order table because the question requires me to. I cannot simply delete it off. And how actually the junction table be formed....? I'm currently reading what you ask me to read.

I dun get the part how the junction table can eliminate multi valued fields.

 

[vbaInet]

If you're currently reading it, keep reading. And I would advise that you read the entire thing, not just that part. Everything will become clear.

 

[Armesh]

INstead of using a table, can I use a query? Because query can automatically take and update values from other field compared to a new table. Is this ok? Or should I use a table and there is a way to keep the fields matched with a table?

 

[vbaInet]

I've given you the tools you need to accomplish your task, so go fish. If I felt a query would cut it I would have mentioned it. Re-think the design.