Query with multiple years and products

[MBeez]

I'm also somewhat new to Access. I have a db that contains all of the
products that we sold in 2003, 2004 and part of 2005 for each client. I'm
trying to put together a spreadsheet that shows the total parts for each year
for each client.

What I'm seeing is that if for example in 2003 the client buys one product
and then in 2004 does not buy the same product again, Access does not
automatically enter a zero value. Is there any way to set it up so it will
put in a vero if the client doesn't buy the same products each year?

 

[Jeff Boyce]

With no idea of your data structure, it will be difficult to offer specific
suggestions.

A general suggestion is to NOT record a zero ... instead, use a query to
determine how many for each period. This sounds like a cross-tab query to
me...

Regards

Jeff Boyce
<Office/Access MVP>

 

[MBeez]

My data is set up as:

Company Product 2003
ABC Co. U-UUGH $3350
etc.

I have seperate spreadsheets for each year set up the same way (2003, 2004,
2005). Does that help any?

 

[Jeff Boyce]

So, you are saying that you have a different table for each year? If so,
your database will make MUCH better use of Access' strengths if you spend
some time normalizing the data structure. For example, a table with
[Company], [Product], [AnnualAmount], and [ProductYear] would let you store
all data in one table.

A second potential issue ... if your data is a summarization of individual
records, where's the underlying detail? You might not even need a summary
table, since you could do this with a query against your detail records.

Regards

Jeff Boyce
<Office/Access MVP>