Need some serious help (1 Viewer)

scorpiob13

New member
Local time
Today, 05:20
Joined
Mar 12, 2019
Messages
6
I have a bookkeeping/accounting office and I need to build a database. So far I think the contact managements template is going to work if I can add a few things to it. I am a complete newbie so I need help in laymen terms. Here is what I'm trying to build and what I hope to have.


I need to build a basic customer table that has all contact info. I then also need to have a table of all our services provided and the price for each service (which will fluctuate). Somewhere within the customer table I need to be able to have a checklist or sub-table or something that I can pick which service(s) they use and the due dates for those services. Those due dates will be reoccurring every month/quarter/year.


Finally I need to be able to have something pop up when I open the database stating what is due that day/week.
 
Last edited:

jdraw

Super Moderator
Staff member
Local time
Today, 06:20
Joined
Jan 23, 2006
Messages
15,361
I have a bookkeeping/accounting office and I need to build a database.

I am a complete newbie so I need help in laymen terms. Here is what I'm trying to build and what I hope to have.

You might consider a supported commercial package. It's your business.
 

plog

Banishment Pending
Local time
Today, 05:20
Joined
May 11, 2011
Messages
11,611
I am a complete newbie so I need help in laymen terms.

Either you need to follow jdraw's advice and outsource this in some manner, or you need to dive in and learn the terms and concepts of a database. You cannot correctly build what you want by spending a day googling.

The first term you need to know is normalization (https://en.wikipedia.org/wiki/Database_normalization)
 

scorpiob13

New member
Local time
Today, 05:20
Joined
Mar 12, 2019
Messages
6
What is good pricing when it comes to outsourcing? Since we are a small business naturally funds are somewhat limited but I'm trying to use this database to help keep our clients under control for when we grow the business.
 

mike60smart

Registered User.
Local time
Today, 10:20
Joined
Aug 6, 2017
Messages
1,899
Hi

Can you upload a zipped copy of what you have done so far?
 

scorpiob13

New member
Local time
Today, 05:20
Joined
Mar 12, 2019
Messages
6
I haven't really done anything yet because I've just been looking at the templates. I was also playing around a little with the Northwind Services provided template I found somewhere else in the Forum but didn't really think it would work for what I need.
 

mike60smart

Registered User.
Local time
Today, 10:20
Joined
Aug 6, 2017
Messages
1,899
Hi

I would recommend that you just look at the Templates to provide some ideas but suggest that you build your database from scratch.

You need to think about using a pen an piece of paper to map out what you need. ie

A Customer has 1 or more Contacts.

The Customer also has Services provided and I need to know when these Services are Due.

from those 2 sentences we have the following tables.

tblCustomers
tblContacts
tblServicesProvided
tblServiceList

Start from there and then come back to us after reading the link regarding Normalisation.
 

mike60smart

Registered User.
Local time
Today, 10:20
Joined
Aug 6, 2017
Messages
1,899
Hi

You will need to make an effort to create your own as Templates are renowned for being difficult to adjust to what you need,
 

mike60smart

Registered User.
Local time
Today, 10:20
Joined
Aug 6, 2017
Messages
1,899
Hi

My suggested link in Post 9 is the way to go with what you need.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 05:20
Joined
Feb 28, 2001
Messages
26,996
To answer a different question, in this case regarding out-sourcing: Look online for products like QuickBooks or Quicken or other over-the-counter general ledger programs. I recall that PeachTree also makes something along these lines. You should be able to determine pricing for out-sourcing pretty well.

The problem with "rolling your own" is that for "newbies" (to which status you admitted in your first post in the thread) it can be tedious and time-consuming to address the level of detail required in such activities. But in a new business struggling to get off the ground, time is exactly what you don't have a lot of.

I am not going to tell you that you can't do it. I don't know your skill level so won't insult you in that way. You probably CAN do this - given enough time. My concern is as an old software engineer who used to have to make project timeline estimates for more than one company.

Custom databases are not like taking the car to the mechanic, naming a problem, and having them look it up in the ASE handbook to get an hours estimate and a parts list. The design phase of a database is THE SINGLE MOST IMPORTANT PHASE of the whole project. Management hates to hear this, but the design phase "takes what it takes" and there is NO estimating ahead of time (because to make an estimate of how long it will take to make an estimate is a two-pass algoritm.) Worse, if you screw up the design (sadly, easy to do if you are a novice), you will spend more time untangling the occasional mess than you will in actually making significant progress to your goal.

As you grow in experience, you will get better and the messes will become fewer, smaller, and farther between. But it takes time to get there. Do you have the months it would take to "roll your own" business management program? I don't want to pour water all over your fire, but thinking of the importance of getting your business going, I would do you a disservice if I didn't at least try to tell you what you face.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 06:20
Joined
Feb 19, 2002
Messages
42,970
I agree with Doc. Quickbooks will almost certainly have all the functionality you want and more that you will realize later that you also need. I don't know the current cost of the Quickbooks version you will need but I'm guessing it will be around $400. For that relatively small price you are getting millions of dollars worth of development effort. You cound never reproduce the featureset of Quickbooks for less so although "free" might seem attractive, you do have to consider that you are not a database expert and although you could, with our help, come up with something very simple to manage contacts and services, it will take weeks or even months and still be only a shadow of what you would get with Quickbooks. The downside of an off-the-shelf product is you get what you get and it works the way it works. That means that you might need to adjust your methods to work with the boxed software rather than having something that works exactly the way you want it to.

One nice thing about Quickbooks is that you can actually interface it with Access using the QB ODBC driver that lets Access link to and even update Quickbooks tables. But try working within it's featurset first. There are classes available to get you a quick start on using the product as well as numerous books with detailed help. Plus on-line help forums if you get stuck on something.
 

jrsaturnino

New member
Local time
Today, 17:20
Joined
Jun 21, 2019
Messages
9
Hi Mark,
Can you help me with this ms access. I am new to Ms access, basically my ideas came from this forum and other video materials. I want to have an automated MS access Ledger.
I want to have the following functions/features in access:
1. Sales Report
- Where i can search sales by date range with customer details and who make the
transaction
2. Collection reports
-where i can search collections by payment date with customer details

3. Ledger Report (Sales-Payments)
- Sales and Collections shall be posted here. My primary key is customer
id but this time we have to match sales and collection using the invoice
number
- Ledger Balance (Sales-Payments) must be running balance.

Please see attached picture.
 

Attachments

  • Screenshot (17).png
    Screenshot (17).png
    92.5 KB · Views: 117
  • SIOC_Credit and Collection Department_be.accdb
    1.2 MB · Views: 100

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 10:20
Joined
Jul 9, 2003
Messages
16,243
Hi Mark,

Can you help me with this ms access. I am new to Ms access, basically my ideas came from this forum and other video materials. I want to have an automated MS access Ledger.

I want to have the following functions/features in access:

1. Sales Report

- Where i can search sales by date range with customer details and who make the

transaction

2. Collection reports

-where i can search collections by payment date with customer details



3. Ledger Report (Sales-Payments)

- Sales and Collections shall be posted here. My primary key is customer

id but this time we have to match sales and collection using the invoice

number

- Ledger Balance (Sales-Payments) must be running balance.



Please see attached picture.

Well I can't see anyone in this thread called "Mark" so either you made a typo or possibly, you have copied and pasted this question to multiple forums!

The image is unclear to me, maybe because i'm using a mobile.

You are more likely to get help and advice if you demonstrate that you are actually tackling the problem yourself, instead of posting a picture and saying i want this that and the other!

Sent from my Pixel 3a using Tapatalk
 

plog

Banishment Pending
Local time
Today, 05:20
Joined
May 11, 2011
Messages
11,611
jrsa--you are not ready to build reports yet--you need to fix your tables. I am looking at your Relationship Tool and here's the big issues with them:

1. Incorrect use of foreign keys. Tables should not share more than 1 field. I see CustomerID and Company fields in every table in your Relationship tool. This is wrong. There should be 1 table that holds the CustomerID/Company data and then when you need to link to that data you just use the CustomerID in the foreign table.

2. Redundant data. Again, tables should not share more than 1 field. I see CI No and SI No in 2 tables. That data should only live in 1 of those. I don't know your data well enough to say which one, but I do know what you have is incorrect.

3. Field Names that contain data. When you have multiple field names named similarily (e.g. CR No, CI No, SI No...etc) you are effectively storing values in a field name. The CR, CI and SI values should be in a field in a table, not in a field name. Most likely you need a new table to hold that data.

4. Storing calculated values. Just from the name alone (Total Sales), I'm guessing that's a calculation of other data in other tables of your database. Thsi is incorrect. When you want to calculate a value, you do so in a query and reference the query, you do not store that value.

I suggest you put down these reports for now and focus on reading up on normalization (https://en.wikipedia.org/wiki/Database_normalization) and fix the issues I have mentioned.
 

Users who are viewing this thread

Top Bottom