Creating a balance 1

[bsound83]

I am a beginner with ms access 2003 and have recently been playing with access because it can hold more records than excel.

I have the following in a table:

DATE CUST# TRANSTYPE DESCRIP QTY COST
1/1/08 12345 B PRE-PAID SALE 10 $50.00
1/2/08 12345 C SERVICE USE -1 $0.00
1/3/08 12345 C SERVICE USE -1 $0.00

I would like to create a query to have the following results:

same as on top with [DATE] and [CUST#] but add...

TRANSTYPE DESCRIP QTY COST CHARGE BALANCE
B PRE-PAID SALE 10 $50.00 0.00 $50.00
C SERVICE USE -1 $0.00 -$5.00 $45.00
C SERVICE USE -1 $0.00 -$5.00 $45.00

It's easy to create a field for the balance: IIF([TRANSTYPE] = "B", [tbl].[COST],[tbl].[BALANCE]+ [CHARGE])...The problem I'm having is to create an expression/function that will calculate/populate the CHARGE table for me. Each pre-paid sale is at a different cost and basically the [CHARGE] field which I want to be ([COST]/[QTY] for the first row that has [TRANSTYPE] = "B".

Is this even possible with the current functions of access or do I need to create a custom VBA code. The other catch if...it is even possible is that there is an expiration date on the pre-paid service and if the customer doesn't use up the quantity of the service by a predetermine date then I need the balance to autopopulate what was left over.

I hope I'm making sense...

Thank You ahead for any responses.

Bing the beginner

 

[postermmxvicom]

If I am hearing you right, you want to calculate some field for some table based off of other fields.

You shouldn't have calculated results stored in tables. Have access calculate the values when you need them. You could make a query. I got to go, otherwise I'd go into detail. I'll be back tomorrow (although with all the smart guys here I am sure you'll have it by then)

One last thing: Sometimes I wonder; "Is that someone's signature? Or do they type that at the end of each post?

 

[fneily]

A word, there's alot of learning before jumping into Access. Here's some suggested reading
Fundamentals of Relational Database Design

http://r937.com/relational.html

Is that your table structure? Your only table? If you post all your table structures, it would help us get a better idea of your database design.

Tables must be normalized. So you could have something like:
tblCustomers
CustID
other customer fields

tblService
ServiceID
Description (Pre-paid sale, etc)

tblTransaction
TransID
CustID
ServiceID
TransType (B/C)
Qty
BasePrice
DateOfTrans
ExpirationDate

Cost would be calculated by Qty*BasePrice.
Looks like charge is Qty*BasePrice.
You can also check for any expiration dates.
You can add up all the Service Use and subtract from Pre-paid total cost.
Etc.

 

[bsound83]

Actually...the tables are a little more in depth than the examples I provided. I am actually pulling data from a POS software that I am able to extract the data as a dBF file, which create all my tables...and I have been creating additional tables to sort out what I wanted...for instance, I had to a new table to correlated the different between the pre-paid services vs. teh service used, etc.

The calculations I'm trying to make are in a query...and yes I am trying to calculate a record from a different record in a different row...


I placed these tables into a query already which is what you currently see on my first posting.

My goal is this to sum it in a nutshell...

I sell a tanning sessions - I get income, but it goes into an unearned revenue bank, the client's "balance"...each time someone comes in to tan, the client's "balance" drops by (the amount of the actual sale divide by the quantity of the tanning sessions; example: tanning package of 10, sells for $50.00, under a new field I need to create a "ChargeAmount" for -$5.00 ($50.00/10) for each time the client comes in to tan. This will continue until the balance becomes zero...The POS system provides the transaction but it doesn't create a "Depleting Bank for me which I am trying to create." All this information is in the same query...maybe I need to seperate the query so that they belong to a different recordset to make this work.

I may need to reconfigure my tables after I read the fundamentals of Relational Database Design...after which I will reconfigure my tables to make this work.

This is what my SQL view looks like at the moment:

SELECT [Total Sales Query].TRAN_DATE, [Total Sales Query].TRAN_TIME, [Total Sales Query].CUST_ID, [Total Sales Query].FULL_NAME, [Total Sales Query].TRAN_NUM, New_Trans_Code.NEW_CODE AS CCODE, Expire_Info.ITEM_DESC, [Total Sales Query].ITEM_COST, Expire_Info.[Session Quantity], Expire_Info.[Expiration Days], IIf([NEW_CODE]='C',"",[TRAN_DATE]+[Expiration Days]) AS Expration_Date, IIf([Session Quantity]=0,0,[ITEM_COST]/[Session Quantity]) AS SUB_COST, IIf([NEW_CODE]="B",[ITEM_COST],[Session Quantity]*(IIf([Session Quantity]=0,0,[ITEM_COST]/[Session Quantity]))) AS BALANCE
FROM Expire_Info INNER JOIN (New_Trans_Code INNER JOIN [Total Sales Query] ON New_Trans_Code.TRAN_TYPE = [Total Sales Query].TRAN_TYPE) ON Expire_Info.ITEM_DESC = [Total Sales Query].ITEM_DESC
GROUP BY [Total Sales Query].TRAN_DATE, [Total Sales Query].TRAN_TIME, [Total Sales Query].CUST_ID, [Total Sales Query].FULL_NAME, [Total Sales Query].TRAN_NUM, New_Trans_Code.NEW_CODE, Expire_Info.ITEM_DESC, [Total Sales Query].ITEM_COST, Expire_Info.[Session Quantity], Expire_Info.[Expiration Days], IIf([NEW_CODE]='C',"",[TRAN_DATE]+[Expiration Days]), IIf([Session Quantity]=0,0,[ITEM_COST]/[Session Quantity]), IIf([NEW_CODE]="B",[ITEM_COST],[Session Quantity]*(IIf([Session Quantity]=0,0,[ITEM_COST]/[Session Quantity]))), [Total Sales Query].ITEM_QTY
HAVING ((([Total Sales Query].TRAN_NUM)<>0) AND ((New_Trans_Code.NEW_CODE)="B" Or (New_Trans_Code.NEW_CODE)="C"))
ORDER BY [Total Sales Query].CUST_ID, [Total Sales Query].TRAN_NUM, New_Trans_Code.NEW_CODE;

I'm sorry it looks like a mess.

 

[fneily]

Is that an off the shelf POS or was it made for you? If it was made for you, then ask the developers to meet your needs.
Have you heard of QuickBooks? I know a couple of tanning salons that use it.
Besides that, it sounds like you want to tweak Access to suit your POS rather then adjust the POS data for Access. To me the tables offered would meet your needs.

 

[bsound83]

Your right fneily...at this particular moment...I'm trying to tweak access to suit the data from my POS. It's not that big of a deal...at the same time I get to learn how to utilize Access which will go a long way. The table you offered are really all I need...I inherited the POS software...it's called Sunease...the real pros of the software is that it tracks detailed client history such as how many minutes they tanned and it allows me to use it with my remote timer. I have a two in one salon...half hair and half tanning. My wife is the stylist and I run the rest. She went and bought a Hair salon POS software that she assume was going to track our tanning as well. However, it didn't go end up that way, the Hair POS went more towards hair salon then tanning...so I decided to keep both. This is currently a start-up so I'm gonna live with it until we can get business rolling.

In the mean-time. I just read the article off your link. I definitely need to denormalized some of my tables. Once I do so I'll get back to exactly what I need.

I heard of Quickbooks, I thought it was geared towards manufacturing or full retail so I opt out.

P.S. I wish there was a way for me to re-program the hair salon POS so that it would be more detailed in the tanning side. The Hair POS is called Salon Iris.

Thank You for the replies...this is my first forum...everyone has made my decision to jump on board very pleasant!

 

[bsound83]

I attached my table to view.

 

[bsound83]

I'm not sure if the first link is the right one. Try this one.

 

[fneily]

Unfortunately, I have Access 2000 so I couldn't open your database. You must have a later version.
Did you google for tanning salon software? There's got to be some out there. It would be a lot easier then learning the frustrating nuances of Access. Also with Access, somewhere you'll end up having to use code. It all depends on how much time you want to spend on Access. It can be its own job.
"everyone has made my decision to jump on board very pleasant!" We're just lulling you asleep.

 

[bsound83]

here is the converted file

 

[fneily]

Let me first say I sympathize that your data is coming from another system. They don't put data in a format needed by Access. Access tables are not like spreadsheets, or lists, or anything else people are familiar with. They must be constructed using the protocols of normalization - a concept quite a few people using Access either do not understand, or ignore.
I looked at your database - oh my. A few examples:
tblDiscount - no primary key. Transtype and Itemindex have multiple duplicate combination. How do you differeniate between records?

tblItemsTotal - Transtype and ItemIndex is the same for multiple ItemDescriptions. Again no primary key

Same problems with tblMisc.

tblTranscode - Transtype and Transcode are the same. Why two fields?

The big boy tblClient - (deep breath) You have correctly FirstName and LastName then incorrectly Fullname.

You may want a tblReferral that lists the various types. Easier to manage that way.

You'll want a tblBedUse. So fields like Lastin, LastHours, LastTime, LastBed, LastStore, TimesIn are taken out of the tblClient.

You have fields with P1.. to P4... Violates first normal form. Drop the number and you have duplicate field names. This'll lead to all kinds of complications. They can be their own table.

Basically, scrap it and build according to Access rules. Also, you need to know how tables relate to each other. One-to-one, one-to-many. You can not have many-to-many relationships.

If you really want to try and organize it for Access, start with paper and pencil. Getting the tables right is the hard part. If they're correct, forms, reports, queries just drop out.

 

[bsound83]

The big one that I'm more concerned with is the tblSalesHistory...that provides all of the transaction, but your right...even that table does not have a primary key to differentiate each item line. The only table that I imported from the POS was the tblSalesHistory and tblClient...everything else I tried to recreate by using a Make Table Query...however, My goal is to create a way to create a starting balance for each client whenever they purchase a tanning package and then for each time they use a session, the price associated with the session will be deducted from the balance. I'm trying my best to normalized the tables the best that I can...could you provide me with 1 example that would show me exactly how I can normalize the tables that you see...

the SessionQty and the ExpireDays in the tblTanPackage and tblBedUse are added data that I had hope would make the process easier...

You are right it looks more like a spreadsheet than anything.

Could you deliberate with tblSalesHistory? How could I normalize that better?

Thank You for your help so far...I'm learning so much.

 

[lespaul]

so did you read Fundamentals of Relational Database Design? The thing you want to ask about each piece of information you want to store is:

Is {this item} about the {table object}

Is the {invoice number} about the {client}?
Is the {payment type} about the {package}?

Why don't you store the number of sessions a person gets? That way you don't have to

create a way to create a starting balance for each client whenever they purchase a tanning package and then for each time they use a session, the price associated with the session will be deducted from the balance

You must have a standard package rate right?
[tt]
tblPackages
PackageID
NumberOfSessions
PricePerSession
[/tt]

then you store what package the person bought so you know the number of sessions they get...if you need to know how much money that translates to, you can join into this table to get the price per session.

So:
[tt]
tblPerson
ID
FName
LName
Address
anything else about the PERSON

tblPackages
ID
PackageName
NumberOfSessions
PricePerSession

tblPersonPackages
ID
PersonID
PackageID
PurchaseDate
Active
[/tt]



Leslie

Have you met Hardy Heron?

 

[fneily]

The Make table query is not for creating main Access tables. They're for something else.

Examples: (not "show me exactly" - then I'd be doing the work)

tblClient
ClientID Primary Key
other client info

tblTanningPackage
TanPakID Primary Key
Description
Price
QtyOfSessions

tblSessionHistory
SessHistID Primary Key
ClientID Foreign Key
TransType
EmployeeID
TimeIn
TimeOut
DateOfSession

tblSessionSales
SessSalesID Primary Key
ClientID Foreign Key
TanPakID Foreign Key
QtyOfPackages
StartDate
EndDate

tblProducts
ProductID Primary Key
Description
Price

tblProductSales
ProdSaleID Primary Key
ClientID Foreign Key
ProductID Foreign Key
DateOfSale
Qty


I split Tanning Packages and Other Products into two tables. So that means two tables for sales.
You can find TotalCost for Tanning by multiplying Price from tblTanningPackage and QtyOfPackages from tblSessionSales.
You can then get a count of how many sessions a client has used from tblSessionHistory. Multiply this count by whatever the base price is then subtract from the above TotalCost to get the current balance.
Bed use would be another table.

 

[bsound83]

Hello repliers.....I'm finally back. I'm basically trying to normalize the data information from the POS software...it's horrible that the data does not have a primary key to differentiate each row. I've tried my best to normalized the data and attached is what I got so far...I've tried finding the total cost and the quantity of sessions, but I'm not comprehending exactly how to do so....Can you please take a look at what I've got so far and see if I'm going the right direction.

Just so all isn't lost I've learned a lot from reading Fundamentals of Relational Database Design...

 

[bsound83]

SELECT tblSessionSales.ClientID, [FirstName] & "" & [LastName] AS ClientName, Sum([PricePerSession]*[QtyOfPackages]) AS Balance
FROM (tblSessionSales INNER JOIN tblTanPackage ON tblSessionSales.TanPackID = tblTanPackage.TanPackID) INNER JOIN tblClient ON tblSessionSales.ClientID = tblClient.ClientID
GROUP BY tblSessionSales.ClientID, [FirstName] & "" & [LastName];

This is what I got to look for the balance of each client....is this right?