ACCESS FEED: Re: [MS_AccessPros] Gathering data from multiple spreadsheets

John,

Yes. I change the month.

Tiffany

________________________________
From: John Viescas <john@viescas.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Tuesday, November 22, 2011 3:00 PM
Subject: RE: [MS_AccessPros] Gathering data from multiple spreadsheets

Tiffany-

Are you changing the year/month in the query for each table?

January:
INSERT INTO tblUnitMonthMileage ([Unit#], YearMonth, Mileage)
SELECT [Unit#], "201101" As YearMonth, Current
FROM January;

February:
INSERT INTO tblUnitMonthMileage ([Unit#], YearMonth, Mileage)
SELECT [Unit#], "201102" As YearMonth, Current
FROM February;

March:
INSERT INTO tblUnitMonthMileage ([Unit#], YearMonth, Mileage)
SELECT [Unit#], "201103" As YearMonth, Current
FROM March;

...etc.

Note that I'm changing what's in the second line each time - "201101", "201102", "201103" ...

John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of cc0623
Sent: Tuesday, November 22, 2011 10:52 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Gathering data from multiple spreadsheets

John,
Same thing happened with March. It is only appending the new units and their mileages.

Tiffany

________________________________
From: John Viescas <john@viescas.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Tuesday, November 22, 2011 2:20 PM
Subject: RE: [MS_AccessPros] Gathering data from multiple spreadsheets

Tiffany-

Are you saying your February data contains only added units and not all units? Do they not take a mileage reading every month?

John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of cc0623
Sent: Tuesday, November 22, 2011 9:22 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Gathering data from multiple spreadsheets

Hi John,

Okay I did what you asked, and the table is showing all the mileage for January's unit's and only the added units In February. I needed all the units from both months.

Tiffany

________________________________
From: John Viescas <john@viescas.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Tuesday, November 22, 2011 11:17 AM
Subject: RE: [MS_AccessPros] Gathering data from multiple spreadsheets

Tiffany-

<sigh> I'm trying to get you to normalize the data. You say you have 10 spreadsheets. Is each one for a specific month? For the current year? First, build a table that looks like this:

tblUnitMonthMileage
Unit# (whatever data type is suitable) First field in the Primary Key
YearMonth Text(6) (This will contain YYYYMM) Second field in the Primary Key
Mileage - a Number data type. Double if they record mileage in tenths; otherwise Long Integer

Let's say your first spreadsheet is name "January". Import that as a table, then run this query:

INSERT INTO tblUnitMonthMileage ([Unit#], YearMonth, Mileage)
SELECT [Unit#], "201101" As YearMonth, Current
FROM January;

That will load all the January data into your table and generate the correct year and month. Do the same for each of the other 9 months, changing the literal "201101" as appropriate. ("2011102" for February, "201103" for March, etc.)

John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of cc0623
Sent: Tuesday, November 22, 2011 6:46 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Gathering data from multiple spreadsheets

John,

None of my fields in my table have the name "month", they are named Unit, January, February, March, etc. in my new table and the records are the Units #'s.

In the 10 spreadsheets I have a column "Unit" and "Current", the months are not listed in the spreadsheets. Current is the column the mileage is stored in. Every month we added a couple of new units, so no monthly spreadsheet is the same. I need to figure out how to append each monthly spreadsheet so the new units are included w/mileage and not create duplicate records.

Tiffany

________________________________
From: John Viescas <john@viescas.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Tuesday, November 22, 2011 10:21 AM
Subject: RE: [MS_AccessPros] Gathering data from multiple spreadsheets

Tiffany-

That shouldn't be necessary. I gave you the table design below. But you should avoid a field name Month because that's also a function. Use YearMonth instead. If you import spreadsheet data that has the months across, you can load your table with a series of Append queries:

INSERT INTO tblMonthMileage ([Unit#], YearMonth, Reading)
SELECT [Unit#], "201101" As YearMonth, [Jan]
FROM ExcelTable;

Run that query 12 times - once for each month.

John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of cc0623
Sent: Tuesday, November 22, 2011 6:10 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Gathering data from multiple spreadsheets

John,
Can I send the table to you? I am not sure how to apply what you are saying.

Tiffany

________________________________
From: John Viescas <john@viescas.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Tuesday, November 22, 2011 9:57 AM
Subject: RE: [MS_AccessPros] Gathering data from multiple spreadsheets

Yes, it's wrong. Each month should be in its own *record*. Eventually you can use a Crosstab query to "pivot" the data into a spreadsheet format.

John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of cc0623
Sent: Tuesday, November 22, 2011 5:28 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Gathering data from multiple spreadsheets

Hi John,
12 columns (text) one for each month Plus one column "Unit" (the Key). Mileage is in each month. I have to use text instead of numbers because some units do not have mileage NA is listed in those fields.

Is this wrong?

Tiffany

________________________________
From: John Viescas <john@viescas.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Tuesday, November 22, 2011 9:20 AM
Subject: RE: [MS_AccessPros] Gathering data from multiple spreadsheets

Tiffany-

What is in Jan-Dec? A number? Is it a mileage reading? Your table should look
like:

Unit#
Month (as in YYYYMM)
Reading

Running columns across that should be individual rows is a relational database
design no-no. (The months are a repeating group.)

John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of cc0623
Sent: Tuesday, November 22, 2011 5:08 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Gathering data from multiple spreadsheets

I have created a table and I need to populate the data from multiple
spreadsheets.

The table has 13 columns Unit# & Jan-Dec. Over the course of the year we have
added units, so every month the spreadsheet has changed and columns have been
added or deleted as well.

I do not need all the information from the spreadsheets, just the "unit" column
and the "Current" column imported into my database.

Is there an easy way to pull all this information into one spreadsheet and keep
it constrained to the unit number?

Tiffany

------------------------------------

Yahoo! Groups Links

[Non-text portions of this message have been removed]

------------------------------------

Yahoo! Groups Links

[Non-text portions of this message have been removed]

------------------------------------

Yahoo! Groups Links

[Non-text portions of this message have been removed]

------------------------------------

Yahoo! Groups Links

[Non-text portions of this message have been removed]

------------------------------------

Yahoo! Groups Links

[Non-text portions of this message have been removed]

------------------------------------

Yahoo! Groups Links

[Non-text portions of this message have been removed]