want to generate a report with product names and total $ per quarter of all.

[cheweedog]

Example: Product 1 in the invoice table has many values that I would like to total by quarter. Another field with product 2 and etc. to perform the same total per quarter. When I query I only see one value, not all records in the total. What would be the best approach to get this report of products and totals per quarter? Working in MS Access 2000.

example of what I need

Product name Total $
Bill of Ship $200

[beacon]

Hi chewee,

I took a look at your database and the first problem I see is that your database isn't normalized, meaning that you don't have your tables set up in a way that promotes meaningful relationships. This makes it extremely difficult to troubleshoot because your tables/data aren't technically uniform.

For instance, all of the available products/buyers/reason codes/etc. should be in their own table, along with any descriptions/addresses/phone numbers/etc. that are related to them. These tables should also have a unique ID (typically an autonumber field), also called a primary key, in the table to identify which field belongs to which index.

Then these tables should be setup with relationships using a foreign key in other tables. An example of this would be having the Product primary key (perhaps called ProductPK) in the Products table link to the Product foreign key (perhaps called ProductFK) in the Invoice table.

The reason for doing this reduces the amount of data entry that must be done and makes querying the information a million times easier.

Here's a link to an article written by one of the forum administrators: Normalisation and Table structures

Check out the link and see about getting your database in order first. Once you take care of that, then it will be much easier to return the data you are looking for because your query will be able to hone in on just the elements that you are looking for much quicker.

If you have any questions after reading the link and trying to alter your database, don't be afraid to ask. This site is full of great people that are more than willing to help and, most importantly, teach you how to be a better database developer.

~beacon

[NeoPa]

Well, it seems I'm not required here after that answer by Beacon. Nice.

I would add one thing though. We prefer questions to be asked explicitly rather than to be partially asked and the experts expected to go and delve into your database to understand what the question is. Attaching a database should really only be done when requested to, at least the question should stand on its own and not require access to the database to be properly understood.

That said, you have some very good advice there from Beacon so all that's left for me to do at this stage is to say :
Welcome to Bytes!