Does "Group By" disables adding a new record?

[Micky]

Hey, I'm building a Toys Store for my Studying (industrial Engineering). Now, each customer entering the store, can buy more than one item, so I have built the tables accordingly, and a connecting table between items and sales, so that each customer in one buy (Serial Number), can buy a few products.
Next step, I designed a query, with a Sum and "Group By" (the Serial Number 1,2,3 etc...), so that I will be able to see everything in one form, including sub form for the different items.
The thing is, that no one told me (thought it seems somehow logical), that the user won't be able to add a new record (means: sell to customer). And now, after spending hours and days, I'm confused. Since I read in the web, that once I'm using "Group By", the form and queries will become automatically "read only" mode.
Do you have any suggestions how do I overcome this problem?


Any help would be very much appreciated. I'm sorry for my bad English, but I hope it's clear.

Thanks!!

[pbaldy]

It is true that the Group By clause makes the query read only. I'm not clear on why you would need it as the source of the form. Typically the form would be based on an invoice table and the subform on a related invoice details table. Can you clarify your table structure and why you need the group by?

[orange]

I suggest you review database design articles, especially those relating to Normalization and entity relationship diagramming. The concepts involved will help you with your project.

Getting the table structures correct is key. Identifying some of the facts/business rules that the database must support is also a good way to ensure good table and relationship design.

Things like this should be considered as you start your project:
The store has many Customers.
A Purchase can involve 1 or more items.
A Sale may involve 1 or more items. (You may want to deal with Sales or Purchase and not both)
A Customer can make 1 or more purchases.
(Perhaps a Customer doesn't have to make a Purchase? so 0 or more Purchases is allowed?)

You may wish to make some sample data (even on paper) and make sure your tables and relationships support the facts and the test data.

Are you familiar with Normalization? How about Entity relationship diagramming?
Here is a list of free video tutorials that may help.

http://www.youtube.com/watch?v=IiVq8M5DBkk Logical data modeling

http://www.youtube.com/watch?v=BGMwuOtRfqU Candidate key

http://www.youtube.com/watch?v=ZiB-BKCzS_I Normalization

http://www.youtube.com/watch?v=pJ47btpjAhA Normalization example

http://www.youtube.com/watch?v=q3Wg2fZENK0 1st Normal form

http://www.youtube.com/watch?v=vji0pfliHZI 2nd Normal form

http://www.youtube.com/watch?v=HH-QR7t-kMo 3rd Normal form

http://www.youtube.com/watch?v=q1GaaGHHAqM E_R Diagramming

http://www.youtube.com/watch?v=lXAGQ8vmhCY ERD Part 2

The first few topics at this site are well worth the read.

http://www.rogersaccesslibrary.com/forum/topic238.html

Good luck with your project and your studies.

[Micky]

Thanks, I will use these tutorials. Anyway, I'm herewith attaching the relationships diagram between the tables.
I studied Normalization and the ERD I guess that it wasn't enough. But anyway, I still have to think how to do this and I'm running out of time with this project.

I suggest you review database design articles, especially those relating to Normalization and entity relationship diagramming. The concepts involved will help you with your project.

Getting the table structures correct is key. Identifying some of the facts/business rules that the database must support is also a good way to ensure good table and relationship design.

Things like this should be considered as you start your project:
The store has many Customers.
A Purchase can involve 1 or more items.
A Sale may involve 1 or more items. (You may want to deal with Sales or Purchase and not both)
A Customer can make 1 or more purchases.
(Perhaps a Customer doesn't have to make a Purchase? so 0 or more Purchases is allowed?)

You may wish to make some sample data (even on paper) and make sure your tables and relationships support the facts and the test data.

Are you familiar with Normalization? How about Entity relationship diagramming?
Here is a list of free video tutorials that may help.




http://www.youtube.com/watch?v=IiVq8M5DBkk Logical data modeling

http://www.youtube.com/watch?v=BGMwuOtRfqU Candidate key

http://www.youtube.com/watch?v=ZiB-BKCzS_I Normalization

http://www.youtube.com/watch?v=pJ47btpjAhA Normalization example

http://www.youtube.com/watch?v=q3Wg2fZENK0 1st Normal form

http://www.youtube.com/watch?v=vji0pfliHZI 2nd Normal form

http://www.youtube.com/watch?v=HH-QR7t-kMo 3rd Normal form

http://www.youtube.com/watch?v=q1GaaGHHAqM E_R Diagramming

http://www.youtube.com/watch?v=lXAGQ8vmhCY ERD Part 2

The first few topics at this site are well worth the read.

http://www.rogersaccesslibrary.com/forum/topic238.html

Good luck with your project and your studies.

[Micky]

I just wanted o update that I have solved the problem. I learned how to do this through the Youtube, using the form wizard.
Thanks both for your help!!