ISTech Support Forum

ISTech Support Forum
http://www.istechforum.com/YaBB.pl
Crystal Reports, ODBC & Access >> Access General Issues >> Sales Order Line Items
http://www.istechforum.com/YaBB.pl?num=1122476346

Message started by Dynamic on 07/27/05 at 06:59:06

Title: Sales Order Line Items
Post by Dynamic on 07/27/05 at 06:59:06

I am trying to write a report in Access using linked tables. The link seems to be working with the following exception. The report I am writing requires each of the line items on a sales order. I have linked to what I believe is the correct table, BKARINVL, but the only info I am getting does not give me part numbers and descriptions past line 1 and repeats them multiple times.

I have noticed two things. The field bkar.invl.key doesn't make it over to Access. The other suspicion involves the BKAR.INVL.CNTR field. Do I need to set up something special for this?

Thanks for your help!

Title: Re: Sales Order Line Items
Post by aricon on 07/27/05 at 15:54:44

You cannot see ANY of the "key" fields through ODBC.

If I remember correctly there is a line number field that you should be able to utilize to bring in the records. If that does not work - and it may not as I remember that my VB programmer had a TON of problems with this a few years ago creating some Access apps for a client, the way he got around it was to write some VBA code that found the part number field and compared it and brought in all records with that same part number and INV number (which is SO number in the table) - then I'm not sure what else may accomplish it.

Title: Re: Sales Order Line Items
Post by DetGalb on 10/05/07 at 10:31:57

This looks like a primary key problem.

When you link the table for the first time, Access asks for the primary key fields (for you to mark them by clicking on them). If you aren't accurate about this, when you look at the data in Access, you will think there are a lot of records that are duplicate.

For example, if you linked to a customer table, and chose state as the primary key, all the customers from Texas would appear to be identical records.

If you don't know the exact primary key field(s), you are better off not designating any in that initial linking to that table, then you don't get misleading results.

For that table, I think that the invoice number and one of the line number or counter fields would get you a true primary field, but choosing none is the safest way of connecting.

Hope that helps!