Hi there everyone, I have to do a bit of work in school and we have to build our own database. I need a bit of assistance as I've ran into bother.

I have to create an invoice based system. Here's a few screenshots of what I have at the minute.

Here's a list of the tables I have at the minute. Can you check if these are right? Do I need a key field for Line Items?

http://i.imgur.com/zzz8t.png

I have setup a report to try and get an invoice to work, but not sure how to group the items together. They appear as separate invoices if you get what I mean.

http://i.imgur.com/Uqo1O.png

Could anyone suggest any ideas? This is mickey mouse stuff for you guys!

Thanks

I don't know your data entry plan, but here's a thought:

You have the table InvoiceDetails with Invoice Number as primary key, Dustomer ID, and Invoice Paid?
Then you have the table LineItems with Invoice Number, Product ID, and Quantity.

Why not have one table with fields InvoiceNumber, CustomerID, ProductID, Quantity, and InvoicePaid?

Also, if you have characters like spacing and punctuation in your field names, it can make coding more difficult down the road.

Hi there Chris, thanks for the reply.

I started off to begin with with spaces and a load of reports/queries and it was a pain in the bum changing the reports/queries whenever I changed the table name, so I decided to just leave it.

I did try that however I assumed that if I wanted one invoice, I would have to have 'Line Items' which would allow the database to store multiple products per invoice. If I done all one, I don't think I can do that.

Thanks!

You can do it that way as long as invoice is not the pirmary key...if you want to use a primary key you can set it up as two or more fields, like invoice + costomer ID + Product ID

But if this works for you...that's ok.

As far as grouping it on your report....hard to say without more info on what you want it to look like, how you want it to group, etc.

Hi there Chris, thanks for your message again.

Thanks for the tips!

One question... on this report, I only want it to come up 'Quantity' once, like on a table. How much I carry this out? The Quantity would appear at the top with the numbers following.

http://i.imgur.com/eBsm1.png

Thanks a lot!

Jack

Open sorting and grouping, group on Quantity. Move the field [Quantity] into the Quantity Header. Place all other fields into Report Detail. If you want, you can put a summary for Quantity into the Quantity Footer as well.

Remove all other groupings you have.

Ok - and one more question Chris, thanks for being patient with me.

On a form where the customer details come up, is there a way for me to have a button to go directly to such an invoice without me putting in the criteria [Insert Customer ID] for Customer ID field and [Insert Invoice ID] for Invoice ID field?

Thanks

Jack

Hi Chris, for some reason I tried that grouping but it's still coming out like this:

http://i.imgur.com/uH86p.png
http://i.imgur.com/sBuwQ.png

Thanks

Jack

A button on a report? I don't know, never tried it. Reports are for creating a printable - for interactive viewing I normally use forms.

Your report is right - you need some fields in the report's Detail. Those fields will be unique for each Quantity.

Now that I think about it, are you sure you want to group on quantity? That's an unusual way to group a report of this type. Maybe try grouping on invoice or customer or even product...just a thought.

Hi Chris, got it thanks!

On the form, say an 'Area X Customers Form', perhaps a button that would say 'Generate Invoice' and it would bring up that invoice without me having to input those details such as Insert Customer ID & Insert Invoice ID?

Thanks

Jack

Yes, in the query for the report you could set the invoice number to reflect what is shown in the Invoice field on your form...and when you click the button that's what will be in the report.