importing dynamic data from Excel Spreadsheet

[rvd48]

im doing a Access project which is based on a computer shop, the customer records, what they ordered, computer components and prices are stored in the database. the computer components and prices are imported from a table in Excel.

The major set back is for me is: that as you all know, computer components fluctuate in price and new products are always coming onto the market, hence its going to be very Tedious to keep on updating the access fields.

it is easier to update the component fields in Excel, is there some way of a having a dynamic link between my Access project and the Excel component sheet, hence when i update my excel sheets, my access sheets also update??

cheers.

 

[dcx693]

Link directly to the Excel file instead of importing it into your database. File > Get External Data > Link Tables.

 

[rvd48]

Link directly to the Excel file instead of importing it into your database. File > Get External Data > Link Tables.

thanks for the help gold ol'penguin.
its solved another problem i had which was that previously i could only get the price of a component by means of a drop down list. now, i can get the price in its own field.

 

[rvd48]

sadly, i carnt get the price of the component in another field.
it still appears in the drop down list in the form, when i imported the spreadsheets into Access, i created a table for each component which consist of two fields: 'component' and 'Price'

i combined all the components into one table called 'TblEverything' through 'Lookup's in 'design view', but when i select on any component in 'DataSheet view', i get the drop down list showing the various models i can choose from and also next to it is the price.

i am now thinking of adding the price for each Component through lookup's in design view. this is where my problems lie, if i select a component such as a hard drive and select Maxtor 120GB from the drop down list, how can i get the price field to show the 'Maxtor 120GB' price?

 

[boblarson]

I think you need to rethink your process. You say that it is more tedious to update the data from within Access than it is for an Excel spreadsheet? Are you currently entering the data into the spreadsheet?

If so, you should think about creating a form within Access that is in Datasheet mode that you can enter things like a spreadsheet, yet it would be stored in a table. Then you can join other related data (hence a relational database) from other tables via queries to display or update depending on what it is.

 

[rvd48]

I think you need to rethink your process. You say that it is more tedious to update the data from within Access than it is for an Excel spreadsheet? Are you currently entering the data into the spreadsheet?

If so, you should think about creating a form within Access that is in Datasheet mode that you can enter things like a spreadsheet, yet it would be stored in a table. Then you can join other related data (hence a relational database) from other tables via queries to display or update depending on what it is.

thanks for the reply bob.

at this point im not worried at whether the data is updated in Excel or Access, as long as the system works, im happy

i'll try and experiment with putting a spreadsheet within a form.

 

[boblarson]

I wouldn't try to put a spreadsheet in a form. What I was getting at, was to create a form with the fields you need to fill out, bind the controls to the table and then use THE FORM in DATASHEET View mode. It will look somewhat like a spreadsheet and so may make it more like you are used to.

 

[rvd48]

after experimenting with the subforms/reports in Form design its not exactly working.

i'll give you the rundown:

a customer order's a computer to be built, in Form view,a new record is made, the components are selected from the drop down list and the correct price appears in a field next to each component selected, then i would have a macro button to link to a report which would be designed into an invoice and which would be printed off. (the hardest bit im finding is getting the price of the component onto the form and therefore making the invoice)

this is what i originally wanted to do:

1) download the component price list from cclcomputer website.

2) in another Excel spreadsheet, copy and paste the components and prices from the CCL sheet onto separate sheets so that they can be imported into Access in the correct structure.

3) import into Access through ''File> Get External Data> Import'' hence 13-14 odd component tables such as 'floppy drives' , 'processers', 'DVD drives' etc... i.e. each table has a 'Component' field and a 'Price' field, also in each of these tables, the 'component' field will be the primary key.

5) create a 'CustomerDetails' table and a 'Payment' table, and a field called 'CustomerID' in each table which would act as the primary key.

4) create a table called 'Everything' which has its first field as 'CustomerID' and would act as the primary key. in design view, i would do look-up's which would link into all the 'component' tables. so in table view, i could select any model of processor or whatever by simply clicking on the drop-down icon, but the price of it is in the drop down list as well, there lies the problem, i want the price to be in a different field which is linked to the component, so any change in model i make in the drop-down lists mirrors the change in the price field.

 

[Parker]

As Bob has already stated, I think you need to re-think your process.

in another Excel spreadsheet, copy and paste the components and prices from the CCL sheet onto separate sheets so that they can be imported into Access in the correct structure.

1'st question why is this necessary?
You don't need to be doing this. Access is more than capable of sorting this out for you. And, anyway what is the point of creating separate component sheets only to combine them again in an Access table!

If you feel the need for this operation then at least create a new spreadsheet with ALL the components in the one sheet and price/comp data in separated cells.

While you could get Excel to do this for you automatically, I suspect that you are doing this manually but either way Access can do this much more efficiently and therefore quicker and easier and without you doing anything once you have set it up.

but the price of it is in the drop down list as well,

2nd Question: Why is this so?
If you have gone to the trouble of breaking up the original import then this data should be in separate fields in your spreadsheet.

Clarify please.

 

[rvd48]

Parker

2nd Question: Why is this so?

ok, i have uploaded some pictures of the system to show you what i mean more precisely.

ok, first link shows all the component tables: http://www.geocities.com/rvd48uk/1stpa1.jpg [**EDIT**: for some reason this page does not display if you click on the link, please copy and paste it into your browser]. 2nd link, i've show for an example the AMD processor table which has the field headings 'Component' and '+1'- i.e. price. http://www.geocities.com/rvd48uk/2ndpg2.jpg Each component table has these two field headings.[**EDIT88 seems like this page is not showing up as the 1st link, copy and paste works for me]



3rd table, shows the 'tblEverything' table from where i can decide what component each customer has. (the customerID field is a primary key and is unique for each customer)Each field is made from look up's in Design view selected from every component table. i've selected the drop down list for AMD processors in table View as an example to show you: http://www.geocities.com/rvd48uk/3rdpa3.jpg
as you can see, the price of the component is also in the drop down list also.

that is my problem, i need the price to be in a separate field, preferably i would like to place it next to the component field and when i change what e.g. the processor from one model to another, i would also like the price field to update to reflect the different price of the new processor selected,
is there a way to do this?

 

[Parker]

Can't view these as the site says I don't have permission. Post your db here and I'll take alook later

 

[rvd48]

edit. wrong button

 

[rvd48]

Can't view these as the site says I don't have permission. Post your db here and I'll take alook later

ok, i've uploaded it onto my mates FTP account.


http://www.hursttilingsolutions.co.uk/frontpage
at the bottom of the page, it says: download access database and when clicked on, a download box should appear.

thanks. btw it took under 2 minutes to download it on a NTL BB 600k connection.

 

[Parker]

Ok 3 things hit me straight away (I don't know if others have downloaded your db or not)

1. You need to re read the posts that have already been given too you
   
   If you are using Excel to download data then link those tables in Access DO NOT recreate the tables all you will achieve by doing this is making yourself and Access do more work than you need to.
   
2. Study the "orders" example in Access
   
   That will show you how to structure a db of this sort and give you a good starting point.
   
3. I hate to say it but..................... Start again
   
   The process is going to go something like this
   

1. Download your data from the web (you can even do this automatically)
2. Link your Excel tables with Access (you have already been told how to do this)
3. Create an order for a customer that allows you to list components and bill accordingly
4. Store the details of orders against customers in such a way that an order can be recalled to obtain a spec for any machine you have supplied
5. Bill your customers
6. recieve payments

By studying the "orders" example and, Possibly, The Northwind db example (although I don't think the latter is necessary in this case),you will see clearly how to achieve the last four of these steps.
The main difference is that you will have lots of different product tables. You could use a system of casscading combo boxes to control these and first pick the product (i.e. processor) and then pick the type, speed etc.

Your price will then come from a query

The only thing that you will need to do as well will be to create the download process and design a system that enables you periodically update, edit/modify,etc. the data downloaded.( I know that the price of some components can sometimes change by the hour ............ I used to build PC's)

Tackle this one step at a time

 

[rvd48]

Ok 3 things hit me straight away (I don't know if others have downloaded your db or not)

1. You need to re read the posts that have already been given too you
   
   If you are using Excel to download data then link those tables in Access DO NOT recreate the tables all you will achieve by doing this is making yourself and Access do more work than you need to.
   
2. Study the "orders" example in Access
   
   That will show you how to structure a db of this sort and give you a good starting point.
   
3. I hate to say it but..................... Start again
   
   The process is going to go something like this
   

1. Download your data from the web (you can even do this automatically)
2. Link your Excel tables with Access (you have already been told how to do this)
3. Create an order for a customer that allows you to list components and bill accordingly
4. Store the details of orders against customers in such a way that an order can be recalled to obtain a spec for any machine you have supplied
5. Bill your customers
6. recieve payments

By studying the "orders" example and, Possibly, The Northwind db example (although I don't think the latter is necessary in this case),you will see clearly how to achieve the last four of these steps.
The main difference is that you will have lots of different product tables. You could use a system of casscading combo boxes to control these and first pick the product (i.e. processor) and then pick the type, speed etc.

Your price will then come from a query

The only thing that you will need to do as well will be to create the download process and design a system that enables you periodically update, edit/modify,etc. the data downloaded.( I know that the price of some components can sometimes change by the hour ............ I used to build PC's)

Tackle this one step at a time

Thank you for your advice Parker; I will follow what you have said.
You say to link the tables from Excel, do you mean via “File> Get External Data> Import” or “File> Get External Data> Link Tables”?

 

[Parker]

Thank you for your advice Parker; I will follow what you have said.
You say to link the tables from Excel, do you mean via “File> Get External Data> Import” or “File> Get External Data> Link Tables”?

You're welcome

File> Get External Data> Link Tables

But think, in depth, about what your Access table structure needs to look like and what it needs to achieve before you overwork your rodent(oops sorry.... "Dictionary of fuzzy logic" --- "wear out your mouse by rapidly and randomly clicking ................")

Remember that one of the things about using an RDB like Access is that you only have to store any piece of data ONCE.

If your project stores anything more than once then you need to think carefully about the way your db is constructed.

(i.e.) in your case a processor is a processor. It may be that you use 50 different manufacturers of processors BUT it is still only one thing. E.g. you may consider having a table listing processor manufacturers and then listing the different models of processor linked to a table of architecture. (A processor designed for skt 7 will not fit a slot1) this architecture table can then be used again for other items (a motherboard with skt7 and simm slots will not take dims)

And yes I do know that skt7 sank with Noahs Arc it’s just an example!

If you are using Excel to import data then, Excel or Access is capable of sorting that data into a more usable format.

Like splitting up the data, updating the price of already stored items, appending tables with new, additional items and even, deleting obsolete stock items from existing tables. If you then need to archive deleted items you can have Access move them to a new table.

There are hundreds of possibilities and most of them better than what you are trying to do at the moment.

 

[rvd48]

thanks for all the help Parker.

i am modeling my database using the 'orders' template.

no doubt that as i progress with this databse, i will have more questions.

my first question is: i have a field called quantity, if i select a component such as e.g. ''Intel Celeron NetBurst 1.8Ghz Rtl (Skt 478)'', i would like the quantity field to say '1'. instead of being blank. hence the quantity field starts with '1' instead of being blank. i suspect i need to change the 'quantity''s field settings in table design view, but could you tell me what i should do?

lol, i sussed it, i put the 'defalt value' field as '1'.

 

[rvd48]

is there a tutorial for this 'orders' template in Access?

 

[rvd48]

The main difference is that you will have lots of different product tables.

for me to select a component from the drop down list, should i make a query which has all the component tables selected? hence, i could make a 'look up' in table design which would use the query.

 

[Parker]

Sorry it took so long to answer you but I had to go and do other things

lol, i sussed it, i put the 'defalt value' field as '1'.

Good: The old grey matter is starting to work.

is there a tutorial for this 'orders' template in Access?

Not that I know of ------ be your own tutor, Pull it apart, change things, study the code behind forms and controls etc. You can’t do any damage if you mess it up just delete your experiment and start with a fresh sample again.

Don’t be “afraid” to learn. If you want to know how to do something pull it apart until you understand what makes it tick.

One of my favourite learning tools for code used to be to create a macro, convert it to VBA (tools; Macro’s; Convert macro’s to vb) and see how MS write it in code.

There is nearly always a better way to write the code but it gives a good starting point. As you search the help files in VBA you will learn how to code for different things. Untill you write things without thinking about it like ----------- DoCmd.OpenForm "MyForm", acNormal, "", "", acEdit, acNormal----------and it no longer appears gobeldegook.

for me to select a component from the drop down list, should i make a query which has all the component tables selected? hence, i could make a 'look up' in table design which would use the query.

You could but you may end up with “Heavy” tables that will run slow. It depends on how many lookup operations.

Look at the “Cascading combo boxes” example in the code repository. It will give you an idea of one way to achieve what I think you need.

Think about the make up of a component and the way you need to use the information. This will give you an idea for an initial table structure for this problem.

Here is one possible

1. Component
2. Manufacturer
3. Architecture
4. Model
5. Purchase price
6. Your retail price

Now: If you make a table for each of the first four items and link them, store your prices in the table called Model then your cascading combo boxes could work thus:

In first combo box pick a component. Your next combo box will then list manufacturers of that component; pick manufacturer: your next combo box will give you archi........... And so on.

When you have picked a component then your prices can be displayed. But you need to think about this also ----- you may have to build some machines to a budget, in which case you may need to see all the information at once on a particular group of components.

When you need to update your component list then you, mostly, only have to update two tables, Model and Purchase Price, Occasionally you will have to add to the Architecture table. Your Retail price should be a calculation from the purchase price so you will never need to touch that.

You may like to include other stuff like a discount structure or an “Obsolete Component” Routine. That you must decide from your business model.

Another way you could do this is to use a combo box to pick components and then have a list box that displays the other information (a bit like a data sheet) then you have all the possible permutations displayed in front of you and you can pick from a list.

There are lots of other ways .

One of the good things about programs like Access is that they are very flexible and will let you do, more or less, what you want. You just have to figure out a way of achieving it.