Is there a way to....??

[netspirit]

I am trying to create a way for a friend of mine to record tanning sessions at a tanning business. So I thought ACCESS would be the way to go... and although I am an EXCEL instructor, I am TOTALLY stumped on how to create what she needs in ACCESS.

So...Is there a way to create a page (record) for each customer like a ledger page? And on page that is there a way to type in the DATE, MINUTES, BED #, AMOUNT PAID, AMOUNT USED, TOTAL (WHICH WOULD = THE AMOUNT PAID - THE AMOUNT USED) and be able to do this every single time the customer comes to tan?

Example: Customer comes in and gives my friend $40.00. She pulls up the record for her customer. She enters the DATE, MINUTES tanned on this date, Bed # used, the $40.00 payment (every 10 sessions) and then enters $4.00 for that day's tanning session and a balance of 36.00 is shown. There will be a page for each client. Each day the client comes in and tans, my friend pulls up that customer's record and then enters the data for that date and the balance will be shown for that date.

Can ACCESS do this? I tried creating a Database of customers, but I don't have a clue on how to be able to create a running ledger with rows for each date that shows the customer's balance. I can do this in Excel, but then I don't have a ledge page for each customer unless I use a separate sheet for each customer. There has to be a better way to do this and it seems like Access is what I need to use.

I would appreciate anyone who can steer me in the direction I need to go to figure this out.

Thanks,
Sharon

 

[The_Doc_Man]

Look up parent/child relationships or one/many relationships.

You want one table of customers and another table of visits, where the visit includes customer number, date, lamp exposure, etc...

Since you are an Excel user, you are probably not intimately familiar with normalization. For relational databases, this is an incredibly important factor. If you don't have a normalized database, what you want becomes cumberson faster than anything.

Once you have the one/many tables set up, you can build forms and reports. To do a report properly, you would create a query that does a JOIN of customers and visits on customer number. JOIN is in Access Help, you can read up on it. The report wizard can take that joined SELECT query and set up sorting and headers for each customer where the detail section is one tanning session.

For a form, you can create two parts. First, a parent form to show each customer. Second, a form to show each session. Now put the session form into the parent form using a sub-form control with the wizard enabled. It will ask the name of the field in each table that represents the "link" between the two tables. Which, I believe, is customer ID in this case.

In this forum, you can do searches. Look for parent-child relationships to see some ideas and some examples.

 

[netspirit]

Great...thanks.. I have started working on this by reading the help on the things you've described. I'm pretty good at looking things up in help, so this really tells me the direction to go.

Right now I am stuck on adding the forumulas....I have created my two tables and have tried to make the TOTAL field take the Paid field, subtract the Spent field and leave the total. I figured out how to do this once tonight, but deleted that table and now I can't do it again. Somehow I clicked on properties and built an expression, but now I can't figure out how I did it! So if you can help me figure that out, it would be great.

And then my next question would be.... How would I take the Total from one line and figure that into the forumula on the next line? (Example: On June 4, paid 40.00, spent 4.00, Total 36.00... Next record is June 6, spent 4.00. So the forumula takes the 36.00 and subtract the 4.00 to leave the 32.00 in the Total field. Easy in Excel, but this escapes me in Access. Can you help?

In the meantime I am reading up on the Relationships between the tables.

Thanks so much.

 

[gemma-the-husky]

effectively what you are getting in access is a multi page set of spreadsheets.

so one page (sheet) is your customers, and another is all the customer visits, for ALL customers. Access enables you to view this in many ways, so will automatically link the cutomers ot their related items in Excel - similar effect to using the autofilter.

So you don't quite need a spearate page for each customers visits/bills, you actually think about it in a different way, as described above.

This is the concept of a primary key/foreign key - you don't store the full customer details in each row of the bills sheet- you just store the index number that identifies the customer row.

if you design a form that shows all the visits as a continuous form, you will see a data repesentation thatr resembles a spreadsheet. now at the bottom in the form footer you can put a total by having a text box with a simple formula =sum(visitcost) say.

The next step is to link this form (which will show EVERY bill) to the customers, so that you only see the bills for the particular customer, and as you step thorugh the cutomers, the linked records will automatically updaqte.

if you already have this data in a spreadsheet, you can import it into a single access table, and try preparing a continuous form to get used to the idea.

but then you need to split off the customers form the sessions to get a true relational database - there are wizards that will help with this.

Its just getting used to a slightly different way of thinking

-the only other thing is to try to forget the row order thats inherent in excel - there is no order in access, everything is just a row of "similar" data - ie similar in that it corresponds to a designated set of criteria you select. eg visits unpaid, visits for a given customer, visits this month - you can then determine the order in which you want this presented - but unlike in excel where you can easily do a calc based on the previous row, you can't do this easily in Access, and in fact its better to try to design the data so you don't have to do this, if possible. In fact its the same effect as resorting the rows in excel after youve put some formulas in - the formulas may be meaningless or even worse dangerously misleading - , after you resort the rows.


-------------------
so to get to your scenario about deducting visits from deposits, think of it differently -

for each customer you need a payments table, and a visits table

then you can easily get a) the total payments for the customer, and b) the total cost of all the visits, and the balance is simply a) - b). [If you want to get smarter you could MATCH the visits to the payments and store this link, although its not strictly necessary - it just may or may not be useful in your scenario] This mechanism avoids any requirement to think about this visit in TERMS of any previous or later visit. If a visit is cancelled you can just delete it, or modify the charge to a cancelled rate charge, etc etc - and you don't have to recalculate ANY other data in your system, as each row (visit) is independent of any other

hope this helps

 

[jollofrice]

Another thing to consider when using calculations in Access reports is that Access does not store calculated values for future use. So, you can't perform a calculation using a calculated value, you have to insert the whole calculation. When I moved from Excel to Access, that gave me a lot of trouble until I figured it out.

 

[netspirit]

Ok... this is GREAT information and I think I am following most of it. I'll let you know if I can stuck again. THANKS!

 

[netspirit]

What next????

I'm stuck and can't figure out the next step. I have created three tables CUSTOMERS (customer ID which is phone # & Primary Key, first name, last name) VISITS (customer ID, Date, Bed #, Minutes, Session Cost) and PAYMENTS (customer ID and payment). Then I established the one to many relationship between the tables (Customer ID has a link line to both Customer ID's in each table).

Next I created a form for visits which included ALL visits for ALL customers and I created the text box which totals the Session Costs.

I'm stuck here.... What do I do next? I need a page/form/table that lists an individual customer and then a spreadsheet underneath the customer that pulls up all of the visits in a spreadsheet format. But I can't figure out how to get that.

I looked up JOIN in the help session, but it looks like JOIN is the same thing as relationships which I don't think it is. I think I'm not understanding this JOIN thing correctly. So where do I go from here?

Stuck

 

[jollofrice]

Use the forms wizard to create a Customer form. Then, create a Visits form and set its default view to datasheet. While viewing the Customer form in design view, use the controls wizard to make a subform using your Visits form. You shouldn't need SQL.

 

[netspirit]

Ok... I did that (with some... no, make that many...trials and errors). But I finally figured that part out. Thanks! I have a Customers Form and a Visits Form as a subform. I also have a balance in the header that subtracts the session costs from the payments. And I have played with colors, lines, property settings for Sunken, Raised, etc. Looking great so far.

Next question. I am going to create an Option Group on the right side of my screen with the following: Add a Client, Delete a Client, View a Report, etc. I have more research to do for these...but, before I do that, I need a Search textbox in the right top side of my Main Form that lets my friend type in a customer's name to pull up the record. I guess I insert a textbox, but how do I make it search by last name by just typing in the last name?

Thanks again for your help.