Found anything Missing?

[prabha_friend]

Please report. Thanks

 

[Pat Hartman]

1. Giving all the autonumbers the same name is not helpful. It makes it extremely difficult to see relationships without opening the relationship window.
2. The name of the PK and FK should match or if you prefer, use "_FK" as the suffix for the foreign keys
3. your names are inconsistent. Subscription vs Subscription_ID
4. the tables Line and Item have identical attributes?
5. Moment seems to be in multiple places and I don't know what a "moment" even is.
6. The relationship to Delivery can't be right. It would allow for Delivery to be related to a different Customer than the Item is. Delivery probably should be a child of Subscription and Request and that means there is a bigger logic problem with the schema.
7. I have no clue what this schema is for. You have given us no context.

 

[Pat Hartman]

@prabha_friend The experts don't post here to gather likes. While acknowledging a post may be polite, the like doesn't convey any information to anyone who reads this post later and the forum is all about information sharing and learning.

 

[prabha_friend]

1. Giving all the autonumbers the same name is not helpful. It makes it extremely difficult to see relationships without opening the relationship window.
2. The name of the PK and FK should match or if you prefer, use "_FK" as the suffix for the foreign keys
3. your names are inconsistent. Subscription vs Subscription_ID
4. the tables Line and Item have identical attributes?
5. Moment seems to be in multiple places and I don't know what a "moment" even is.
6. The relationship to Delivery can't be right. It would allow for Delivery to be related to a different Customer than the Item is. Delivery probably should be a child of Subscription and Request and that means there is a bigger logic problem with the schema.
7. I have no clue what this schema is for. You have given us no context.

one of my schoolmate's relations is running a small Dairy. He asked me to develop an inventory cum software for him. I choose MS Access as the Base and escalate to further technologies if needed. This is my ever Database I am developing for a Dairy. Upon thinking. I have derived the following entities:
Customer (Living in a Particular Road Line of a City)
Item (5 items in general: Milk, Curd, Butter, Ghee and Khoa)
Subscription (Can be switched, Paused and Closed)
Adhoc Request (Can be done on the above 5 listed items)
and Finally Delivering the Same.

 

[prabha_friend]

@prabha_friend The experts don't post here to gather likes. While acknowledging a post may be polite, the like doesn't convey any information to anyone who reads this post later and the forum is all about information sharing and learning.

Why this much fast

 

[prabha_friend]

Main Subscription Form:

Delivery Drafts:

Why I call it Drafts:
Because its the form which loads all the deliverables from the database and make it acknowledged after Delivery.

 

[prabha_friend]

Moment is nothing but a combination of Date and Time.

 

[prabha_friend]

SELECT Customer.ID, [DD] AS Delivery_Date, Subscription.Session, Customer.NameVal, Item.ID, Item.NameVal, Subscription.Rate, Nz((SELECT LAST([Volume_Switch].[New_Volume]) FROM [Volume_Switch] WHERE [Subscription].ID=[Volume_Switch].[Subscription_ID] ),[Subscription].[Volume]) AS Delivery_Volume
FROM Item INNER JOIN (Customer INNER JOIN Subscription ON Customer.ID = Subscription.Customer) ON Item.ID = Subscription.Item
WHERE (((Subscription.Moment)<=[DD]) AND ((Subscription.ID) Not In (SELECT [Closure].[Subscription] from [Closure]) And (Subscription.ID) Not In (SELECT PAUSE.[Subscription] from pause where [Pause].[Subscription]=[Subscription].[ID] and ([DD] between [Pause].[From] and [Pause].[To]))))
ORDER BY Customer.ID, Subscription.Session DESC;

 

[plog]

Moment is nothing but a combination of Date and Time.

That explains the datatype but not what the data represents. What does that date/time represent in the context of each table that field appears in?Why is it necessary?

Lasty, and most importantly, you're just throwing things at us. Posted a schema, thoughtful notes were giving, you briefly and very tangentially addressed them and then threw a form at us, without waiting for a response you then threw a query at us without reason or context. I can't tell if you are showing off what you have done or genuinely want feedback and assistance.

 

[prabha_friend]

That's all the Database Has.
Just thought having a column to denote the birth of each record. For example:
The Shop owner may say: "Yeah we have covered that area from this year onwards"
Customer Anniversary offer
Now. It's has become an habit of mine to add a Moment column for every Key entities...

 

[plog]

I suggest you go to Pat Hartman's original numerated response (Post #2) and address what she brought up number by number. Either tell her that you agree with each and every point, or explain why you disagree. Go point by point to each of her numbers in that post. Then we can move on to forms and queries.

 

[prabha_friend]

1. Giving all the autonumbers the same name is not helpful. It makes it extremely difficult to see relationships without opening the relationship window.
2. The name of the PK and FK should match or if you prefer, use "_FK" as the suffix for the foreign keys
3. your names are inconsistent. Subscription vs Subscription_ID
4. the tables Line and Item have identical attributes?
5. Moment seems to be in multiple places and I don't know what a "moment" even is.
6. The relationship to Delivery can't be right. It would allow for Delivery to be related to a different Customer than the Item is. Delivery probably should be a child of Subscription and Request and that means there is a bigger logic problem with the schema.
7. I have no clue what this schema is for. You have given us no context.

1. Giving all the autonumbers the same name is not helpful. It makes it extremely difficult to see relationships without opening the relationship window.
I want to create the Entities as we see in the real word. For example I also feel that the Table name is sufficient to know it is a different entity's ID. But the problem is: we have to address the table name also in the queries. For example: Item.ID and Subscription.ID like that. I am fine with that.
2. The name of the PK and FK should match or if you prefer, use "_FK" as the suffix for the foreign keys
Yes. Will be useful while upsizing to SQL Server.
3. your names are inconsistent. Subscription vs Subscription_ID
Yup. My Mistake. Will be rectified.
4. the tables Line and Item have identical attributes?
But those two are completely different entities.
5. Moment seems to be in multiple places and I don't know what a "moment" even is.
Moment is nothing but a combination of Date and Time.
6. The relationship to Delivery can't be right. It would allow for Delivery to be related to a different Customer than the Item is. Delivery probably should be a child of Subscription and Request and that means there is a bigger logic problem with the schema.
I feel it would be better to a have solid details carved in the final Bill (Delivery). Since I am keeping the Customer and Item ID in the delivery itself I excluded that. Yes, I also feel its not perfect.
7. I have no clue what this schema is for. You have given us no context.
one of my schoolmate's relations is running a small Dairy. He asked me to develop an inventory cum software for him. I choose MS Access as the Base and escalate to further technologies if needed. This is my ever Database I am developing for a Dairy. Upon thinking. I have derived the following entities:
Customer (Living in a Particular Road Line of a City)
Item (5 items in general: Milk, Curd, Butter, Ghee and Khoa)
Subscription (Can be switched, Paused and Closed)
Adhoc Request (Can be done on the above 5 listed items)
and Finally Delivering the Same.

 

[prabha_friend]

@prabha_friend The experts don't post here to gather likes. While acknowledging a post may be polite, the like doesn't convey any information to anyone who reads this post later and the forum is all about information sharing and learning.

Yes. I agree and will stop putting likes

 

[prabha_friend]

That explains the datatype but not what the data represents. What does that date/time represent in the context of each table that field appears in?Why is it necessary?

Lasty, and most importantly, you're just throwing things at us. Posted a schema, thoughtful notes were giving, you briefly and very tangentially addressed them and then threw a form at us, without waiting for a response you then threw a query at us without reason or context. I can't tell if you are showing off what you have done or genuinely want feedback and assistance.

Please understand that I have attended the schools only upto 10th standard in India. Everything I read, build and Share is being done out of sheer curiosity and Passion that I share on my works...

 

[prabha_friend]

I suggest you go to Pat Hartman's original numerated response (Post #2) and address what she brought up number by number. Either tell her that you agree with each and every point, or explain why you disagree. Go point by point to each of her numbers in that post. Then we can move on to forms and queries.

Done Plog. Waiting for your thoughts of my forms and queries...

 

[Mark_]

Backing up two steps, you will need to be very CLEAR on what you intend to have this database DO. "Dairy" doesn't change that this looks like a scheduled delivery of goods system. If this is true, what outputs do you need?

 

[The_Doc_Man]

As often as you have posted here, and as long as you have been on this site, you should know that just posting a relationship diagram does not tell us ANYTHING about the goals, rules, and methods of the business it represents. A verbal overview of the intent of the DB would have been helpful. Prabhakaran, you KNOW we will answer you if and when we can - but there is no need to make it hard for us to know what you intended.

 

[prabha_friend]

Backing up two steps, you will need to be very CLEAR on what you intend to have this database DO. "Dairy" doesn't change that this looks like a scheduled delivery of goods system. If this is true, what outputs do you need?

There are two places the user touches my form. One to know what all the Deliveries are scheduled for today and todays' requests list. But finally, he has to "Mark it Delivered" what all are items actually delivered today. The same data. First time he "reads" second time he "writes".