[OT] MS Access and Filemaker

#1

For those who know both apps, I need to know if I can do something in
Access, which I don't know. In Filemaker terms I would create a
database with one record per utility invoice we receive. I have nearly
100 invoices. I want to track as I receive the invoices each month. I
would do this in a portal. I can look through the portal to see all
invoices processed.

Does Access have similar functionality? I don't have a problem getting
a book to learn Access if I can do this. The company only uses MS
products. I am too low on the totem pole to effect such a change. The
software decisions are made at a corporate office in NJ approved by a
parent company in Japan.

I don't want to try to track this through spreadsheets, so I am willing
to use this as a learning experience into Access (gag). ;-)

TK

#2

TKnTexas wrote:

Quote:

For those who know both apps, I need to know if I can do something in
Access, which I don't know. In Filemaker terms I would create a
database with one record per utility invoice we receive. I have nearly
100 invoices. I want to track as I receive the invoices each month. I
would do this in a portal. I can look through the portal to see all
invoices processed.

Does Access have similar functionality? I don't have a problem getting
a book to learn Access if I can do this. The company only uses MS
products. I am too low on the totem pole to effect such a change. The
software decisions are made at a corporate office in NJ approved by a
parent company in Japan.

I don't want to try to track this through spreadsheets, so I am willing
to use this as a learning experience into Access (gag). ;-)

TK

yes, you certainly can do this in access (or about any db platfrom that
i am aware of). As you probably know, access ues SQL. you can write
your own sql queries to contstrain your found set (or use the query
editor to drag and drop fields and enter constraints.

you can create forms in design view to display your data. A similar
function to portals can also be accomplished by inserting what is known
as a 'sbutable' into the form that displays your data (provided the two
tables have a realationship defined).

hope that helps.

best,
sg

#3

But it sounds like you just need to get the data out of Access. If that's
true, you don't need to mess with Access' forms, or really learn much about
the program; you just need to learn enough to either export the data from
Access into something Filemaker can eat (Excel or text), or pull the data
directly into Filemaker via an ODBC connection, whcih would come closer to
what Stephen is describing. That's a better method, but you would need to
learn something about making that ODBC connection and setting up the SQL
statements. That's still not rocket science; the main difficulty, in my
view, is handling Filemaker's method of escaping quotes for SQL paramaters.

Not sure what Stephen means by 'use the query editor to drag and drop fields
and enter constraints.' Do you mean the editor in Access? Because there's a
sort of crude SQL query editor on the Filemaker side. Same question about
the constraints-- did you mean field validation on the Filemaer side (which
will come over as constraints)? Or something on the Access side?

--
John Weinshel
Datagrace
Vashon Island, WA
(206) 463-1634
Certified For Filemaker 8
Certified For Filemaker 7

"MaximalVariance" <StephenTGallagher (AT) gmail (DOT) com> wrote

Quote:

TKnTexas wrote:
For those who know both apps, I need to know if I can do something in
Access, which I don't know. In Filemaker terms I would create a
database with one record per utility invoice we receive. I have nearly
100 invoices. I want to track as I receive the invoices each month. I
would do this in a portal. I can look through the portal to see all
invoices processed.

Does Access have similar functionality? I don't have a problem getting
a book to learn Access if I can do this. The company only uses MS
products. I am too low on the totem pole to effect such a change. The
software decisions are made at a corporate office in NJ approved by a
parent company in Japan.

I don't want to try to track this through spreadsheets, so I am willing
to use this as a learning experience into Access (gag). ;-)

TK

yes, you certainly can do this in access (or about any db platfrom that
i am aware of). As you probably know, access ues SQL. you can write
your own sql queries to contstrain your found set (or use the query
editor to drag and drop fields and enter constraints.

you can create forms in design view to display your data. A similar
function to portals can also be accomplished by inserting what is known
as a 'sbutable' into the form that displays your data (provided the two
tables have a realationship defined).

hope that helps.

best,
sg

#4

John Weinshel wrote:

Quote:

But it sounds like you just need to get the data out of Access. If that's
true, you don't need to mess with Access' forms, or really learn much about
the program; you just need to learn enough to either export the data from
Access into something Filemaker can eat (Excel or text), or pull the data
directly into Filemaker via an ODBC connection, whcih would come closer to
what Stephen is describing. That's a better method, but you would need to
learn something about making that ODBC connection and setting up the SQL
statements. That's still not rocket science; the main difficulty, in my
view, is handling Filemaker's method of escaping quotes for SQL paramaters.

Not sure what Stephen means by 'use the query editor to drag and drop fields
and enter constraints.' Do you mean the editor in Access? Because there's a
sort of crude SQL query editor on the Filemaker side. Same question about
the constraints-- did you mean field validation on the Filemaer side (which
will come over as constraints)? Or something on the Access side?

--
John Weinshel
Datagrace
Vashon Island, WA
(206) 463-1634
Certified For Filemaker 8
Certified For Filemaker 7

"MaximalVariance" <StephenTGallagher (AT) gmail (DOT) com> wrote in message
news:1163779545.084913.280330 (AT) k70g2000cwa (DOT) googlegroups.com...

TKnTexas wrote:
For those who know both apps, I need to know if I can do something in
Access, which I don't know. In Filemaker terms I would create a
database with one record per utility invoice we receive. I have nearly
100 invoices. I want to track as I receive the invoices each month. I
would do this in a portal. I can look through the portal to see all
invoices processed.

Does Access have similar functionality? I don't have a problem getting
a book to learn Access if I can do this. The company only uses MS
products. I am too low on the totem pole to effect such a change. The
software decisions are made at a corporate office in NJ approved by a
parent company in Japan.

I don't want to try to track this through spreadsheets, so I am willing
to use this as a learning experience into Access (gag). ;-)

TK

yes, you certainly can do this in access (or about any db platfrom that
i am aware of). As you probably know, access ues SQL. you can write
your own sql queries to contstrain your found set (or use the query
editor to drag and drop fields and enter constraints.

you can create forms in design view to display your data. A similar
function to portals can also be accomplished by inserting what is known
as a 'sbutable' into the form that displays your data (provided the two
tables have a realationship defined).

hope that helps.

best,
sg

great points, john, thanks! i tend to bottom post, so hope this doesnt
confuse the thread too much...

my (perhaps 'mis') -understanding was that the OP via client restraints
wanted an MS only solution. i agree with you that ODBC (or JDBC or any
open connectivity for that matter) could readily be employed to pull
the data into FM and vice versa.

as for the constraints, granted FM has something crude for SQL-ish (not
really ANSI is it?) i was more referring to the access query editor.
right now im on a -nix box, so i don't have access in front of me to
know what its called exactly--maybe query builder or some ilk, if you
or anyone still wants to know (but you probably do know) i can
look...but, the gist is one can generate reg-ex-'ish' expressions or at
least SQL's 'LIKE' or 'HAVING' etc and particular field
constraints to limit the records returned...

while i only work in the biostatistics/genetic modeling realm (and am a
big proponent of FM for solving epidemiological database dilemmas where
study managers/Principal Investigators may have limited programmatic
background) i still see the MS ubiquity, however for bang for buck, we
at UPENN are moving strongly to FM. Obviously, ORACLE, DB2, MYSQL have
their place, but there are a variety of niches to fill and bridges to
be crossed...

hope that helps the OP and clarifies what i meant, john.--thanks.

best,
sg

#5

Yes, I am wanting an Access only solution. I like that in FMPro I
could just view the portal of any utility company and see if a current
invoice has been processed.

Within the FMPro application I understand relationships of parent to
child file... the one to many. If I understand correctly, I can do
this with a relationship and a subtable in Access? It seems that so
many accounting types that I meet, are spreadsheet-focused versus using
a database. When a database is suggested they thing that means needing
to hire consultants to create an elaborate application. Or, trying to
do things in Excel that are ackward. Each computer has Access. Right
now I am temping with the company. But if I am hired, I will create
the database to do what I want. I do appreciate all the responses.
Thanks.
TK

MaximalVariance wrote:

Quote:

John Weinshel wrote:
But it sounds like you just need to get the data out of Access. If that's
true, you don't need to mess with Access' forms, or really learn much about
the program; you just need to learn enough to either export the data from
Access into something Filemaker can eat (Excel or text), or pull the data
directly into Filemaker via an ODBC connection, whcih would come closer to
what Stephen is describing. That's a better method, but you would need to
learn something about making that ODBC connection and setting up the SQL
statements. That's still not rocket science; the main difficulty, in my
view, is handling Filemaker's method of escaping quotes for SQL paramaters.

Not sure what Stephen means by 'use the query editor to drag and drop fields
and enter constraints.' Do you mean the editor in Access? Because there's a
sort of crude SQL query editor on the Filemaker side. Same question about
the constraints-- did you mean field validation on the Filemaer side (which
will come over as constraints)? Or something on the Access side?

--
John Weinshel
Datagrace
Vashon Island, WA
(206) 463-1634
Certified For Filemaker 8
Certified For Filemaker 7

"MaximalVariance" <StephenTGallagher (AT) gmail (DOT) com> wrote in message
news:1163779545.084913.280330 (AT) k70g2000cwa (DOT) googlegroups.com...

TKnTexas wrote:
For those who know both apps, I need to know if I can do something in
Access, which I don't know. In Filemaker terms I would create a
database with one record per utility invoice we receive. I have nearly
100 invoices. I want to track as I receive the invoices each month. I
would do this in a portal. I can look through the portal to see all
invoices processed.

Does Access have similar functionality? I don't have a problem getting
a book to learn Access if I can do this. The company only uses MS
products. I am too low on the totem pole to effect such a change. The
software decisions are made at a corporate office in NJ approved by a
parent company in Japan.

I don't want to try to track this through spreadsheets, so I am willing
to use this as a learning experience into Access (gag). ;-)

TK

yes, you certainly can do this in access (or about any db platfrom that
i am aware of). As you probably know, access ues SQL. you can write
your own sql queries to contstrain your found set (or use the query
editor to drag and drop fields and enter constraints.

you can create forms in design view to display your data. A similar
function to portals can also be accomplished by inserting what is known
as a 'sbutable' into the form that displays your data (provided the two
tables have a realationship defined).

hope that helps.

best,
sg

great points, john, thanks! i tend to bottom post, so hope this doesnt
confuse the thread too much...

my (perhaps 'mis') -understanding was that the OP via client restraints
wanted an MS only solution. i agree with you that ODBC (or JDBC or any
open connectivity for that matter) could readily be employed to pull
the data into FM and vice versa.

as for the constraints, granted FM has something crude for SQL-ish (not
really ANSI is it?) i was more referring to the access query editor.
right now im on a -nix box, so i don't have access in front of me to
know what its called exactly--maybe query builder or some ilk, if you
or anyone still wants to know (but you probably do know) i can
look...but, the gist is one can generate reg-ex-'ish' expressions or at
least SQL's 'LIKE' or 'HAVING' etc and particular field
constraints to limit the records returned...

while i only work in the biostatistics/genetic modeling realm (and am a
big proponent of FM for solving epidemiological database dilemmas where
study managers/Principal Investigators may have limited programmatic
background) i still see the MS ubiquity, however for bang for buck, we
at UPENN are moving strongly to FM. Obviously, ORACLE, DB2, MYSQL have
their place, but there are a variety of niches to fill and bridges to
be crossed...

hope that helps the OP and clarifies what i meant, john.--thanks.

best,
sg

#6

Yeah, Access will do pretty much anything Filemaker can do, but the
landscape and language look different. Whole different world.

--
John Weinshel
Datagrace
Vashon Island, WA
(206) 463-1634
Certified For Filemaker 8
Certified For Filemaker 7

"TKnTexas" <tkntexas55 (AT) aol (DOT) com> wrote

Quote:

Yes, I am wanting an Access only solution. I like that in FMPro I
could just view the portal of any utility company and see if a current
invoice has been processed.

Within the FMPro application I understand relationships of parent to
child file... the one to many. If I understand correctly, I can do
this with a relationship and a subtable in Access? It seems that so
many accounting types that I meet, are spreadsheet-focused versus using
a database. When a database is suggested they thing that means needing
to hire consultants to create an elaborate application. Or, trying to
do things in Excel that are ackward. Each computer has Access. Right
now I am temping with the company. But if I am hired, I will create
the database to do what I want. I do appreciate all the responses.
Thanks.
TK

MaximalVariance wrote:
John Weinshel wrote:
But it sounds like you just need to get the data out of Access. If
that's
true, you don't need to mess with Access' forms, or really learn much
about
the program; you just need to learn enough to either export the data
from
Access into something Filemaker can eat (Excel or text), or pull the
data
directly into Filemaker via an ODBC connection, whcih would come closer
to
what Stephen is describing. That's a better method, but you would need
to
learn something about making that ODBC connection and setting up the
SQL
statements. That's still not rocket science; the main difficulty, in my
view, is handling Filemaker's method of escaping quotes for SQL
paramaters.

Not sure what Stephen means by 'use the query editor to drag and drop
fields
and enter constraints.' Do you mean the editor in Access? Because
there's a
sort of crude SQL query editor on the Filemaker side. Same question
about
the constraints-- did you mean field validation on the Filemaer side
(which
will come over as constraints)? Or something on the Access side?

--
John Weinshel
Datagrace
Vashon Island, WA
(206) 463-1634
Certified For Filemaker 8
Certified For Filemaker 7

"MaximalVariance" <StephenTGallagher (AT) gmail (DOT) com> wrote in message
news:1163779545.084913.280330 (AT) k70g2000cwa (DOT) googlegroups.com...

TKnTexas wrote:
For those who know both apps, I need to know if I can do something
in
Access, which I don't know. In Filemaker terms I would create a
database with one record per utility invoice we receive. I have
nearly
100 invoices. I want to track as I receive the invoices each month.
I
would do this in a portal. I can look through the portal to see all
invoices processed.

Does Access have similar functionality? I don't have a problem
getting
a book to learn Access if I can do this. The company only uses MS
products. I am too low on the totem pole to effect such a change.
The
software decisions are made at a corporate office in NJ approved by
a
parent company in Japan.

I don't want to try to track this through spreadsheets, so I am
willing
to use this as a learning experience into Access (gag). ;-)

TK

yes, you certainly can do this in access (or about any db platfrom
that
i am aware of). As you probably know, access ues SQL. you can write
your own sql queries to contstrain your found set (or use the query
editor to drag and drop fields and enter constraints.

you can create forms in design view to display your data. A similar
function to portals can also be accomplished by inserting what is
known
as a 'sbutable' into the form that displays your data (provided the
two
tables have a realationship defined).

hope that helps.

best,
sg

great points, john, thanks! i tend to bottom post, so hope this doesnt
confuse the thread too much...

my (perhaps 'mis') -understanding was that the OP via client restraints
wanted an MS only solution. i agree with you that ODBC (or JDBC or any
open connectivity for that matter) could readily be employed to pull
the data into FM and vice versa.

as for the constraints, granted FM has something crude for SQL-ish (not
really ANSI is it?) i was more referring to the access query editor.
right now im on a -nix box, so i don't have access in front of me to
know what its called exactly--maybe query builder or some ilk, if you
or anyone still wants to know (but you probably do know) i can
look...but, the gist is one can generate reg-ex-'ish' expressions or at
least SQL's 'LIKE' or 'HAVING' etc and particular field
constraints to limit the records returned...

while i only work in the biostatistics/genetic modeling realm (and am a
big proponent of FM for solving epidemiological database dilemmas where
study managers/Principal Investigators may have limited programmatic
background) i still see the MS ubiquity, however for bang for buck, we
at UPENN are moving strongly to FM. Obviously, ORACLE, DB2, MYSQL have
their place, but there are a variety of niches to fill and bridges to
be crossed...

hope that helps the OP and clarifies what i meant, john.--thanks.

best,
sg

#7

Hi T

Quote:

Does Access have similar functionality?

Access has better functionality including

1. Subforms - similar to portals
2. Events based programmiing which Filemaker does not have.
3. Custom classes so you can create you own syntax and map out an
object orientated design. eg if phoneBill.import( "somePath",
showDialog ) then
4. The ability to hide and enable objects programmatically.
5. The ability to create functions that can be used with SQL, which can
also be used to create dynamic SQL, eg select helloWorld(), or select *
from customers where type = currentFocus()
6. Excellent support for DDE, and automation with and for MS Excel, MS
Outlook, C++, Visual basic, even custom REALBssic applications. Similar
but faster and normally easier than AppleScript
7. Royalty free runtime engine.

Quote:

I don't want to try to track this through spreadsheets, so I am willing
to use this as a learning experience into Access (gag). ;-)

There are three hassles with Access
1. Multiuser database support is lousy - 3 users is the most I normally
recommend.
Solution connect to a real backend. I normally use postgresql.
2. A 2GB limit which I've only hit once. When I did I swapped the
backend to MYSQL.
3. It doesn't run on a Mac. You can get around this by using Remote
Desktop connecting to a Windows XP box, for multiuser logins to a
Windows XP professional machine for $125USD I use xpunlimited
http://www.xpunlimited.com/version_1...startmenu.html

If MS Access was available for the Mac, I imagine Filemaker would have
support for event based programming by now, which would be a good
thing.

Cheers

Keith

#8

Wow. . . thanks Keith. That information really helps.
TK

Keith Hutchison wrote:

Quote:

Hi T

Does Access have similar functionality?

Access has better functionality including

1. Subforms - similar to portals
2. Events based programmiing which Filemaker does not have.
3. Custom classes so you can create you own syntax and map out an
object orientated design. eg if phoneBill.import( "somePath",
showDialog ) then
4. The ability to hide and enable objects programmatically.
5. The ability to create functions that can be used with SQL, which can
also be used to create dynamic SQL, eg select helloWorld(), or select *
from customers where type = currentFocus()
6. Excellent support for DDE, and automation with and for MS Excel, MS
Outlook, C++, Visual basic, even custom REALBssic applications. Similar
but faster and normally easier than AppleScript
7. Royalty free runtime engine.

I don't want to try to track this through spreadsheets, so I am willing
to use this as a learning experience into Access (gag). ;-)

There are three hassles with Access
1. Multiuser database support is lousy - 3 users is the most I normally
recommend.
Solution connect to a real backend. I normally use postgresql.
2. A 2GB limit which I've only hit once. When I did I swapped the
backend to MYSQL.
3. It doesn't run on a Mac. You can get around this by using Remote
Desktop connecting to a Windows XP box, for multiuser logins to a
Windows XP professional machine for $125USD I use xpunlimited
http://www.xpunlimited.com/version_1...startmenu.html

If MS Access was available for the Mac, I imagine Filemaker would have
support for event based programming by now, which would be a good
thing.

Cheers

Keith