large DB issue

[maximax]

Hi,



I am fairly new to Access and I have a first issue:

I have a database of information to overpass the maximum number of rows allowed in Excel, so the data are stored on 2 spreadsheets. I need to run a pivot table on this data but I can't as it's on 2 spreadsheets. I heard that Access do not have such limitation and it could be my solution.

So I open Access, open my Excel file in Access and choose the first spreadsheet and import the data. Everything is okay until now. Then I would like to add the second spreadsheet below, so on the "External data" tab, "import & Link" field I choose Excel, I select the file, choose the second option (append) and click OK, the I select the second spreadsheet (that contains the same headers) and click next, click next again and then finish.
Then I get a message telling me: " Operation must use an updatable query", click OK, then the error message saying: "an error occurred trying to import the file (...). The file was not imported".

So here comes the questions:
-How can I do to get my complete database into one single Access table?
-How can I run a pivot table (similar as Excel) on this table?


Many thanks in advance to the one(s) willing to help.

Regards,

Maxime

[alansidman]

Import the second worksheet into a second table. Create a Union Query to join the two tables into one. Save the Query. Open a new query using the Union Query as your record source. Change the second query to a make table query. Run it. Delete the first two tables after making sure that you have all the records in the new table.

Create a select query using the new table. Once it is designed, change the query to a Pivot table view. Make your pivot table same as in Excel.

Alan

[orange]

Further to Alan's comments/advice, I recommend you do a little researc on database design and normalization.

How can I do to get my complete database into one single Access table?

is a very poor starting position and shows an issue with your understanding the concepts of RDBMS.

You may find these videos useful. There are other videos, these are just a sample.

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

You can go back and watch these as often as you want.

[maximax]

Thank yo very much to both of you. Very helpful.

I succeed in creating a union query and I have now my 1.2 Million rows into one query.

Now, my (new) issue is:
when I choose the pivot table view and drop my fields in the rows, columns, filters and data. I get no info calculated and then I got an error message saying "database already exists".
Any idea why this is happening?

Thx again.

Max

[alansidman]

No idea, but to be on the safe side, run a compact and repair and see if that makes a difference.