Multiple Invoices

[Noel2]

Here is my situation:

I have a work order system that I have created in Access '97. I have done well with the system the only problem now is that I need to start using the information for a little more than what I have been using it for. I have a form that has a record of each order that my technicians have been out to a location. I have a subform on this form that is in datasheet mode that records what parts they have purchased for that order. The thing that both keep in common is the order id for that order. The form is based on a table called orders and the form is based on a table called details. I need to see what I can do to create a query that will for each order, in one record, give me all of the information for that order. This will include:
ordrid,tech_nm,addrss,tm_strt,tm_stp,amt_hrs,invc1,amt1,invc2,amt2,invc3,amt3,invc4,amt4,.......,invcn,amtn,....,etc.
please let me know how I could create a query like this so that I can have all of the invoices for each order in one record.

Thank you,
Noel

 

[ideafixer]

If I am reading your question correctly, you need to create a query to join both tables on the order ID. This can be done by opening the database window then selecting the query tab. Click new and go through the wizard. It is very straight foreward.

 

[Noel2]

it is close... the only problem is that it creates a record for each one of the invoices. I just want one record for each work order. Then that record will include as many invoices that I have with the invoice number and the amount for each one.

Thank you,
Noel

 

[ideafixer]

You still only have the two tables? One containing work orders. This table has one record per order ID. The other containing invoices. This table has multiple records per order ID. You want to display the work order once then the invoices that are associated with it in a query? You want to have the work order information dispayed once then each invoice number an amount dispayed on the record?

I would recommend using a report to accomplish this. You can use your query to base the report on. Using the report wizard is an easy method to kick out a fairy decent report. The catch here is you must add a grouping level to get your desired results.

Group on the order ID then put your invoice details in the detail. This will give you one occurrence of the work order information on the report. In the detail section your multiple invoice records will be displayed for each order ID.

Good Luck!

 

[Noel2]

thanks... I really wasn't looking for a report though... the reason that I wanted a query is so that I could just take the information from the query as a source and then place it into an excel spreadsheet with only one line per work order. if there is another way to do this, I am all ears.

Thank you,
Noel

 

[ideafixer]

I am not aware of a simple way. I would have to write a program to accomplish this. You would loop though both tables to build another table containing one work order record then your invoice data in each field. It's not a very difficult program but if you have never done one before it would be a place to start. You know you can right click on reports and export them to excel? Unfortunately you still will not get the format you are looking for.

 

[RiverGuy]

You should just be able to use a crosstab query. Under queries, click New, then on the type, choose crosstab. Under rows, you would want your Order ID I suppose. For columns, the line item ID, or I guess you might be calling it "invoice id". And the cells would have whatever you want to calculate. That would be about as close as I can see doing it with just Access tools alone.

 

[ideafixer]

That may be possible. Play around with the cross tab query and see if it will suite your needs. I have only used cross tabs to group and sum data. Maybe this is why you wanted to put the data in Excel to begin with.

 

[RiverGuy]

Also of note is that you can export reports to Excel. Just as you can export other reporting systems (i.e. Crystal which has cross tab reporting) to Excel.

 

[Noel2]

not that great with that whole crosstab query thing... the only reason that I export this report down to excel is due to the fact that I need to send it in a format to our accounts receivable department. The person that is in accounts receivable is very specific as to having her own format for what she wants and will not budge from her ex-hell format. I would love to be able to work with the crosstab features. I would like to know if you know where I can find more information about the crosstab features. Please let me know if you know where I can get that, I would love to learn... I am pretty good with code. Would you happen to have a sample of code that I could use to work with to get this type of thing that I have going? Please let me know so that I can appease this lady in accounting.

Thank you,
Noel

 

[RiverGuy]

Crosstabbing isn't too bad if you use the wizard. You've got to specify 3 things...the rows, columns, and instersecting data.

Try the wizard and you will be bound to figure it out.

 

[Noel2]

Thank you... anyone want to grapple the code? Please let me know.

thank you,
Noel

 

[ideafixer]

The first thing you need to determine is how many fields are needed to capture the invoice information. If you have 20 invoices for a certain order record you need to know this. To find out how many fields you need, you should write a query. Join the Orders tables to the Details table by Order ID. Then group by the order ID and count the invoice numbers. I assume each invoice has a unique ID in the details table. Add the Order ID and Unique invoice ID from the tables. Use the group by to group order ID. Then change the total field on the query grid to count for your Unique invoice ID field. I created a query designing sample tables. The query is below:

SELECT Tbl_Orders.OrderID, Count(Tbl_Invoices.InvoiceNumber) AS CountOfInvoiceNumber
FROM Tbl_Invoices INNER JOIN Tbl_Orders ON Tbl_Invoices.OrderID = Tbl_Orders.OrderID
GROUP BY Tbl_Orders.OrderID
ORDER BY Count(Tbl_Invoices.InvoiceNumber) DESC;

 

[Noel2]

This looks like it is going to be my answer. I am going to give it a shot and let you know exactly what I find on this.

Thank you,
Noel