So how much would it cost to have this done

peskywinnets

Registered User.
Local time
Today, 14:57
Joined
Feb 4, 2014
Messages
582
I'm a one man business (still early days & with a major eye on cost...but also efficiency)...I have a sales database full of the usual stuff....customer name, billing address, shipping address, email address, Order Number, SKUs ordered, Quantity ordered, price paid etc.

I add to this main database daily by way of a text file import....essentially all new sales since the last import. What I need (because, I reckon at the moment it's beyond my ability...or perhaps more to the point, likely to be a time sump at a time when I should be focusing on my business ...not coding in Access!) is for an Access macro/VBA code to

1. Create a VAT invoice for all newly imported orders
2. Email the invoice out
3. Self update the Access database to mark the order as 'invoice emailed'.

I'll probably need step 1 & step 2 to be broken out (as opposed to chained), this is so I can check a few 'quirky' orders before the email is actually sent. However step 2 step 3 can be chained.

Basic Sample of my database...

Code:
Order No.         Item       Qty      Price    Shipping      Customer        

    1           widget A       1      £9.99      £2.99       Norma Stits    
    1           widget B       3      £19.99     £2.99       Norma Stits      
    2           widget C       1      £8.99      £8.99       Tom Cockles
    3           widget A       1      £9.99      £2.99       Drew Peacock  
    4           widget E       1      £9.99      £3.99       Mary Hinge
    4           widget F       1      £9.99      £3.99       Mary Hinge

Obviously there are a lot more fields, but I post the sample above to illustrate a couple of points....1. when a customer buys more than one product (item), there will be extra row entries (one row entry per 'different' product purchased)...each with the same common order number... 2.unfortunately the shipping the customer paid will be applied to each row (therefore in the above example, Brian Smith actually only paid £2.99 shipping, even though it looks like he paid two lots of £2.99 shipping...perhaps that quirk could be part of this job/task - remove any duplicate postages for multi product orders, leaving only the last row with the one associated postage paid?!)

So for someone that's pretty clued up on Access, how long would it take them to put together something that meets my requirements? (& how much should I expect to pay...if it's a lot of work & likely to be costly, then I'm going to have to just slog my way through it slowly, learning the pieces of the puzzle bit by bit!)
 
Last edited:
Might depend on the specific requirements of the invoice, but offhand I don't see anything particularly difficult. Emailing the invoice is easy, as is marking the orders as done. Presumably you don't have to worry about new orders coming in mid-process. I'd be surprised if it took more than an hour or two. Cost obviously depends on the individual.

Edit: have you tried presenting each step as a problem here on the forum?
 
Thanks Paul.

Edit: have you tried presenting each step as a problem here on the forum?

No I haven't...I watched a whole heap of youtube videos & all were quite lengthy...I therefore figured perhaps it might be a big ask to be guided through this via a forum. I've done a search on the forum, but there wasn't anything that went in to sufficient detail. I've seen a lot of mention of 'hack' Northwinds for invoicing...but (from what I can gather) Northwinds seems to be a manual entry style for entering sales (as opposed to importing sales en-masse)

That said, I'm not completely clueless (I've been working with Access for about 2 months...mainly on getting stuff in & out of the main database, I've basic coding skills (PIC ICs, using picbasic)).

My main stumbling block is how to get Access to pull in more than one item....i.e. I reckon with enough effort, I could probably lash up an invoice where a customer has bought one product line, but for the life of me, I can't work out how to get access to suss if a newly imported set of sales has a customer that's bought a couple of products.

perhaps this is what I should make my immediate goal - the most basic of invoices (not to email, just to get me into the flow of things)....
 
Well, for starters, you want invoices and line items to be two separate tables, linked via the Invoice ID or invoice number. (I lean toward an internal ID number just because I dislike using valid data as a PK.) That will, among other things, eliminate the quibble you had where the shipping cost is getting listed multiple times, as you can assign that at the invoice level rather than the line item level.

That means the very first thing you need to do is look into 'normalization'. In a nutshell, it means that everything in a table must be directly related in some way. A major sign that a table is de-normalized is that you're unnecessarily repeating data - in this case, the shipping charge. Most of the time (there can be exceptions), the repeating data should be a value linked to another table that breaks it out.

In an invoice system, you usually have (I'm keeping it simple), a customers table, invoice table, and line items table. You can have a ton of others, too - employees (to track who issued it or did any actions), a state list, a shipping rates table, a parts table, pricing history, etc. But the basic three would be customers (which contains data about each customer), invoices (containing only data that applies to the invoice as a whole, including the customer ID from the customers table), and line items (containing what got sold, the cost, the quantity, etc, as well as the invoice ID it belongs to).
 
Thanks for the great input.

yes, I realise that I will need more than one table (though I hadn't realised my quest for an invoice would force my hand!) - ironically, I'm using one table because at my level of experience when I started, I figured it was simpler to append to one table from my import text file (& then massage data within), than to start having to worry about appending to several tables & figuring out the best structure & joining them up.

being candid...I find the whole process of importing new data very unwieldly (almost certainly due to my noobage), here's what I'm having to do...

1. Delete a temporary table.
2. Import the new data...to be the just deleted temp table (this 'temp' table is a 'scratch table' if you like - it is only used at the point of data import to run queries against)
3. Run an unmatched query against the new data against the main sales database (alas, my import data also contains data that was already imported on previous imports - I have little control over this, so must use unmatched query to establish only those records that aren't already in the mains sales database)
4. Check for any oddities (missing data, erroneous data)
5. Append to my main sales table
6. Run a bit of VBA code to generate new sales invoice numbers in sequence (checking for those incoming new orders that are actually the same one customer who has bought several products & therefore has a few line entries on the import file)
7. I now have a hulking sale database!

Now I realise step 5 is the point when I can start spreading the incoming data out across several tables...but that flushes out a new problem for me (mainly how to tie all the tables together!!!)
 
Last edited:
Seriously, you want to research 'normalization'. In your case, I would recommend a Customers table, an Invoices table, a Line items table, and an Items table.

The data you provided in post 1 would break down as follows:

tblCustomers
CustomerID_PK
LastName
FirstName
(Address1)
(Address2)
(City)
(State)
(ZIP)
(ContactName)
(ContactPhone)

tblInvoices
InvoiceID_PK
OrderNumber *Note - you CAN use order number as your primary key if you want, but I try not to use any real data as a PK)
CustomerID_FK
ShippingCost
(InvoiceDate)
(ShippingDate)

tblItems
ItemID_PK
ItemName
ItemCost
(ItemNumber)
(ItemDescription)

tblLineItems
LineItemID_PK
InvoiceID_FK
ItemID_FK
Quantity


Notes:
  • Items in parenthesis are suggestions of commonly-used fields
  • PK means primary key
  • FK means foreign key
  • Foreign keys are linked to primary keys via the relationships menu (we can walk you through that if necessary)
This LOOKS more complicated, but trust me, once you get the layout done and have made a query or two, this kind of splitting of your data makes working with it much, MUCH easier in the long run.

Now as to length of time and cost...for a basic, bare-bones inventory system, someone can probably get something up and running within a week, definitely within two. It will be incredibly basic, however, and you can probably expect to pay anywhere from $50 to $100 an hour, although temp agencies can sometimes find someone willing to do it for less.

Unless you have a lot of special requirements, it's probably easier and cheaper to find pre-made solutions online.
 
Great info...a hearty thanks.

re cost ...I spontaneously combusted when I roughly costed it up based on your input (my wife isn't happy, the brief flash of flames made a terrible mess of our nylon carpet), when I see those kinds of £££s leaving my hermetically sealed wallet, I figure that I'm going to have to get my access learning cap on (I've already been watching normalization videos since your last post!)

on a slightly related note...one thing has been troubling me wrt creating an invoice....a customer's billing address size is random can have all this data...

Billing address line 1
Billing address line 2
Billing address line 3

Billing City
Billing Region
Billing Postcode
Billing Country.

...but the lines I've bolded above aren't necessarily always going to have data & be used (for example Brazilian addresses go on for about six weeks ...hence needed 3 address lines, whereas a UK address might only use one main address line eg "13 Acacia Terrace, St Albans, Hertfordshire" etc) ....how on earth do you format to avoid gaps in the address lines on an invoice ...i.e. for those customers that only have a simple basic address & haven't input any data for billing address line 2 & billing address line 3?!! Using my example, the formatting of an invoice is going to end up like this...

Alan Smith
13 Acacia Terrace


St Albans,
Hertfordshire
AL1 1AU
United Kingdom.



!!!!!! So I guess it will need some form of conditional formatting when producing the invoice, for example....

Billing address line 1
If Billing address line 2 = null, then Billing City

etc etc? (I know I'm some way off from having to worry about such detail, but an early heads up on how to approach this would be very welcome)
 
Last edited:
....how on earth do you format to avoid gaps in the address lines on an invoice ....

You're going to love learning to work with VBA. :-)

Edit: Seriously, though, while it can be done using IIF statements in queries, it's much simpler to use VBA for a function to generate a compact address regardless of how many fields are filled out.
 
To be quite honest, though - hit up google and look up invoicing systems online. There are a ton of pre-made ones that might handle everything you need. Some even come with free trials.
 
I have looked...& frankly (for access at least - & I do need an access solution, because I'm not just going to be using this data for emailing out sales invoices, but also to massage/coax data into a specific format that Quickbooks needs) & alas, they're all a bit basic.....I've not found one that can cater for data being imported en-masse - each solution I've seen, seems to assume that every sale order is going to be entered manually....how very Mork & Mindy mid 80s!
 
Last edited:
There is lot you can do to help yourself:

Going backwards:

=trim(fieldname) gets rid of empty Address Lines.

You could have more tables but to gey started:

Clients Table
Invoices Table
Invoices_Items Table.

On your temp input I have a flag updated. Set this to yes is the orders have already been processed. Alternatively you could set the Order No and Product key to unique.

Assuming you have created a Customer you can create an Invoice.

Take the update Shipping take First(Shipping Value)

The detail lines can be updated normally.

To create an Invoice you go from the Invoices_Items into Invoices. Put the items into the Detail and Invoice table infor into a Header.

If you can understand PicBasic you can go along way with this.

We are here to help.

Simon
 
Might depend on the specific requirements of the invoice, but offhand I don't see anything particularly difficult. Emailing the invoice is easy, as is marking the orders as done. Presumably you don't have to worry about new orders coming in mid-process. I'd be surprised if it took more than an hour or two. Cost obviously depends on the individual.

Edit: have you tried presenting each step as a problem here on the forum?

2 hours seems wildly optimistic to me, Paul.

good luck!


OP. Sign him up now!
 
The data you provided in post 1 would break down as follows:

tblCustomers
CustomerID_PK
LastName
FirstName
(Address1)
(Address2)
(City)
(State)
(ZIP)
(ContactName)
(ContactPhone)

Ok, so I did a bit of work last night...I exported my hulking table (tblSales) out of Access & into Excel,...in Excel I de-duplicated all the customers (so that there was only one Excel row per unique customer), once de-duplicated, I stripped out all the 'non customer centric' columns...& then re-imported the resulting Excel file back into Access, calling it tblCustomer (& just letting Access choose the PK). I then ran an 'update query' to update my big mutha tblSales database to add in the correct associated CustomerID to each transactional row.

Under database relationships, I then joined my tblSales & tblCustomers by CustomerID...so step 1 achieved (though I can't quite bring myself to delete all the original Customer centric info in the main tblSales yet!).

I found the whole process strangely therapeutic & went to bed engulfed in warm cozy feeling of enormous well being, knowing that I'm slowly crawling towards a state of relational database Nirvana ;) ...with the end goal being an 'oh so elusive' simple (yet arousing) sales invoice!

(I don't get out much)
 
Last edited:
Thanks for the early morning chuckle. Keep on trucking - you are heading in the right direction
 

Users who are viewing this thread

Back
Top Bottom