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. [ ; )
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. [ ; )