Allow more than order in an ORDER form.

[kiko]

Oh yes yes. Thank you for helping. I know you're trying to provide enough info and examples, I understand. It's just that I was confused about the "line" as I saw people use it a lot in their database, so I was wondering is it a must to include that. But really, thank you so much for the help! Really appreciate it. My mother tongue is Malay as I'm from Malaysia

[orange]

Is your problem solved? Or do you have more questions?

How good is google??

Adakah masalah anda diselesaikan? Atau adakah anda mempunyai lebih banyak soalan?

[kiko]

My problem is solved. I managed to understand a lot more about access with your help! Thank you so much.

And yes, I have some more questions actually.

1. How do I delete records, say for books that haven't been sold for a month or more?
2. I don't manage to find out how to make automatic calculation in my database for quantities bought and sold. I tried referring in books but it's confusing for me :|

Haha, google is actually quite good in direct translating. Not bad.

[orange]

1. Create a select query to get the records involved.

I didn't have a Salesdate, so Ill use Orderdate.

You know which books were ordered in which Order, so find the books that have been Ordered in the last X months.

The Books that are not in that set have not been Ordered(sold) in that time period. (I think)

This is an academic exercise, right? You wouldn't normally delete such records. You might want to send some back to the suppliers, or do some marketing to sell them.....

[kiko]

I will try that. How about second question?

[orange]

How about posting a copy of your database? I'll look at your material that has all the tables.

Here's a general overview off the top of my head -there are examples and many posts involving this sort of thing.

A lot of times people will keep transactions in a separate table. Transactions would have info on the Book,Price,Quantity and Date amongst others. Transactions out(sales) are negative, Books coming in from Suppliers(acquisitions) would be positive. At any time would be
QuantityonHand = LatestInventoryCount + AcquisitionsQty since LatestInventory - SalesQty since LatestInventory.

[Parsonshere]

BooksKiko should not be defined in your relationships at all. That is only used as a rowsource for a combobox or something to make a selection. These items will be discontinued also, so you want to be able to remove them from our inventory reference, while you will keep the actual record in orderlineItemKiko. Think of BookKiko as a completely separate inventory database, and the customer order as the sales database. Also, even though what you have works, normally you would make ordlineItemID and OrderRef both Primary Keys. This way, you could actually number the line items with the field ordlineItemID in the invoice. As for deducting, you could add a Boolean, and make sure it is not editable in your form, to mark the record as having been deducted from inventory. Use a command button to programmatically edit the Boolean and run the query to deduct the inventory at the same time. You may want the Boolean in the item table, in case of a partial order fill. I am just thinking, probably a better way to do that.

[orange]

Interesting comments.
However, I mocked up this data base to help Kiko with some basic concepts. I purposely did not make my database match his data model. He will have to adjust and hopefully learn from those adjustments to his real database. It's my view this is a school assignment, and we here do not do school assignments. We offer assistance and some examples, but the student/poster will learn something from examples and will do the work themselves.

BooksKiko should not be defined in your relationships at all. That is only used as a rowsource for a combobox or something to make a selection.

When you make a statement like this to someone who is learning, you should review the code and verify your facts. Misstatements can confuse the reader.
I purposely did not use any forms in this mock up, so there is no combo box.

BooksKiko is the only place that a Book's genre is identified.
So to answer his query #4 from his post#6

4. Output list of book genres in order of popularity.

you need the table BooksKiko.

These items will be discontinued also,

You appear to know more about Kiko's set up than Kiko has mentioned, or are guessing at some things. Or you are in his class and know more details of the assignment???

If you look closely at Kiko's data model you will see that not all tables are linked. He cleverly positioned his Return_Outwards table with a list of tables. So his model was not complete. He will have to work out the linkages.

I don't believe his assignment was "inventory control" based --FIFO, LIFO, Reorder points, Partial Order, Partial deliveries, Alternate Suppliers, Item Substitutions.... etc. but may be there was more to the assignment.

Bottom line is I created a simple database based on a generic Customer -Order-OrderLineItems- Items model. I read what Kiko said were his issues and tried to simulate with simple constructs to help solve those issues. He seems keen to learn, but we have not seen his work.

I appreciate the comments you have made and I hope you enjoy the feedback. And please recognize, as I do, that there are many ways to set up tables and relationships and solve problems. If there were only one answer, wouldn't we all use that solution.

[kiko]

I don't know why am I having problem to upload my database here. I've been trying to do that few times now but it doesn't seem like it's coming out.

Parsonshere, thank you so much for your help. But I somehow don't really understand that much about access so I don't use combobox function.

My lecturer only wants us to make a simple database to store information on customers, suppliers, stocks. He doesn't really provide us that much information about what should be in the database or whatever as he just simply let us do however we want it. Which is actually more harder as he doesn't really help us a lot so we would have to find out through internet or forums like this.

[kiko]

Orange. How is it possible as for my query to find customer who has spent the most, i want to show the first name and last name as well as the customer ID?

[orange]

Did you look at the example query? KikoCustomerWithMostSpending

In the query designer, add the KikoCust table, join it to the original query on CustId, then add the FName and LName fields to output.

SELECT TOP 1 [%$##@_Alias].CustKiko.CustId, [%$##@_Alias].Spend, CustKiko.CustFName, CustKiko.CustLName
FROM (SELECT CustKiko.CustId, Sum([AgreedPrice]*[Quantity]) AS Spend FROM (CustKiko INNER JOIN CustInfoKiko ON CustKiko.CustId = CustInfoKiko.Custid) INNER JOIN OrderLineItemKiko ON CustInfoKiko.CustOrderId = OrderLineItemKiko.OrderRef GROUP BY CustKiko.CustId ORDER BY Sum([AgreedPrice]*[Quantity]) DESC)  AS [%$##@_Alias] INNER JOIN CustKiko ON [%$##@_Alias].CustId = CustKiko.CustId;

This alias [%$##@_Alias] is created by Access with the original query.

Remember, there are many ways to do sql queries.

Results from my examples

CustId	Spend	CustFName	CustLName
1	$1,271.30	Jim	Smith

[kiko]

Because I already try adding the FirstName and LastName but an error came up. Will try this one now.

[kiko]

I already tried. It works but it gives me different results. Why is that?

[orange]

Post your database as a zip file

[kiko]

WonderWare.zip

attached is my database