Query Problem

[Access denied]

Ok, I am a new access user so I am hoping that someone can help me with this problem.

I have been extracting sales information out of my accounting software and making sales reports using pivot tables. The input spreadsheets have grown way to big (like over half a million lines), so I am switching over to access.

I can import the data fine but when I go to make a query the sales information is multiplying itself. For example my Inputs look something like this (there are way more columns but for examples sake)

Customer_# Customer_Name February_Sales March_Sales
ABC123 ABC Company 1000.00 700.00
ABC123 ABC Company 6000.00 9000.00
ABC123 ABC Company 300.00 100.00

The raw data comes out from the accounting software by invoice number by item sku so there are many records for one customer

So I have imported my tables and have a master customer table with customer# and customer Name (I am using the customer# here as the primary key). The other two tables for the sales have an autonumber primary key.

When I create the query I get something like this:

Customer Name February Sales March Sales
ABC Company 21,900.00 39,200.00

In this case the February sales were totaled and muliplied by 3 and March by 4

I don't know why it is doing this, I hope you guys can help me out.

Thanks for reading!!

 

[pbaldy]

In my experience, that type of thing is usually a problem with joins and related tables. You have tables for each month?

 

[Access denied]

Yes there is a table for each new month

 

[Rabbie]

Yes there is a table for each new month

Why?? You should be able to have just one table and then use a query to extracr the dat for a particular month or other period.

 

[Access denied]

Correct me if I am wrong (I probably am) but there are two problems with that.

1) I am at almost 500,000 lines (records) of data and growing, won't that be too much?

2) I want to see the information month by month side by side so something like this

customer name .February Sales .March Sales .and so on

If I have it in one database table then I won't be able to get the months side by side correct?

 

[Rabbie]

1. There is no specific limit on the number of records in a table. Basically if there is room for tables for each month there is room for 1 table holding all the data.

2. A Crosstab query should sort this out for you.

Meanwhile back to your original question about the multiplying of data. How are you calculating the totals for each month? Are your tables joined correctly?

 

[Access denied]

I am using a sum in the query. This may be solved if I put it into one table, I am going to try that and then see what happens. I will let you know.

How do you do a crosstab query?

 

[Access denied]

Ok I did a crosstab query and it worked! You guys are awesome.

I did run into another problem when I tried to import the whole spreadsheet from excel into access. What does this mean?