The Acess CHALLENGE!

  • Thread starter Thread starter Joe Challenge
  • Start date Start date
J

Joe Challenge

Hello,

The challenge is on!
I am asking for someone knowledgeable and skilled enough
to put together a DB. Up until now I have not met any so
called expert to be able to do it. So .... I am wondering
by the means of challenge to see if anyone can create this
DB. Here it is:
1) Open Access and got the templates section and look for
these DB's Inventory Control and Order Entry.
The Challenge.
Can anyone make these two DB's work together and respond
the same as they do individually. e.g. make an order and
removed from the inventory and add if it is returned.
Provide the same reports as they do individually.
Thanks
Joe Challenge
..
 
Thanks TC.

Thought about a similar reply, but couldn't be bothered with all his
postings.
 
At the risk of igniting a bidding war, I would begin work on this challenge
for $7,500 (US).

Jeff
 
Joe,

You will quickly learn that the experts here do not need nor want
challenges. They have conquered much greater challenges than you're offering
and they are here to actually offer help for free (how you missed this, I
haven't a clue). If you want to give them a challenge, as you've noticed,
they will gladly take it for a fee. But if you need help, they will actually
give it to you for free.

So if you have a problem, give it to the experts as a challenge and do
enclose a check. Otherwise, you can politely ask for help, and maybe you
will get a solution free of charge.


HTH,
Immanuel Sibero
 
I bid $10,000 (US).


--
Regards,

Adrian Jansen
J & K MicroSystems
Microcomputer solutions for industrial control
 
You had a lot of fun with this didn't you?
-----Original Message-----
Joe,

You will quickly learn that the experts here do not need nor want
challenges. They have conquered much greater challenges than you're offering
and they are here to actually offer help for free (how you missed this, I
haven't a clue). If you want to give them a challenge, as you've noticed,
they will gladly take it for a fee. But if you need help, they will actually
give it to you for free.

So if you have a problem, give it to the experts as a challenge and do
enclose a check. Otherwise, you can politely ask for help, and maybe you
will get a solution free of charge.


HTH,
Immanuel Sibero








.
Click to expand...
 
I guess a little Ebay in the mix can't hurt. I didn't
think it would cause such a wave. w00t! I love you guys
don't get upset I made a mistake. Anyone still want to
help I would like to learn how to do this?
Thanks
Joe Mistake
 
Seriously now, you need to post some specific questions the group can
answer, other than the obvious one of "Design me a database".

I looked at the Inventory database template a while ago, and found it a good
learning tool, but nowhere near good enough to handle the first product
costing/inventory control database I wrote. All you can learn from the
examples is the bare guts of how the various parts, like tables, queries,
forms, reports fit together and work. You still have to put in the hard
work of designing the system to suit your actual needs. It sounds like you
are starting from scratch here too, and I can assure you its a steep
learning curve. Your choice is either to learn a lot - and asking here is a
great way to get a start, or to invest in a commercial product.

--
Regards,

Adrian Jansen
J & K MicroSystems
Microcomputer solutions for industrial control
 
Yes I am serious now and thanks. I am in need of making
orders and keeping an inventory as the items are being
sold. I know the learning curve here is large but I have
come very close to mastering - Per Se' most of the other
Office programs ( FrontPage is no easy task either). But
Access messed up my head (lack of better words) It is not
the relational that confuses me it is the what each Item
is looking for such as: Does a form query a table or does
a table support the form When does a form need a query? To
add or subtract?. I dont know what is being pulled per se'
from what. Everything has to have a data source I know. It
is the order in which things work that confuses me. Like a
bad dream I hope this is temporary.
Thanks Adriene
Joe
 
Joe, your experience is not unique. Access is inherently more complex than
Word, more involved than Excel. You will need to be patient with yourself
and learn it gradually over several months, and you will need some resources
to do that.

If you learn from books, buy one and work through the exercises until you
understand what's happening. If you learn from classes, enrol in one and
again spend time working through the examples. If you learn by example,
examine the Northwind sample database. The calculated fields in the Orders
Subform will answer your question about when a form needs to be based on a
query rather than just a table.

For an introduction to calculated fields - why the belong in queries, not
tables - see:
Calculated fields
at:
http://allenbrowne.com/casu-14.html

When that all makes sense and you have begun to get a good feel for
programming in VBA, this article may be a useful introduction to your
specific question about stock quantities:
Inventory Control - Quantity On Hand
at:
http://allenbrowne.com/AppInventory.html
 
It is not
the relational that confuses me it is the what each Item
is looking for such as: Does a form query a table or does
a table support the form
Click to expand...

Data is stored in Tables (and only in Tables).

Data is selected, sorted, calculated, and arranged in Queries.

Data is displayed, edited, deleted, and maintained using Forms.

Data is printed using Reports.
When does a form need a query?
Click to expand...

I'd say ALWAYS. Even if you base a Form on a table directly, Access
will create a temporary hidden query in the background as the Form's
Recordsource.
To add or subtract?.
Click to expand...

Update queries, or VBA code, can add or subtract values in tables...
but, often, it's not necessary to actually *store* such calculated
values in tables. Read Allen's references to get some background on
how to handle calculated values in an Access database.
I dont know what is being pulled per se' from what.
Click to expand...

Well... that's both the strength and the weakness of using a sample
database. It's thoroughly worked out, and therefore is complicated and
(sometimes) a bit tricky, and since you can't easily comment Queries,
and often Microsoft doesn't do a terrific job of documenting their
samples, they can be risky as a guide to developing your own.
Everything has to have a data source I know. It
is the order in which things work that confuses me.
Click to expand...

"Strange loops or tangled heirarchies" are always confusing... <g>
 
w00t! This was good! Excellent description. Big, Big help
here! I got a handle on some of this now. Ok when you
create a table and then another it asks what relation is
this between the tables? Is Access asking me if this info
is related to one another? e.g Order entry table "Customer
Orders" and Inventory table will have different columns
and of course column names/values. So when an item is
entered into/from the form>> query>> it should remove it
from the column in Inventory say "Units on hand" What
removes the number in the Inventory table? The Query?
Thanks John
Joe
-----Original Message-----


Data is stored in Tables (and only in Tables).

Data is selected, sorted, calculated, and arranged in Queries.

Data is displayed, edited, deleted, and maintained using Forms.

Data is printed using Reports.


I'd say ALWAYS. Even if you base a Form on a table directly, Access
will create a temporary hidden query in the background as the Form's
Recordsource.


Update queries, or VBA code, can add or subtract values in tables...
but, often, it's not necessary to actually *store* such calculated
values in tables. Read Allen's references to get some background on
how to handle calculated values in an Access database.


Well... that's both the strength and the weakness of using a sample
database. It's thoroughly worked out, and therefore is complicated and
(sometimes) a bit tricky, and since you can't easily comment Queries,
and often Microsoft doesn't do a terrific job of documenting their
samples, they can be risky as a guide to developing your own.

"Strange loops or tangled heirarchies" are always
Click to expand...
 
I will do that. I have Office Inside Out for the Xp
versions of Office.I will study Access in this book.
Thanks Allen
Joe
 
(snip)
It is not the relational that confuses me it is the
what each Item is looking for such as: Does a
form query a table ...
Click to expand...

Joe, congratulations on perservering. You've now got some excellent tips to
start you going!

But here's my 2c's worth extra. You'll probably find that it >is< the
relational side of things, that is most complex to begin with; far more so
than understanding how to base a form on a table, etc.

If you do not immediately understand the following terms, you'll have lot's
of work on the relational side :-)
- primary key, foreign key, 1:m relationship, referntial integrity,
normalization.

Start here for a dry, but instructive, read:
http://support.microsoft.com/support/kb/articles/Q100139.ASP

HTH,
TC
 
Congratulations on persevering, in the face of a lot of ribbing! The other
portion of this thread sounds like you have a better handle on your
challenge than when you first posted.

Do keep coming back ... and to make your posts (and responses) more appropo,
you can use John V's descriptions to post each question to the most relevant
newsgroup (i.e., tablesdbdesign, queries, reports, ...).

Best of luck!

Jeff Boyce
<Access MVP>
 
Ok I will do that sounds really good
Thanks TC
Joe
-----Original Message-----



Joe, congratulations on perservering. You've now got some excellent tips to
start you going!

But here's my 2c's worth extra. You'll probably find that it >is< the
relational side of things, that is most complex to begin with; far more so
than understanding how to base a form on a table, etc.

If you do not immediately understand the following terms, you'll have lot's
of work on the relational side :-)
- primary key, foreign key, 1:m relationship, referntial integrity,
normalization.

Start here for a dry, but instructive, read:
http://support.microsoft.com/support/kb/articles/Q100139.A SP

HTH,
TC


.
Click to expand...
 
w00t! This was good! Excellent description. Big, Big help
here! I got a handle on some of this now. Ok when you
create a table and then another it asks what relation is
this between the tables?
Click to expand...

It doesn't ask; you should tell. You don't *have* to - you can create
a database with no relationships at all; but that's pretty useless.
Relationships are there to protect the integrity of your data.
Is Access asking me if this info
is related to one another?
Click to expand...

Just for example - each Customer will presumbably have many Orders.
Your Customers table should have a CustomerID field as a Primary Key;
your Orders table should have a CustomerID field of the same datatype
(Long Integer if the Customers CustomerID is an Autonumber) as a
"foreign key". You would go to the Relationships window and add both
tables; drag the CustomerID from Customers.CustomerID to
Orders.CustomerID.

This will prevent the addition of an order for a nonexistant customer.
It makes no sense to have an Order in the Orders table if there isn't
a corresponding customer!
e.g Order entry table "Customer
Orders" and Inventory table will have different columns
and of course column names/values. So when an item is
entered into/from the form>> query>> it should remove it
from the column in Inventory say "Units on hand" What
removes the number in the Inventory table? The Query?
Click to expand...

There should NOT BE A FIELD [Units On Hand].

The field *SHOULD NOT EXIST IN YOUR TABLE*, anywhere.

I know, I know... scary. But it's quite correct.

Instead, you should dynamically calculate the units on hand by a
totals query summing all the records where you add stock to the
inventory, and subtract all the sales.

This calculated field can be displayed, used in further calculations,
etc.; but unless you have a very special situation, it almost
certainly should not be stored in the table.
 
From memory, I think the article that I recommended, has a link at the
bottom, to another one. I think the second one is better (but read them both
anyway). I gave both to an inexperienced person recently, & she preferred
the second.

Good luck!

TC
 
Back
Top