Q re: Creating Bare-Bones Invoicing System

[Frothingslosh]

I just got assigned a project with a tight time limit. Basically, Marketing is unhappy with the invoicing tool they already use, and instead want me to create an Access app to generate and print invoices.

In a nutshell, they export a 42 column spreadsheet from the main project tracking system, add three custom columns, and save it as an xml file. This xml file is set up to work as an Excel spreadsheet, but is not formatted to allow an import into Access using Access' built-in import functionality.

In the spreadsheet, each line is full data for invoicing for a single individual and event. Each individual can have multiple events, but the individual's ID and personal data (names, phone number, address, email) is the same across each event.

What I'm thinking of is this:

First off, as someone is manually saving this thing anyway, I'm going to get them to start saving it as an actual Excel workbook rather than the XML file.

I'm thinking that the best way to handle this data is to just do a straight Transferspreadsheet of the whole thing into a temporary table in Access itself - the issue is breaking it up afterword. I know I could just import line items alone and use the single table, but 1)Duplicating data unnecessarily drives me insane, and 2) I want to really minimize the size of the database, because they're going to get an insanely huge number of these line items over a year or so.

Anyway.

Pulling the individual's ID, name, phone, address, and email is easy enough - just group them and I'm down to one line each. Then I can just append them to a permanent table, tblInvoices or something like that. Right now, there are actually no invoice numbers generated - they just list multiple event IDs - but for linking purposes, I'm thinking I'd best add an Invoice ID field, and probably a date imported field for use in a moment, as well as date invoiced and whatever fields I may need for tracking.

The other 20 or so fields I'm taking out to become the line items are where I'm a little iffy. I'm THINKING the way to go is to use a query to pull the fields I need, and include a calculated field with a function that pulls the invoice ID from the most recently added tblInvoice entry for that specific person. Then I can append that to a permanent table, and once that's done, delete the import table. (The db will be set to compact on close to avoid these deleted tables from getting out of hand.)

So basically, is that the way to go, or am I making this unnecessarily complex? I know I have a tendency to do that from time to time.

 

[CJ_London]

Keeping your db small can be done by linking to your excel table rather than using transfer spreadheet (Note: you can also link to an XML file). If you keep the name and location of the spreadsheet (or XML file) the same you won't have relink and use compact on close (a pain if you are trying to get out the door!) - just compact occasionally.

For example create a folder called 'import' and within that another called 'archive'. When your receive the excel file, lets says it is called April Sales, copy to the archive folder and also to the import folder and in the import folder rename it as Sales to overwrite/replace the previous file.

This can aslo me automated using VBA Dir and FileCopy functions.

Pulling the individual's ID, name, phone, address, and email is easy enough - just group them and I'm down to one line each.

What you can do instead of grouping is write an append query along the following lines

SELECT INTO YourCustTbl ID, name, phone, address
SELECT DISTINCT ID, name, phone, address
FROM YourLinkedExcelTbl ON YourCustTbl.ID=YourLinkedExcelTbl.ID
WHERE YourCustTbl.ID Is Null

This will only append new customers

Agree adding an Invoice ID field but ensure it links back to a suitable ID in your source so you can track back if required. Not quite sure what you are trying to achieve that an autonumber won't. It looks like your are saying you want a different invoice number range for each customer - if so, why?

 

[Frothingslosh]

Part of the issue is these aren't actually customers. The company calls it invoicing, but it's actually part of payment for certain events. Each person can (and does) run multiple events over time, and one invoice can cover as many as a half-dozen events. (In theory, it can cover any number, but in practice they generally only get in four max before a check is issued.) The term we actually use is 'provider'.

This system's main purpose is to simply generate and track these 'invoices', not the overall provider data (which is tracked in the system that exports all this). That's why I was looking at probably setting up just invoice and line item tables - I want to track the actual invoice that goes out the door, which means the address, phone number, and the like as they were at the time the invoice was generated, and I don't see any way other than storing them with each individual invoice.

It doesn't help that my deadline on this thing being FINISHED is Monday, and half of every day is already tied up with stuff I can't set aside, so that's part of the problem.