Prev: Access auto numbers
Next: Appending table autonumber problem
From: gorsoft on 6 Nov 2009 20:19
On Nov 6, 12:43 pm, "BruceM via AccessMonster.com" <u54429(a)uwe> wrote:
> It sounds as if your term for line items is enquiry.  It would help to know a
> little more about the structure, but I will use part of the field list you
> showed in an earlier post as a starting point.  Work on a copy of the
> database, of course.
>
> invoice date
> invoice no
> customerID
> productID
> item description
> Quantity
> Unit price
>
> I will assume InvoiceDate, InvoiceNo, and CustomerID are header items, and
> the rest are line items, or enquiries.  I will asume further that the
> InvoiceDate is the same for all records with a particular InvoiceNo.  Same
> for CustomerID.  You could make a SELECT DISTINCT query of InvoiceDate,
> InvoiceNo, and CustomerID, or a totals query in which you group on those
> three fields.  In any case the query will include just the header fields, and
> will show only unique records.  Use the query to make a table containing the
> same information.  This could be a make-table query, or I think you can copy
> a query and paste it as a table.  There are other adaptations you could use,
> but in the end there will be only one record per InvoiceNo.
>
> Now make a copy of the original table.  Delete InvoiceDate and CustomerID
> fields.  Create a one-to-many relationship between CustomerID in the Header
> table created from the query and CustomerID in this new Details table.  Use
> the Header table as the basis for an Invoice form, and the Details table as
> the basis for a subform as described in the posting in which I sketched the
> table structure.
>
> This should leave you with Header (Invoice) information on the main form and
> related LineItem/Detail/Enquiry records on the subform.
>
> Whatever the details, the general idea is that repeated Header information
> will be consolidated into one table with no repetition, and Header fields
> deleted from a copy of the original table to leave you with the Detail
> information.
>
> As for printing a report, you could filter or group the records as needed,
> but I am not clear on how TransactionDate from your original posting of table
> structure fits into this.  I assume it is part of a Detail record, but I
> don't know if the TransactionDate month will always be the same as the
> InvoiceDate month.  You can make it do what you want either way, but it is a
> little more involved if the Header and Detail months are not the same in all
> cases.
>
> BTW, whenever I mention basing a form on a table you could (and probably
> should) use a query based on the table instead.  This will allow you to sort
> by InvoiceNunber, among other things.
>
>
>
>
>
> gors...(a)hotmail.com wrote:
> >> Are you saying there are invoices with several line items, and that there may
> >> be several such invoices per month for a client?  If so, are you further
> >[quoted text clipped - 54 lines]
>
> >> - Show quoted text -
>
> >Normally only 1 invoice per client per month - that invoice could have
> >one or more item lines (each one representing a unique enquiry with
> >its own characteristics).  I have a table of those enquiries - what I
> >am struggling with is how do I convert the enquiries into invoice item
> >lines and invoice headers without going through each client and
> >creating an invoice header and then selecting the item lines to add to
> >that invoice or worse still re-keying the item lines (enquiries).
> >Unless you can suddenly see what I am driving at (which cannot be easy
> >I know), I will adopt the approach I outlined in my previous post
> >above.
>
> >Gordon
>
> --
> Message posted viahttp://www.accessmonster.com- Hide quoted text -
>
> - Show quoted text -

Thanks Bruce, that's great. I can see through the fog now! Thanks for
all your help and your patience.

Gordon
From: BruceM via AccessMonster.com on 9 Nov 2009 07:10
This is where I learned a lot of what I know about Access. Glad to pass
along some of it in turn. Good luck with the project.

gorsoft(a)hotmail.com wrote:
>> It sounds as if your term for line items is enquiry.  It would help to know a
>> little more about the structure, but I will use part of the field list you
>[quoted text clipped - 71 lines]
>>
>> - Show quoted text -
>
>Thanks Bruce, that's great. I can see through the fog now! Thanks for
>all your help and your patience.
>
>Gordon

--
Message posted via http://www.accessmonster.com

First  |  Prev  | 
Pages: 1 2 3
Prev: Access auto numbers
Next: Appending table autonumber problem