BEST APPROACH RECOMMENDED 1

[saratoga4]

HELLO
What is the best access method to accomplish the following.

I would like to import from excel.. a new table "Daily Sale File" to bring in all of the recordes (one for each customer) that represent a sale from my website for that day.Each record will have an item name , item code and customer info.

The database will already have two tables :
-Item Master Table-with item id, weight , name and price
-Customer Sales Master Table -with customer information, the item sold, the weight of the item sold pulled from the item master

The process will be as follows:
-Import the 'Daily Sale Table"
-Load the Daily Sale table to the Customer Sales master table.
-Retrieve the weight of the item from the Item master table and populate this on the customer sale master

Generate reports from the customer master table
Generate an excel file from the customer master table
add a flag that notes the customer record was processed on the master sales table

Should i use queries or learn to code this in VBA

thank you
Cynthia


Tan Item master file with the code and the item weight and my cost price.

The datatbase will have a customer master file. This file will be loaded each day by moving data from the daily sale file

 

[fneily]

First some necessary reading:
Fundamentals of Relational Database Design

http://r937.com/relational.html

Your tables are not normalized. This will lead to massive duplicates (like you see in the excel spreadsheet) and query problems down the road.
You state :"the weight of the item sold pulled from the item master". In a relational database you don't have duplicate fields in two tables.
Your Customer Sales Master Table has "customer information" and "the item sold". What does customer information have to do with items sold? And vice versa? Nothing. You should have a tblCustomer with CustID and customer info fields.
Now one customer can buy many items and one item can be bought by many customers. This is a many-to-many relationship. So you have three tables:
tblItems
tblCustomer
and a junction table connecting the two- tblTransaction
with fields TransID, CustID, ItemID, DateSold and any other COMMON data.
Then through queries you can do your analysis.

My advice is to study normalization and set up your tables correctly.
Or you can just link to the spreadsheet from Access instead of going through all the importing. But again, your data won't be normalized.

 

[saratoga4]

Thanks for the red light.. i am not taking the planning steps first.. and the database needs to be as you say.. normalized . The article you recommended is excellent.
I appreciate your input.
Once i correctly establish the tables.. i have a spreadsheet of sales that can be imported each day. These new customers then need to be added to the tblCustomer . and the new sale transactions added to the tblTransaction.

Would you recommend VBA to add this new data from todays sales?

 

[fneily]

Here's a couple of different ways. See which one you would like, try it and then you'll have some code to post that can be looked at.

Import 1 spreadsheet into 2 tables in access
thread181-1419859

Using MS Query to get data from Excel
faq68-5829

VBA questions can be posted at:
Microsoft: Access Modules (VBA Coding)