Imported File (Not enough info)

[Emma35]

Hi All,
I'm trying to design a database which imports a .csv file every week......the info from this file will populate a table from which i intend to run queries to get totals and averages. The problem is that the .csv file doesn't have enough information to run the queries i've been asked to run.

The 3 headings from the file will be :

Description (Code which describes a product - eg:A1, B2 or C2 etc)
Date
Weight (Weight in tonnes)

I need to run queries which total the weight from the area of the factory from which the product has come (not included on the csv file) so i was wondering if it would be possible to auto-populate another field in the table which would tell me where the product has come from. Values are below

A1 - A Block
A2 - A Block
B1 - B Block
C1 - Site


C2 - C Block

Hope i've explained it ok,
Thanks a lot,
Em

[recyan]

Till some one comes along,
Don't think there is a need to auto-populate another field in the table.
You could have a different table with the data, something like :
Code | Area
A1 | A Block
A2 | A Block
B1 | B Block
C1 | Site
C2 | C Block

& then use this table in a join with your first imported table.

Thanks

[Emma35]

Thanks recyan......i do actually have another table with that information in it but when i link them together and run the query i'm getting no results at all. If i remove the link and just run the query with the main table then the results are perfect.
My tables are

tblMainRecords - ID(PK) |Description | Date | Weight
tblProducts - ID(FK) | ProductID | ProductDescription | Block

In tblProducts the field ProductDescription is the more commonly used name for the codename in the field Description in tblMainRecords, and the field Block is the location it came from.
The field ProductID is the codename for the product.
The PK and FK are both autonumbers

Thanks,
Em

[recyan]

I am a bit puzzled.
Am posting some data with a query & its results, just check out, if it gives you some guidelines :

Table1

TheCode	TheDate	TheQuantity
A1	3/1/2013	10
A1	3/2/2013	20
A2	3/3/2013	30
B1	3/4/2013	40
C1	3/5/2013	50
C2	3/6/2013	60

Table2

TheCodeAreaID	TheCode	TheArea
1	A1	A Block
2	A2	A Block
3	B1	B Block
4	C1	Site
5	C2	C Block

The Query Query1:

SELECT 
    Table1.TheCode, 
    Table1.TheDate, 
    Table1.TheQuantity, 
    Table2.TheCode, 
    Table2.TheArea
FROM 
    Table1 
    INNER JOIN 
    Table2 
    ON 
    Table1.TheCode = Table2.TheCode;

The Query Results :

Table1.TheCode	TheDate	TheQuantity	Table2.TheCode	TheArea
A1	3/1/2013	10	A1	A Block
A1	3/2/2013	20	A1	A Block
A2	3/3/2013	30	A2	A Block
B1	3/4/2013	40	B1	B Block
C1	3/5/2013	50	C1	Site
C2	3/6/2013	60	C2	C Block

Thanks

[Emma35]

Thanks again recyan. Just looking at my last post i realise i couldn't have made it more confusing if i'd wanted to. I've renamed some of my fields in the 2 tables to make it a bit clearer i think.

tblMainrecords
ID - Autonumber (PK)
Description - Text - C2,A2 etc
Weight - Number - Weight in tonnes
Barcode Date - Date - Date product was made

tblproducts
Description - Text - C2,A2 etc (PK)
ProductName - Text - Actual name of product (Apple, Orange etc)
Block - Text - Location where product came from
ID - Autonumber - (FK)

I have a query designed around these two tables...linked with an Inner Join. I'm trying to get a listing of all the product codes with their equivalent product names and locations displayed in the query results. Here's the SQL

SELECT tblMainRecords.Description, tblMainRecords.Weight, tblMainRecords.[Barcode Date], tblProducts.ProductName, tblProducts.Block
FROM tblMainRecords INNER JOIN tblProducts ON tblMainRecords.ID = tblProducts.ID
WHERE (((tblMainRecords.[Barcode Date]) Between [Forms]![frm_BetweenDates]![Date1] And [Forms]![frm_BetweenDates]![Date2]));


Thanks,
Em

[recyan]

There appears to be no relationship between
tblMainRecords.ID = tblProducts.ID

Try
SELECT
............
FROM
tblMainRecords
INNER JOIN
tblProducts
ON
tblMainRecords.Description = tblProducts.Description
WHERE
...............;

Thanks

[Emma35]

Hi recyan,
My apologies for the late reply....just back in work today. St Patricks weekend gets a bit crazy over here.
I reset the relationship as you suggested and it's working perfectly. Thanks very much for taking the time to help me...i really appreciate it !

Cheers,
Emma xx

[recyan]

Happy St Patricks Day to you and your family.
Glad you got things working.
Whenever you can spare some time, take a look at the links provided by orange in the below thread. I'm sure you'll find them useful later.
https://www.accessforums.net/databas...ted-22393.html

Thanks