Primary Key field(or fields) have to be unique. If you have one field as the primary key, then you can only have one of each value in that field. If you have more than one field as primary key, then the combo of the two fields has to be unique.
A simple example would be a calendar. If you had fields for Month and Day, with Month as the ONLY primary key, you could enter:
Month(PK) Day
January 1
February 15
Only one day for each month can be entered, because month has to be unique on the table since it's the primary key. If you tried to enter January 2, it would be rejected because January is already on the table.
If you had month AND day as the primary key, then you can enter all days for each month one time, because the combo of month and day is unique.
Month(PK) Day(PK)
January 1
January 2
February 15
February 16
etc.
Hopefully that helps.
--- In MS_Access_Professionals@yahoogroups.com, cc0623 <cc0623@...> wrote:
>
> No, I don't. Please explain...
>
>
> Tiffany
>
>
>
> ________________________________
> From: John Viescas <john@...>
> To: ms_access_professionals@yahoogroups.com
> Sent: Wednesday, November 23, 2011 10:07 AM
> Subject: Re: RE: [MS_AccessPros] Gathering data from multiple spreadsheets
>
>
> Â
> Tammy-
>
> Do you understand why you had to define both fields as the primary key?
>
> John
>
> Sent from Samsung Mobile
>
> -------- Original message --------
> Subject: Re: RE: [MS_AccessPros] Gathering data from multiple spreadsheets
> From: cc0623 <cc0623@...>
> To: "MS_Access_Professionals@yahoogroups.com" <MS_Access_Professionals@yahoogroups.com>
> CC:
>
> Hi John,
> Â
> Success! Learning this gave me an idea on how to append all my sold records from a different table. Thanks for the lesson. Happy Thanksgiving!!!
> Â
> Â
> Tiffany
>
> ________________________________
> From: John Viescas <john@...>
> To: MS_Access_Professionals@yahoogroups.com
> Sent: Tuesday, November 22, 2011 11:37 PM
> Subject: RE: RE: [MS_AccessPros] Gathering data from multiple spreadsheets
>
> Â
> Tiffany-
>
> 1) Open the table in Design view.
>
> 2) Click in the Primary Key field and de-select the Primary Key button on the
> toolbar (2003 and earlier) or on the Design tab (2007 and later).
>
> 3) Hold down the Shift key and click YearMonth - that should highlight both
> Unit# and YearMonth.
>
> 4) Click the Primary Key button.
>
> 5) Save
>
> You'll probably need to delete all existing rows in your table before you do
> this, then rerun your append queries after.
>
> 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/> http://www.viescas.com/
>
> (Paris, France)
>
> From: cc0623 [mailto:cc0623@...]
> Sent: Wednesday, November 23, 2011 7:15 AM
> To: john@...
> Subject: Re: RE: [MS_AccessPros] Gathering data from multiple spreadsheets
>
> You are correct. How do I set the combo key?
>
> Sent frrowm Yahoo! Mail on Android
>
> _____
>
> From: John Viescas <john@...>;
> To: <MS_Access_Professionals@yahoogroups.com>;
> Subject: RE: [MS_AccessPros] Gathering data from multiple spreadsheets
> Sent: Wed, Nov 23, 2011 5:35:19 AM
>
> Tiffany-
>
> Did you make the *combination* of Unit# and YearMonth the Primary Key? I bet you
> only set Primary Key on Unit#.
>
> 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 <javascript:return>
> [mailto:MS_Access_Professionals@yahoogroups.com <javascript:return> ] On Behalf
> Of cc0623
> Sent: Tuesday, November 22, 2011 11:14 PM
> To: MS_Access_Professionals@yahoogroups.com <javascript:return>
> Subject: Re: [MS_AccessPros] Gathering data from multiple spreadsheets
>
> John,
>
> It is pulling from the correct month but it is only pulling the new
> units/mileage, it is excluding any units that are already in the table.
>
> Tiffany
>
> ________________________________
> From: John Viescas <john@... <javascript:return> >
> To: MS_Access_Professionals@yahoogroups.com <javascript:return>
> 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 <javascript:return>
> [mailto:MS_Access_Professionals@yahoogroups.com <javascript:return> ] On Behalf
> Of cc0623
> Sent: Tuesday, November 22, 2011 10:52 PM
> To: MS_Access_Professionals@yahoogroups.com <javascript:return>
> 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@... <javascript:return> >
> To: MS_Access_Professionals@yahoogroups.com <javascript:return>
> 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 <javascript:return>
> [mailto:MS_Access_Professionals@yahoogroups.com <javascript:return> ] On Behalf
> Of cc0623
> Sent: Tuesday, November 22, 2011 9:22 PM
> To: MS_Access_Professionals@yahoogroups.com <javascript:return>
> 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@... <javascript:return> >
> To: MS_Access_Professionals@yahoogroups.com <javascript:return>
> 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 <javascript:return>
> [mailto:MS_Access_Professionals@yahoogroups.com <javascript:return> ] On Behalf
> Of cc0623
> Sent: Tuesday, November 22, 2011 6:46 PM
> To: MS_Access_Professionals@yahoogroups.com <javascript:return>
> 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@... <javascript:return> >
> To: MS_Access_Professionals@yahoogroups.com <javascript:return>
> 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 <javascript:return>
> [mailto:MS_Access_Professionals@yahoogroups.com <javascript:return> ] On Behalf
> Of cc0623
> Sent: Tuesday, November 22, 2011 6:10 PM
> To: MS_Access_Professionals@yahoogroups.com <javascript:return>
> 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@... <javascript:return> >
> To: MS_Access_Professionals@yahoogroups.com <javascript:return>
> 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 <javascript:return>
> [mailto:MS_Access_Professionals@yahoogroups.com <javascript:return> ] On Behalf
> Of cc0623
> Sent: Tuesday, November 22, 2011 5:28 PM
> To: MS_Access_Professionals@yahoogroups.com <javascript:return>
> 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@... <javascript:return> >
> To: MS_Access_Professionals@yahoogroups.com <javascript:return>
> 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 <javascript:return>
> [mailto:MS_Access_Professionals@yahoogroups.com <javascript:return> ] On Behalf
> Of cc0623
> Sent: Tuesday, November 22, 2011 5:08 PM
> To: MS_Access_Professionals@yahoogroups.com <javascript:return>
> 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]
>
> ------------------------------------
>
> Yahoo! Groups Links
>
> [Non-text portions of this message have been removed]
>
> [Non-text portions of this message have been removed]
>
> ------------------------------------
>
> Yahoo! Groups Links
>
> [Non-text portions of this message have been removed]
>
>
>
>
> [Non-text portions of this message have been removed]
>
Tidak ada komentar:
Posting Komentar