If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > PC based Database Applications > Microsoft Access > Help with school database

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Nov 2012
Posts: 6
Help with school database

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
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Oct 2009
Posts: 202
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.
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Nov 2012
Posts: 6
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!
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Oct 2009
Posts: 202
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.
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Nov 2012
Posts: 6
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
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
Join Date: Oct 2009
Posts: 202
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.
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
Join Date: Nov 2012
Posts: 6
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
Reply With Quote
  #8 (permalink)  
Old
Registered User
 
Join Date: Nov 2012
Posts: 6
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
Reply With Quote
  #9 (permalink)  
Old
Registered User
 
Join Date: Oct 2009
Posts: 202
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.
Reply With Quote
  #10 (permalink)  
Old
Registered User
 
Join Date: Oct 2009
Posts: 202
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.
Reply With Quote
  #11 (permalink)  
Old
Registered User
 
Join Date: Nov 2012
Posts: 6
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
Reply With Quote
  #12 (permalink)  
Old
Registered User
 
Join Date: Oct 2009
Posts: 202
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.
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On