Allow more than order in an ORDER form.

[kiko]

Hey. A newbie here.

I have a problem when I want to create an "ORDER" form, that will allow user to enter more than one order.

I have no clue how to do it.

Please help anyone!

Thanks!

[orange]

Very rare request.
An Order is typically related to a Customer.
The general scenario is
a Customer places an Order
the Order is for 1 or Many Items

Here's a tutorial that should help you with concepts.
http://www.rogersaccesslibrary.com/T...lationship.zip

[kiko]

Very rare request.
An Order is typically related to a Customer.
The general scenario is
a Customer places an Order
the Order is for 1 or Many Items

Here's a tutorial that should help you with concepts.
http://www.rogersaccesslibrary.com/T...lationship.zip

Oh, that is actually what I meant but I guess it didn't come out correctly, sorry -.-"

I already read through the whole concept of ERD etc.

I can't seems to find a way to make my order form to allow customer to order more than one items.

[kiko]

Yes, that's what I meant actually. I don't know why I type it differently -.-"

I cannot find a way to make it possible for customer order forms to allow customer to have more than one order items.

How to do it? Please help me!

[orange]

watch this tutorial
https://www.youtube.com/watch?v=YZKN_-P6wck

Here's a tutorial that deals with Customers and Orders
http://www.rogersaccesslibrary.com/T...erItDesign.zip

Getting your tables and relationships designed is key to database.
Good luck.

[kiko]

Thanks so much! The videos are really helpful!

I need help for queries. I'm supposed to write up a query in SQL view, it gets very confusing.

1. Find the town/state which is the common shipping destination.
2. List everyone who has bought two or more books by the same author.
3. Find customer who has spent the most.
4. Output list of book genres in order of popularity.

How do I do all these queries? I managed to do the others but got stuck with these.

[orange]

1 - Show a jpg of your tables and relationships.

2 - Show some of your other queries SQL.

[kiko]

that is my relationship table

as for the queries that I already did :

1. List all books that cost more than RM50

SELECT *
FROM Books
WHERE Price>50;

2. List all books that cost above average

SELECT *
FROM Books
WHERE Price> (SELECT AVG(Price) As AveragePrice
FROM Books;

3. Find the town/state which is the most common shipping destination.

SELECT State
FROM Customers_Info, Customers_Orders
WHERE Customers_Info.CustomerID=Customers_Orders.Custome rID
AND State> (SELECT Count(State) As MostCommonDestination
FROM Customers_Info;

The third query does not give me the output that I want. It somehow just give me a list of state for all the destinations.

Hope you can help me.

Thanks so much!!!

[orange]

I have looked at your query requirements and your relationships.
I mocked up some tables and data to set up some queries.
The tables and names are not exactly like yours.

Attached is an mdb with the tables and queries and relationships I used.
You should be able to adapt what I have done to your situation.

Good luck.

[kiko]

Okay thank you so much! Will look at it properly. Thanks a lot for your help!!

[kiko]

I've looked it up. It's really helpful!!!

By the way, is there a way where I can make the quantity from inventories be deducted automatically when there's a sales/purchases made??

And is it possible if I want to delete, let's say inventories that haven't been sold for a month or more?

[kiko]

By the way, I'm confused with the "LINE" attributes u added as part of the orderitem table. I've checked several examples in books, they have that as well. but i don't quite understand why actually.

[orange]

Kiko,

Tell me more about the "line" attributes -- I'm not following.

Do you understand PK and FK?
Do you understand referential integrity?
If you have established a relationship between two tables and you have selected
Cascade Delete – this option means that if you delete a record from one table, corresponding records in the other table are also deleted.

here's a tutorial that deals with keys and relationships.
http://www.rogersaccesslibrary.com/T...lationship.zip

Focus you question and I will try to answer.

Edit: I created a jpg of the relationships in the database I sent. I have opened the Relationship editor to show the selections between the Cust table and the CustInfo (which I sometimes refer to as the Orders table). You set Referential Integrity in the Relationships window. You "do joins" in the query window. Referential Integrity tells the database system to ensure the integrity of the relationships -- Do not allow activity that would create Orphan records. If you Deleted a CustId from the CustKiko table, you could be left with no corresponding CustId in the CustInfoKiko (Orders table). You would NOT want this to be allowed. That's the purpose of Enforce Referential Integrity, and Cascade Delete Related Records.

[kiko]

Oh I'm really sorry. I was so confused that my question were blurred as well. Yes I do understand about PK and FK.

The "LINE" attributes i was talking about is this : In your "ORDERLINEITEMKIKO" table, you have an autonumber data type for "OrderLineItemID" as the PK right? My tables does not have that. Is it a MUST to have that table?

And is it possible to make the quantity be automatically deducted or added everything I made sales or purchase new items? :/ I'm sorry if my question is not clear, orange. English is my second language so yeah.

[orange]

I put a pk on every table. The PK is how you uniquely identify records in the table.

Definition: The primary key of a relational table uniquely identifies each record in the table. It can either be a normal attribute that is guaranteed to be unique (such as Social Security Number in a table with no more than one record per person) or it can be generated by the DBMS (such as a globally unique identifier, or GUID, in Microsoft SQL Server). Primary keys may consist of a single attribute or multiple attributes in combination.

I know I don't have all the tables you had in your database, but I was trying to provide enough info and examples - without doing an assignment/problem for you. My intent is to show you some techniques that could be useful to you.

Queries can have arithmetic operations-- if that's what you mean.

What is your mother tongue?