Jim-
The lock file will be created if the VP opens his copy with the linked table and then opens the link. Why do you have your people trained this way?
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 Jim Wagner
Sent: Wednesday, November 30, 2011 10:47 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Linked tables question
John,
I did not explain it correctly. The databases have locking files in the folder but are not locked out from others getting in them. I was trying to figure out a way to have the VP see the data without having to link the table and creating the lock file in the folder so people do not think that someone is in the databases.
Jim Wagner
________________________________
________________________________
From: John Viescas <john@viescas.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Tuesday, November 29, 2011 1:29 PM
Subject: RE: [MS_AccessPros] Linked tables question
Jim-
Two possibilities:
1) Not everyone has full access to the folder in which the database is stored.
2) Someone crashed out of the database and left a .ldb file hanging around.
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 Jim Wagner
Sent: Tuesday, November 29, 2011 9:10 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Linked tables question
John,
The problem is that we have conditioned the employees to see if there is a locked file before entering the database, to not go in it. It looks like someone is in the database but they are not. Also if we make a copy of the database for maintenance, testing or to fix something the database looks like someone is in them and actually nobody is.
Jim Wagner
________________________________
________________________________
From: John Viescas <john@viescas.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Tuesday, November 29, 2011 1:01 PM
Subject: RE: [MS_AccessPros] Linked tables question
Jim-
You get locked out:
a) If one user opens exclusive
b) If one user opens read-only
If both users have full access to the database with the linked table and are
opening shared, there should be no problem.
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 luvmymelody
Sent: Tuesday, November 29, 2011 8:21 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Linked tables question
Hello all,
We have 2 databases that we support that have a linked table between them. On
the form in the Executive VP database there is a subform with the source for the
subform has a Select statement
SELECT [LatestPSS].[movedate], [LatestPSS].[BALANCE] FROM LatestPSS; this shows
the VP the data she needs to see on a daily or even when she needs to see it.
The data is manipulated in the other database by a user who updates the
information. The purpose for this way is so the VP does not have to open the
other database just to see 2 fields values.
The issue is that whenever anyone is in either database, the database gets
locked or shows that someone is in both databases.
Is there a way of using a non linked table solution to get the data to the VP
without linking tables?
Thank you
Jim Wagner
------------------------------------
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
Rabu, 30 November 2011
RE: [MS_AccessPros] Linked tables question
Bls: Bls: [belajar-access] VBA Barcode Code 128 [1 Attachment]
Dari: Ahmad Satiri <satiri27@yahoo.co.id>
Kepada: "belajar-access@yahoogroups.com" <belajar-access@yahoogroups.com>
Dikirim: Senin, 28 November 2011 12:15
Judul: Bls: Bls: [belajar-access] VBA Barcode Code 128
Dari: CGSATU <cgsatu@yahoo.com>
Kepada: "belajar-access@yahoogroups.com" <belajar-access@yahoogroups.com>
Dikirim: Kamis, 24 November 2011 9:54
Judul: Re: Bls: [belajar-access] VBA Barcode Code 128
To: "belajar-access@yahoogroups.com" <belajar-access@yahoogroups.com>
Sent: Wednesday, November 23, 2011 12:25 PM
Subject: Bls: [belajar-access] VBA Barcode Code 128
Kepada: "belajar-access@yahoogroups.com" <belajar-access@yahoogroups.com>
Dikirim: Rabu, 23 November 2011 10:10
Judul: Re: [belajar-access] VBA Barcode Code 128
To: Access Group <belajar-access@yahoogroups.com>
Sent: Wednesday, November 23, 2011 9:39 AM
Subject: [belajar-access] VBA Barcode Code 128
Attachment(s) from GALIH PERSADHA
1 of 1 File(s)
[MS_AccessPros] Run-Time error '2501'
Screen displays the following 2 messages. This is happening in MS Office 2007.
Run-time error '2501'
The OpenForm Action was cancelled.
Where the following code is triggered.
Private Sub ROUTINGID_DblClick(Cancel As Integer)
Dim stDocName As String
stDocName = " Control_All_Form "
On Error Resume Next
DoCmd.OpenForm stDocName, acNormal, , , , , "1" & "|" & Me.ROUTINGID & "|"
Error_exit:
Exit Sub
End Sub
Private Sub Form_Open(Cancel As Integer)
Dim rs As Object
Dim OpenArg_Txt, varArray As Variant
Dim intI As Integer
...
.
.
If Ltotal = "0" Then
NRF = "No record found "
If MsgBox(NRF, vbOKOnly) = vbOK Then
Cancel = True
End If
GoTo Exit_Form_Open
End If
....
.
.
Exit_Form_Open:
Exit Sub
End Sub
Can't figure out why the 'Resume Next' was not triggered at all. It always stopped at the DoCmd.
Debug shows cancel is = 0 when the code failed at the DoCmd.
Any help is appreciated.
Regards,
Thomas
Re: [belajar-access] Doc Management
On 30/11/11 6:12 PM, CGSATU wrote:
Oh data type attachment hanya ada di access 2010 ya ?Sekedar saran :MS Access 2010 kan ada Type Data Attachment. Bisa digunakan untuk menyimpan file .doc ataupun .jpgMengenai PK (Primary Key) alias ID Number kalau di MS Access, saya pikir tidak masalah. Malah yang saya pikir adalah semacam "pengkategorian".Jadi , kelompokkan semua dokumen itu dalam beberapa kategori : semisal kalau dalam lingkungan perusahaan ada kategori pemasaran, produksi, keuangan, direksi, rahasia, dsbMungkin kalau untuk sekolah, bisa berdasarkan mata pelajaran, dsbcgsatu
Ya saya terpikir pengelompokannya misal semua doc yg berhubungan dengan no.PO customer 123 dijadikan 1 PK dan selanjutnya terus.
ada yg pernah buat model spt ini ?
Re: [belajar-access] Mohon bantuanya dan arahanya untuk permasalahan file acces saya [1 Attachment]
Dear Jack Pulsa Bisa dicoba gunakan tools ini untuk memperbaiki data yg corrupt, sebelumnya backup terlebih dahulu atau tata caranya silakan simak link sbb: jangan lupa feedback nya ya buat kita-2... Thank you | Terima Kasih | Matur Suksema Sumiyanto --- On Wed, 30/11/11, Jack <jack.pulsa@gmail.com> wrote:
|
Attachment(s) from Sumiyanto Surabaya
1 of 1 File(s)
Re: RE: [MS_AccessPros] Gathering data from multiple spreadsheets
John,
Okay, so I need to delete the Last Month, Current and total fields from the "Assets" table? But leave those fields on Reed's Report. Then add tblunitmonthmileage to the Assets query. From there I need to tell the query to.....
What I need the query to do is show the Last Month - Current = Total, and average the last twelve months to get a monthly average usage. One problem is not all the unit has 12 months of history. And another problem is how does the query know what the Last month and Current month actually is? I have imported all the mileage I have archived into tblunitmonthmileage. and the structure is Unit#, YearMonth, Mileage.
Here is my Assets Query:
SELECT Assets.Unit, Assets.[Last Month], Assets.Current, Assets.CO, Assets.[Acc Code], Assets.[Well No], Assets.Model, Assets.Year, Assets.Description, Assets.Driver, Assets.[Mobile Phone], Assets.Department, Assets.[Vin#], Assets.License, Assets.Acquired, Assets.Weight, Assets.Supervisor, Assets.Images, Assets.Retired, Assets.Total, Assets.Sold, Assets.Notes
FROM Assets INNER JOIN tblunitmonthmileage ON Assets.Unit = tblunitmonthmileage.[Unit#]
WHERE (((Assets.Sold)=False));
Tiffany
________________________________
From: John Viescas <john@viescas.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Wednesday, November 30, 2011 2:40 PM
Subject: RE: RE: [MS_AccessPros] Gathering data from multiple spreadsheets
Tiffany-
Since LastMonth and Current will be constantly changing, those are calculated fields that should never be stored in a table. If you have loaded the most recent data into tblUnitMonthMileage, you can always find out the reading for the current month by joining Assets to tblUnitMonthMileage and filtering for the latest month. You can use the query I gave you to calculate the miles traveled 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: Wednesday, November 30, 2011 10:25 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: RE: [MS_AccessPros] Gathering data from multiple spreadsheets
Hi John,
I think I need to go back a few steps, let's tackle this from a step to next step perspective.
In my "Assets" table I have two fields, Last month and Current. I would like to tell those fields to get the data from my tblunitmonthmileage. Can I do link two tables this way? If so, How do I do this? The rows in tblunitmonthmileage are Unit#,YearMonth (yyyymm), Mileage. How does it determine what Last Month and Current is?
Tiffany
________________________________
From: John Viescas <john@viescas.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Wednesday, November 30, 2011 12:52 AM
Subject: RE: RE: [MS_AccessPros] Gathering data from multiple spreadsheets
Tiffany-
Query to calculate mileage:
SELECT T1.[Unit#], T1.YearMonth, T1.Mileage - NZ(T2.Mileage, 0) As Miles
FROM tblUnitMonthMileage As T1 LEFT JOIN
tblUnitMonthMileage As T2
ON T1.[Unit#] = T2.[Unit#]
AND T2.YearMonth = IIf(Right(T1.YearMonth, 2) = "01", Format(CInt(Left(T1.YearMonth, 4)) - 1, "0000") & "12", Left(T1.YearMonth, 4) & Format(CInt(Right(T1.YearMonth, 2)) - 1, "00"))
Explanation:
tblUnitMonthMileage has mileage readings for the end of each month. The YearMonth is stored as YYYYMM - I assume this is correct. This query joins two copies of tblUnitMonthMileage with a match on Unit# and a match on the *previous* month. The complex IIf is doing this:
If the month in the first copy is 01, look for a match in the second copy on (year - 1) and the value 12. For example, if YearMonth = "201101", it looks to match on "201012" - subtracting 1 from the year part and looking for December.
If the month in the first copy is NOT 01, look for a match using the current year and the month - 1. For example, if the YearMonth = "201109", it looks to match on "201108" - using the current year and subtracting 1 from the month part.
This should end up with rows for a given unit matching like this:
T1.YearMonth T2.YearMonth T1.Mileage T2.Mileage
201101 201012 3,420 1,500
201102 201101 4,500 3,420
201103 201102 6,000 4,500
... etc.
Subtract T2.Mileage from T1.Mileage to get the miles driven for T1.YearMonth.
Got it?
If that works for you, you can use that query as the source for an Append or Make Table query to build tblUnitMonthMiles that has the distance driven each month, not the mileage reading.
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 29, 2011 11:39 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: RE: [MS_AccessPros] Gathering data from multiple spreadsheets
John,
Assets Query:
SELECT Assets.Unit, Assets.[Last Month], Assets.Current, Assets.CO, Assets.[Acc Code], Assets.[Well No], Assets.Model, Assets.Year, Assets.Description, Assets.Driver, Assets.[Mobile Phone], Assets.Department, Assets.[Vin#], Assets.License, Assets.Acquired, Assets.Weight, Assets.Supervisor, Assets.Images, Assets.Retired, Assets.Total, Assets.Sold, Assets.Notes
FROM Assets
WHERE (((Assets.Sold)=False));
Assets Table:
The Query shows all the fields in the table.
Mileage is the odometer reading at the end of the month. Last Month-Current= total (is the actual miles). Odometer reading-Odometer reading=miles driven.
Tiffany
________________________________
From: John Viescas <john@viescas.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Tuesday, November 29, 2011 2:44 PM
Subject: RE: RE: [MS_AccessPros] Gathering data from multiple spreadsheets
Tiffany-
What is the SQL of Assets Query? What is the structure of the Assets table? It sounds like you need another table called perhaps tblUnitMonthMiles - Unit#, YearMonth, Miles - calculated from the data stored in tblUnitMonthMileage. What is the mileage reading in tblUnitMonthMileage? The odomoter at the beginning of the month, end of the month, or some random reading date within the 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 29, 2011 10:33 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: RE: [MS_AccessPros] Gathering data from multiple spreadsheets
John,
Sorry... Reed's Report is currently getting all the data from the "Assets Query" this query is linked to my "Assets" table. I need to show on Reed's Report a monthly average using the last 12 months mileage but the info to get it from is on the "tblunitmonthmileage". The Unit# is the key between these tables. The structure for "tblunitmonthmileage" is unit#, YearMonth, Mileage.
Now, the "Assets" table has the Last Month-Current=Total fields and these are on "Reed's Report" but they are not pulling the data from anywhere, they are there because I have been hand entering them every month. But, I would like it if those fields were linked to the "tblunitmonthmileage" somehow because that would save me alot of work. As I said before, you showed me a way to append that data (mileage) every month and it gets appened into the "tblunitmonthmileage".
Tiffany
________________________________
From: John Viescas <john@viescas.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Tuesday, November 29, 2011 1:30 PM
Subject: RE: RE: [MS_AccessPros] Gathering data from multiple spreadsheets
Tiffany-
I have no clue what "Reed's Report" looks like. What are the tables involved, and what is the structure? What query are you using as the Record Source?
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 29, 2011 9:15 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: RE: [MS_AccessPros] Gathering data from multiple spreadsheets
John,
That is the name of the report.
Tiffany Atchley
WWW.ODYSSEYMINISHETLANDS.NET
________________________________
From: John Viescas <john@viescas.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Tuesday, November 29, 2011 1:00 PM
Subject: RE: RE: [MS_AccessPros] Gathering data from multiple spreadsheets
Tiffany-
I'm not sure what you mean by "Reed's Report."
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 29, 2011 8:18 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: RE: [MS_AccessPros] Gathering data from multiple spreadsheets
John,
Awesome! Glad I am one the right track. I am learning by leaps and bounds here.
I need to show the monthly mileage on an existing report (Reed's Report) and an average for the year per unit. The unit# is already showing on "Reed's Report" and he has "last Month" & "Current" showing on his report, this info is actually coming from another table. I would like to replace "Last Month" with the Yearly Average. Could you guide me through this please?
Thanks,
Tiffany
________________________________
From: John Viescas <john@viescas.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Tuesday, November 29, 2011 9:19 AM
Subject: RE: RE: [MS_AccessPros] Gathering data from multiple spreadsheets
Tiffany-
Glad to hear you've made good progress. I don't think your new table duplicates data. The one I had you build merely contains the raw data - the Unit, Month, and the Mileage reading. Your new table sounds like you created it from a query that calculates the mileage driven. Miles driven during a month isn't the same as the mileage reading, so it's not duplicate data.
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 29, 2011 5:01 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: RE: [MS_AccessPros] Gathering data from multiple spreadsheets
Hi John,
Well I have compiled all the mileages from the year by unit by month, in the format you suggested in a table. Now I need to show the monthly mileage on an existing report and an average for the year per unit. The unit is already showing on "Reed's Report" and he has "last Month" & "Current" showing on his report, this info is actually coming from another table. I would like to replace "Last Month" with the Yearly Average.
I believe you have told me not to duplicate data in tables. But, I have done just that. My Assets table has two fields "last Month" - "Current" = Total and my new tblunitmonthmileage that we created together has the mileage listed as monthly per unit. Is this a problem? And I have a form that that has these LM & Current in it so I can add them in every month. But, you showed me an easier way unbeknowst to you. Due to the table you had me make I no longer have to hand enter all the mileage every month I can now append it and it takes just minutes as to opposed to an hour. Thank you!
Tiffany
________________________________
From: John Viescas <john@viescas.com>
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@yahoo.com>
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@viescas.com>
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@yahoo.com]
Sent: Wednesday, November 23, 2011 7:15 AM
To: john@viescas.com
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@viescas.com>;
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@viescas.com <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@viescas.com <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@viescas.com <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@viescas.com <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@viescas.com <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@viescas.com <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]
------------------------------------
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]
Re: [MS_AccessPros] Linked tables question
John,
I did not explain it correctly. The databases have locking files in the folder but are not locked out from others getting in them. I was trying to figure out a way to have the VP see the data without having to link the table and creating the lock file in the folder so people do not think that someone is in the databases.
Jim Wagner
________________________________
________________________________
From: John Viescas <john@viescas.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Tuesday, November 29, 2011 1:29 PM
Subject: RE: [MS_AccessPros] Linked tables question
Jim-
Two possibilities:
1) Not everyone has full access to the folder in which the database is stored.
2) Someone crashed out of the database and left a .ldb file hanging around.
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 Jim Wagner
Sent: Tuesday, November 29, 2011 9:10 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Linked tables question
John,
The problem is that we have conditioned the employees to see if there is a locked file before entering the database, to not go in it. It looks like someone is in the database but they are not. Also if we make a copy of the database for maintenance, testing or to fix something the database looks like someone is in them and actually nobody is.
Jim Wagner
________________________________
________________________________
From: John Viescas <john@viescas.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Tuesday, November 29, 2011 1:01 PM
Subject: RE: [MS_AccessPros] Linked tables question
Jim-
You get locked out:
a) If one user opens exclusive
b) If one user opens read-only
If both users have full access to the database with the linked table and are
opening shared, there should be no problem.
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 luvmymelody
Sent: Tuesday, November 29, 2011 8:21 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Linked tables question
Hello all,
We have 2 databases that we support that have a linked table between them. On
the form in the Executive VP database there is a subform with the source for the
subform has a Select statement
SELECT [LatestPSS].[movedate], [LatestPSS].[BALANCE] FROM LatestPSS; this shows
the VP the data she needs to see on a daily or even when she needs to see it.
The data is manipulated in the other database by a user who updates the
information. The purpose for this way is so the VP does not have to open the
other database just to see 2 fields values.
The issue is that whenever anyone is in either database, the database gets
locked or shows that someone is in both databases.
Is there a way of using a non linked table solution to get the data to the VP
without linking tables?
Thank you
Jim Wagner
------------------------------------
Yahoo! Groups Links
[Non-text portions of this message have been removed]
------------------------------------
Yahoo! Groups Links
[Non-text portions of this message have been removed]