Table Linking Problem

[Andyherm]

Hi all,
I'm totally new to access so be gentle
I've created the database that uses a macro to import data from a set of csv files into their relevant tables which works fine. The database holds ordering data for an online business with a t_order table which holds the main order information, addresses, product codes etc and also extra tables for detailed info on the individual products and options.
I want to link the product codes in each order to the product and option tables to pull through description information and pricing in a Crystal report but the product codes, quantity, option names etc for each order are stored in an array field called basket, example data below.
How can I link the individual parts of this array to fields in the relevant tables?

1|ST177|Set 3, Mixed set of 10 pins|||Pink
Data in the array is in this order.
Quantity|Prod Code|1st Option|2nd Option|Cost|Colour

I'm using Access 2003.
Many thanks.

 

[lespaul]

I guess I'm not understanding what the problem is....you have some csv files that you import to your database and that works fine.

Now you have all the information in your database tables and you want to use Crystal Reports to create a product report.

What does this "basket" array have to do with it?

Leslie

Essential for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[Andyherm]

The basket field contains the quantity and product code for each product ordered and where applicable, the name of any product related options that has been specified.
The problem is that the field doesn't always contain the cost which can be variable depending what options have been specified.
I'm trying to link the option name in the array to the option name field in the options table so I can link the option ID and option cost fields to the order table.

 

[lespaul]

but where is it coming from? you've already put all the information in the table....is this part of the information you have put in the table? Did you put it in the table like this?

What field doesn't always contain the cost?

Leslie

Essential for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[Andyherm]

ah sorry. The data comes as database exports from the ISP's mysql database, each table being exported as a .csv file which I've then imported into the access DB.
All the data imports into the DB in the same format as it was in the .csv so the array is how the order details are stored by the ISP and the structure of the array is Quantity|Prod Code|1st Option|2nd Option|Cost|Colour.
This would be repeated for each product ordered so for 2 products, 1st option would be the 3rd and 9th parts of the array.
When an option is listed in the array, this changes the price of the product and the relevant prices are stored in the options tables so I'm trying to find a way to link the option name in the basket array to the option name field in the options table.

 

[lespaul]

All the data imports into the DB in the same format as it was in the .csv

What you need to do instead is import the data in a normalized manner. There are several ways to do this. You can import as you get it into a temporary table and then use some queries/functions to extract the data in the normalized format.

What tables do you have and maybe I can help you get the structure of YOUR data set up correctly so that you can import this information and then be able to easily extract the information you need.

Leslie

 

[Andyherm]

Hi Leslie,
Sorry for the delayed response.
That would be great! Much appreciated.
Do you need me to send you sample tables or just list all the tables I'm working with?
As mentioned, this is the first time I've had anything to do with Access so let me know exactly what info you need and I'll get back to you.
Thanks!

 

[lespaul]

no just post the information here. I think for you the easiest thing is going to be to import the data into a temporary table and then write some queries to extract it.

tableName
field name (PK/FK)

now is the format above the WHOLE of the import file or is there additional information?

 

[Andyherm]

I'm using 6 tables, data samples as follows...
The data in the basket field is actually quantity|product code|option type|comment|cost|second option

t_order
-------
OrderID 7313
Totals 40.50|3.75|0.00|44.25
Email test.test2@btinternet.com
TelNo 1325318059
FaxNo
Address1 1 fictional road
Address2 testsville
Address3 testing
Postcode DF12 H33
FirstName jon
Surname smith
Basket 1|ST227|Set of 3|||Silver & Crystal|1|ST396|Ruby Red||27.25|Silver wire|
StatusID
OrderNo TS198-204129-8/11/2007
OrderDate 08/11/2007
MemberID 9969
DelAddress1 1 fictional road
DelAddress2 testsville
DelAddress3 testing
Delpostcode DF12 H33
Referee wedding ideas mag
Status
Second Option

t_product
---------
ProductID 159
NewID 0
SectionID 2
Sub_SectionID 2
ProductName Diamante Startburst Pins
ProductSummary
ProductCode ST227
Deleted 0
VAT 0
DeliveryID 0
ProductCost 13.25
Thumbnail
Image

t_optiontypes
-------------
OptionTypeID 2
OptionTypeName Set of 3

t_optiontypessecond
-------------------
idOptionTypesSecond 197
OptionTypeNameSecond Silver & Crystal

t_options
---------
OptionID 2
ProductID 36
OptionName 16
OptionPrice 4.65
Disabled 0

t_optionsSecond
---------------
idOptionsSecond 197
ProductID 367
OptionName 200
OptionPrice
Disabled 0

Let me know if you need any more info.

 

[Andyherm]

Hi Leslie,
Was this enough info?

 

[lespaul]

Hi Andy,
I apologize for not getting back to this! I would first suggest that you read the Fundamentals document linked above and then we can discuss a better table set up. You may also want to explore the Northwind sample database.

A more normalized structure for your database would be something like this:[tt]
tCustomer
CustomerID
Name
Address
email
(all other information that is about the CUSTOMER)

tProducts
ProductID
ProductName
ProductCode
(all other information that is about the PRODUCT - I may even consider the 'Options' as a product - would really depends on the specific situation)


tOrders
OrderID
CustomerID
InvoiceNumber
InvoiceTotal
ShippingCosts
VAT
(all other information about the Order)

tOrderDetails
DetailID
OrderID
LineID
ProductID
SalePrice
Taxable
[/tt]

If you can change the table structure you can then use an import schema to import to the correct location or you can use a series of SQL statements to extract the correct information after importing to a single table.

Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com