creating invoice

[waldorfedu]

i am a very small business owner. I have been using FileMaker pro but now
that i have Office 2007 i would like to change to Access. at present i am
manually copy/paste from FileMaker into Access - that's ok. Where i need help
is how to create invoice similar to FileMaker invoice from Table or, even
better, are there templates available (obviously I cannot find them) but then
i am new to all this - thanks.

 

[Ken Snell \(MVP\)]

Microsoft has published a number of templates for ACCESS 2007:
http://office.microsoft.com/en-us/templates/CT101426161033.aspx

Beyond that, for someone here to provide suggestions, we'd need to know a
lot more details about your tables, etc.

 

[waldorfedu]

Thank you for the information. I have uploaded a sample invoice at
http://members.cox.net/tei1/Access Example.pdf . The one thing I liked
about FileMaker (I assume it will also be available in Access) is the value
lists. For example, I have typed in under "Items Ordered" more than 30 items
and obviously I can click on appropriate one to fill appropriate place in
invoice. Similarly, under "Quantity" I can pull up whatever number is needed
and under "Price" whatever price; and the calculation Quantity x Price is
placed in "Total"; say there are 9 items, FileMaker adds up and "places"
total in Sub-Total - I manually input tax if appropriate, and S&H and again
calculation occurs automatically. Sorry to go on but this the best way I know
of indicating what I require.

 

[Ken Snell \(MVP\)]

You do these features in ACCESS by using the query that is the Row Source of
the combo box control and by using the query that is the Record Source of
the form. You will find that you have extensively more flexibility with
ACCESS in such ways than you did with FileMaker (I've worked with both,
starting with ACCESS, and was always frustrated with FileMaker as I tried to
find a way to do what was so easily done in ACCESS.)

In the combo box's RowSource query, you could use a query that provides you
with the list of parts and their costs:

SELECT PartsTable.PartNumber,
PartsTable.PartName, PartTable.PartCost
FROM PartsTable
ORDER BY PartsTable.PartNum;

You'd set the Column Count property of the combo box to 3, set the Bound
Column property to 1, and then you could get the cost of the selected part
number by reading the .Column(2) property of the combo box control and
writing it into the appropriate control that holds the cost for the part in
your invoice.

In the form's Record Source query, you'd use a calculated field that
calculates the Total Cost (Quantity times Cost) -- in this way, the form
will display the total cost without having to store the value in the table
because you can always recalculate it when you want in a query.

Hardest thing you'll find, I believe, is moving from the FileMaker paradigm
to the ACCESS (relational database) paradigm.
--

Ken Snell
<MS ACCESS MVP>

 

[waldorfedu]

Thank you for all the information, Yes, I obviously have a lot of work to do.
What I am trying to find out before I do this is stuff like the following. On
my present invoice (sample at
http://members.cox.net/tei1/Access Example.pdf ) I can, by clicking on
empty box, bring up list and by clicking on appropriate item on list the
empty box is filled. As you can see from the sample I have done this 9 times
and I can also do this for "Quantity" and "Price". Once all items are entered
I can print invoice - it is all very simple and straightforward and obviously
I am hoping it will be equally easy in Access. If I had to run a query for
each item to enter it would be very time consuming and I would keep to
FileMaker. I am a retired educator and my main time is researching and
writing lessons and that is what I enjoy but obviously I need to keep
records. I also offer free lessons so entering items easily is important. I
appreciate your help and will follow through on what you have said but I am
trying to find out that Access will do these things - thanks.

 

[Steve]

It hasn't been mentioned yet that the cornerstone of a successful Access
application is a table structure that is correctly designed. It is
imperative that your tables be normalized. I can't tell what exactly that
you are invoicing but a general set of tables for customers, products and
orders look like this:
TblCustomer
CustomerID
FirstName
MI
LastName
<<Ther customer contact fields>>

TblProduct
ProductID
ProductName
ProductPrice
<<Other product descriptive fields>>

TblOrder
OrderID
CustomerID
OrderDate

TblOrderDetail
OrderDetailID
OrderID
ProductID
Quantity
ProductPrice

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[waldorfedu]

Again thanks for the information. However, I am still at the decision making
time of whether to use Access instead of FileMaker and I need the following
answered before I can make that decision. On my present invoice (sample at
http://members.cox.net/tei1/Access Example.pdf ) I can, by clicking on
empty box, bring up list and by clicking on appropriate item on list the
empty box is filled. As you can see from the sample I have done this 9 times
and I can also do this for "Quantity" and "Price". Once all items are entered
I can print invoice - it is all very simple and straightforward and obviously
I am hoping it will be equally easy in Access. If I had to run a
query/whatever for each item to enter it would be very time consuming and I
would keep to FileMaker. I am a retired educator and my main time is
researching and writing lessons and that is what I enjoy but obviously I need
to keep records. I also offer free lessons so entering items easily is
important. I appreciate your help and will follow through on what you have
said if I do change from FileMaker to Access but I am trying to find out that
Access will do these things - thanks.

 

[John W. Vinson]

I can, by clicking on
empty box, bring up list and by clicking on appropriate item on list the
empty box is filled. As you can see from the sample I have done this 9 times
and I can also do this for "Quantity" and "Price". Once all items are entered
I can print invoice - it is all very simple and straightforward and obviously
I am hoping it will be equally easy in Access.

It will. Probably easier in fact.

If I had to run a
query/whatever for each item to enter it would be very time consuming and I
would keep to FileMaker.

The query gets run - but it gets run in the background, automatically (just as
an equivalent query gets run in Filemaker). You don't have to see it or do
anything with it; just open a Form (for onscreen viewing) or a Report (for
printing). Normally in fact you would have a form to enter and edit the data,
with a command button on the form which would print the invoice (an Access
Report based on a Query, but you never need to know that except when you're
designing it).

John W. Vinson [MVP]

 

[Ken Snell \(MVP\)]

I apologize for the delay for my reply -- work has kept me busy.

I see that John Vinson has replied to your most recent post. Post back if I
can provide additional information.

 

[waldorfedu]

Thanks - i guess I'll have to learn Access - thanks everyone