ISTech Support Forum

ISTech Support Forum
http://www.istechforum.com/YaBB.pl
Crystal Reports, ODBC & Access >> Access General Issues >> Linked Tables and relationships in Access (DBA)
http://www.istechforum.com/YaBB.pl?num=1177434018

Message started by nateanddba on 04/24/07 at 10:00:18

Title: Linked Tables and relationships in Access (DBA)
Post by nateanddba on 04/24/07 at 10:00:18

I am the resident hack here that likes better reports than what DBA innately offers. I have deveoped many excel query reports and recreated the 'Accounts Receivable' -> "A - Enter Customers" -> "Edit Existing Customer" GUI in Access. However, i am having extreme difficulty in understanding the relavance of some tables for creating a report with information form several tables: Company Name, State, Sales persons, Credit Hold, <- I think this all comes from BKARINVI. The other stuff i need is the individually sold items like in BKARINVL (PCODE, PPRICE, PQTY, PEXT, ASD. I can not figure out how to link the two tables. Any clues? I'm not set on using those tables, just getting the data so i can see a particular product's sales by state or customer and also looking at a sales person's accounts and their performance by month. I want sales only from last 2 or 3 years and i can certainly make it a query and return it to excel.

Here are my current queried fields, which don't work entirely:
BKAR_LASTSALE BKAR_SLSP_NUM_1 BKAR_SLSP_NUM_2 BKAR_CLASS BKAR_CREDIT_HLD BKAR_INVI_PCODE BKAR_INVI_PQTY BKAR_INVI_PPRCE BKAR_INVI_PEXT YEAR MONTH

Title: Re: Linked Tables and relationships in Access (DBA
Post by Tim Keating on 04/24/07 at 11:23:07

If you want historical sales data, BKARHINV contains sales order header info and BKARHIVL contains line item info. The screen shot below shows how I link these tables to customer file in Crystal Reports.

http://home.earthlink.net/~keatingt/DBA_SS_2.jpg

Title: Re: Linked Tables and relationships in Access (DBA
Post by nateanddba on 04/24/07 at 11:27:56

Tim,
Thank you for that quick and clear solution. Is there a whitepaper or something that explains what each of the tables are and what the fields are?
-Nathan-

Title: Re: Linked Tables and relationships in Access (DBA
Post by Kelloggs on 04/24/07 at 11:34:29

I dont understand what information you are trying to get, but I am going to take a wild guess:

A way to link it would be using tables: BKARCUST, BKARHINV, and BKARINVL

Example:

SELECT DISTINCT
BKARCUST.BKAR_CUSTNAME,
BKARHINV.BKAR_INV_NUM,
BKARHINV.BKAR_INV_SONUM,
BKARHIVL.BKAR_INVL_CNTR,
BKARHIVL.BKAR_INVL_PCODE,
BKARHIVL.BKAR_INVL_PQTY
FROM BKARCUST INNER JOIN (BKARHINV INNER JOIN BKARHIVL ON BKARHINV.BKAR_INV_NUM = BKARHIVL.BKAR_INVL_INVNM) ON BKARCUST.BKAR_CUSTCODE = BKARHINV.BKAR_INV_CUSCOD;

Depending on the size of your DB, I would recoment you to use a pass-through query, to select the columns and data that you need before doing the link.

Regards,

Kelloggs

Title: Re: Linked Tables and relationships in Access (DBA
Post by nateanddba on 04/24/07 at 11:50:35

Thanks Kelloggs for writing out the query for me. It's nice getting this warm welcome so quickly. I'll have to do some reading on your method. It sounds much more efficient than linking the whole table.

Title: Re: Linked Tables and relationships in Access (DBA
Post by Tim Keating on 04/24/07 at 12:21:10

Check here http://www.istechsupport.com/freedl.asp for a download of a data map - such as it is. DBA never made one available so it's been pieced together over the years.

If you can't find a field you need, you can browse the tables using Maintain Database in DBA or look at the source code.

It may be quickest to just post here because a lot of us know the commonly used fields off-hand.

Title: Re: Linked Tables and relationships in Access (DBA
Post by nateanddba on 04/24/07 at 13:02:18

Woo Hooo!!! Data Map! Yeah! This is sweet! You guys have made my day.
I am working on one oterh project. We need to create a different packing slip and Invoice for a certain customer. The heater needs to be different so it does not show our company name. Does anyone have experience changing or creating these?

Title: Re: Linked Tables and relationships in Access (DBA
Post by Kelloggs on 04/25/07 at 07:23:03

nateanddba wrote:

The heater needs to be different so it does not show our company name. Does anyone have experience changing or creating these?

If you want to remove the header, you can modify the:
T6SOC3.RTM (Packing Report)
IT6SOF4.RTM (Invoice Report)

Woudnt that be more easy?

Regards,

Kelloggs