Help again Oracle!..POS Register -2

M

MeOka

Ref: MeOka - POS. 13/08/03
Hi, I did post some time ago about newbie type things but now having done
some research I need to move on to more complicated things like actually
getting this to work! so here's the next stage:- coding my retail POS in
msAccess "cash register & ledger system" (for personal use), in my soon to
be opened second-hand book dvd & music shop.
Below, I have sketched out ( & I hope text formatting holds as intended!)
how the end-product will appear. The bits of code & form types that will
make it function is still a mystery to me, & I must admit extremely flat
headed about, & I will need advise on.
My reference books are, & all I can afford: Teach Yourself Access2000 by
Charles Siegel & Pocket Guide-Fast Answers msAccess - also have done 1/2
course on database design awhile ago, mostly covered data relationships &
data/form design, so am now familiar with the graphics/data side of it.
---------------------------------------
90% of table & fields are ready to go, with relevant relationships, below is
a table list so far:
*CBX - combo box table listing all the 'choice' options (lots) through list
boxes- through Tables.
*CUSTOMER- data base for customer info-who sold what & when.
*EMPLOYEE - to record my (& other) hours, rates, pay, tax held etc.
*PRICES DB - A very large database of titles & prices for referencing; holds
current marketplace prices & about 70,000 entries- and will need to be
constantly updated with bulk entries. Note; I have decided to run two
separate tables for stock listings; one for 'Stock' currently on hand or
have sold & a 'Prices Db' for reference purposes, only due to the problem of
bulk updates overwriting my stock on-hand entries & details; this would seem
to be the safest approach, & which I can maintain both through the same
form.
*STOCK - for stock currently held, listing purchases & sales with dates &
'unit count' for each title held or sold.
-Q1. How do I count units in this table into a 'count' field so I can tell
how many of that Title I hold or have sold, I understand there is a count
function, but are yet to get it to work!
*RECEIPTS - for record keeping of expense receipts. I'll scan & file the
receipts & hyperlink to its' location-not important.
*DAY LEDGER - main ledger for recording daily transactions & expenses, buy,
sell, tax, till float & balance & misc day to day expenses all run through
the Cash Register form.
*INVESTMENT LEDGER - for investments portfolio - all details & amounts,
profits/losses made & relevant taxes-linked to Financial Ledger.
*FINANCIAL LEDGER - links 'day ledger' & records end of day cashregister
money totals, also 'Investment Ledger' totals, & is used generally for end
of year 'accounting's record reports.
*BANK LEDGER- records bank account balance & bank reconciliations.
----------------------------Forms------------

*SELL form - shown below
*BUY form
*STOCK LOOKUP form
*TROLLEY form
*CASHREG form for buy & sell transactions.
Note: Stock Lookup, Trolley & Cashreg forms used in both buy & sell forms
(not yet implemented).
Below is a sketch for typical Buy Form components:
1) STOCK LOOKUP (query?) for buy & sell forms:
------------------------------------
| (A) data input | | (B) data input |
------------------------------------
| (C) list & select item |
------------------------------------ ------- ------
| (D) list & select itme | | (E) | | (F) |
------------------------------------ ------- ------
A = stock 'code' type-in lookup to 'Stock' table.
B = title type-in box for search to both 'Stock & Prices db' tables.
C = title listings from 'A' search to 'Stock Table'.
D = title listings from 'B' search to 'Prices Db' table for cross checking
prices.
E = button enters item C into 'Basket' box 2 below then clears all for next
search.
F = button appears on Buy form only & enters buy item title & prices
(possibly plural) & updates stock count field into the 'Stock' table & also
transaction details into 'Day Ledger'.
-Q2. above has me beat, I can't get any of this to work period!

2) BASKET: - grows with items selected from 'C' above.
------------------------------------------------------
| Stock Code | Title 01..from Box above | $10.00 |
| Stock Code | Title 02..from Box above | $20.00 |
| Stock Code | Title 03..from Box above | $70.00 |
| Stock Code | Title 04..from Box above | $10.00 |
| Stock Code | Title 05..from Box above | $50.00 |
-------------------------------------------------------
-Q3. Is the above a Table or Form?

3) CASHREG form:
TRANS DATE | autodate | (H
SUB TOTAL: | 160.00 | (H
Credit/Debit, Trade or Disc. | Disc 10% | -10.00 | (I
XYZ checkboxes TOTAL: | 150.00 | (J
sTAX: | 15.00 |
(K
TENDER: | 160.00 | (L
CHANGE: | 10.00 | (M
PAYMENT TYPE: | (N) | (O) |

K) = tax calc of 10% of Sub Total & entered into the 'Day Ledger' tax field
on button 'O' being activated.
O = button enters items 2, G, I, J, K & N into 'Day Ledger' fields, also
activates cash drawer & serial port receipt printer then clears item 2 & 3
boxes for next trans - how? I dunno!
N = listbox to CBX for payment types, it works, I can do this simple stuff.
-----------------------
Items 1, 2 &3 above are important problems to solve at the moment, so any
help with forms/subforms/queries/calc. coding will be GREATLY appreciated.
PS. laying out this page has also helped refine the final layout.
I feel I should put all this in one posting -just to give the full picture.
Also, wouldn't it be better for msAccess to have pre-built 'modules' for a
typical business setup with plug-in components, in addition to the complete
but vague db 'template' system currently used? to a newbie it all seems a
very raw & unpredictable process in compiling a database.
MeOka (oka=Aussie. [ ; )
 
A

Albert D. Kallal

Any reasons why you don't go out and purchase something like QuickBooks
Point of sale?

You can purchase the whole package for $800. That is less then 3 days of
developer time!

To develop your own POS system with accoutring will take you several years
to work, and the results will not be near what you can get for $800.
(perhaps your time is not very valuable).
Also, wouldn't it be better for msAccess to have pre-built 'modules' for a
typical business setup?

Not sure what you mean? It is general database tool, not a accounting
package. You can certainly go out and find companies that package solutions
in ms-access. For example, here is a accounting package for ms-access. You
can even download the trial edition. Source code and the ability to modify
forms is included if you purchase this system. I would certainly take a look
at this.

http://www.accountingsuccess.com/access_accounting_software_low_le.html

However, to expect ms-access to come with a general solution is like asking
that the folks at sears to make tools include the car, or whatever it is you
are planning to build with the tools!. Remember, ms-access is a development
tool just like a wrench, or a saw. Perhaps, people who do create wrenches
and saws should include more things a car, or even perhaps a garage opener
you are planning to setup with those tools!

I am not sure if you should become a software developer for the next two or
three years of your life. You might be better off trying to run your
business instead...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top