[magenta]

Is anyone any good with MS Access? I'm attempting to do my IT coursework (A level) & really realy struggling, I haven't got a clue what I'm doing...

[Sidders]

I did A-level IT and I had to use access but I'm not overly good. I got an E. What do you want to know in any case?

[magenta]

I'm shite. I've stupidly left it all to the last minute when we did all our Access training kinda of stuff back last year - so I can't remember any of it.

I'm trying to import from Excel to Access, but I want certain columns in the Excel file to go into certain places in the tables of the database, rather than it all just going across, but I can't work out how it's possible.

I've also totally confused myself because for some reason in one of the tables I've set the primary key up as something which repeats itself basically, it's a nightmare.

I've got three tables - and in them originally is gonna be all of the information for company stock, but I also need the flexibility to include all of the same information, but for customer stock, someone suggested I have a unique identifier for what is customer & what is company stock, but I can't work that out either as it's all coming over from Excel and being imported in...

Confused? I am.

[Sidders]

No, I don't think I can help, sorry.

[Nablo.]

If I only paid attention in IT I would know, I remember being taught it but not how to do it

[Matt]

I'm trying to import from Excel to Access, but I want certain columns in the Excel file to go into certain places in the tables of the database, rather than it all just going across, but I can't work out how it's possible.

Open Access, File > Get External Data > Import

Files of Type > Microsoft Excel > Find your Excel file > Open

Show Worksheets > Click the worksheet you want to import > Next

Next again

In Existing Table > Pick table to import into > Next

Here's where you can pick which columns you want to import. In the preview box at the bottom, click on each column in turn and set the options at the top (such as skip this field). > Next

Set your primary key options > Next > Finish.

[magenta]

The thing is - for the purposes of my project I think I want to create a macro that will import - as the information needs to be imported every month - so can I pick the columns to be imported through using a macro or not?

I did see that you could do it through the menu options and maybe the best solution might be to do that & put in my user guide instructions for doing it or something...

[Gaspode_The_Wonder_Dog]

i made a database using access with a switchboard an everything as part of my degree an it did my head in. i still have no idea how i actually completed it. but good luck with that though....

[Sidders]

Blagging will get you everywhere.

[Morals]

Could you import the whole spreadsheet to a separate table to the one you normally use and then write an SQL query which pulled the relevant information across into the actual table you work from?

For example, if you had a table with three columns, Name, Sex and DOB, and you didn't care about the DOB, you could import all the data into a table (tblAllData) and then use:

Code: Select allSELECT tblAllData.Name, tblAllData.Sex;

(You could add a WHERE condition if you wanted to do more complex things).

If you wanted to add this data to an existing table you would use an append query:

Code: Select allINSERT INTO tblData
SELECT tblAllData.Name, tblAllData.Sex;

You'd also be able to split the records if you wanted between company and customer stock by doing something along the lines of:

Code: Select allINSERT INTO tblCustomerStockData
SELECT * FROM tblAllData
WHERE StockType = "Cust";

You may need to tweak it a bit but that should in theory append any data in the imported table where the stock type is set to "Cust" to a table called tblCustomerStockData.

Don't know if that would be useful as I don't know what data you're storing and how you've designed your tables etc.

[magenta]

That sounds like a good idea - and something that might get me a few more marks!

This is what I've got -

DEPOT (DepotNo (primary key), CustRef (foreign key), Location, Address1, Address2, Address3, Address4, Postcode)

CUSTOMER (CustRef (primary key), Customer)

VALUES (ValuesID (primary key), DepotNo (foreign key), SalesValue, StockValue, Date)

& it's one depot no has many values, and... I can't remember the other one & my drawing things of it are upstairs.

I've got another problem because the CustRef which I've set as the primary key actually repeats itself, & so does the customer - so I can't have that as it, & that's a foreign key in another table so I dunno what I can do with it...

Plus what I said before.. "I've got three tables - and in them originally is gonna be all of the information for company stock, but I also need the flexibility to include all of the same information, but for customer stock, someone suggested I have a unique identifier for what is customer & what is company stock, but I can't work that out either as it's all coming over from Excel and being imported in... "

This is doing my head in & it's got to be done in 3 weeks.

[Morals]

Not quite sure how your primary key can be repeating as it has to be unique and Access won't let you have duplicates. I'd need more info to help with that.

With the stock thing, how about having a stock table which has all the info you need (your values table by the looks of things) and add two extra fields (set as a boolean datatype (Yes / No)) - one called Customer and one called Company - then you don't need to duplicate information, you can check the relevant box / boxes and then use queries to select records based on which box / boxes are checked. Is that what you need?

If you want to send me more info send it all to me as an Email (mailto:morals@ticoc.com) and I'll take a look through it and give you some help with it.

[magenta]

I've just been planning it all on paper & putting things off & putting things off so far - so I tried setting it all up yesterday & realised that I couldn't have that as the primary key!

That's a good idea about the customer/company thing - the thing is, there's gonna be information transferred into the database every month, so does that mean I'm/my end user is gonna have to go through it checking all the relevant boxes basically?

I'll sort out all the info then send you an email in the morning if that's OK? Shattered, no point trying to do this now cos it'll frustrate me even more!

[Morals]

It depends on the data coming in from the spreadsheet - if the spreadsheet indicates whether the stock is cust / comp / both it can be done automatically, otherwise yes, the user would have to check the boxes.

Send over the stuff tomorrow, that's fine, and I'll try and fine an hour or so to go through it after work tomorrow.

[Sidders]

Since you seem to know about SQL, you might be able to help me with my Java assignment soon.

[Jonny Hoare]

Who actually uses Access to build databases????

[Adam]

name me another application that creates databases?

[Mcqueen_]

Who actually uses Access to build databases????

Me seen as its required to build a databse in Access for GCSE ICT.

[Morals]

name me another application that creates databases?

dBase, lotus approach, mySQL, Oracle....

[Morals]

Since you seem to know about SQL, you might be able to help me with my Java assignment soon.

Depends what the assignment is.

I see a new business venture coming...

[Sidders]

Basically it's just got to be a java application to communicate with a uni database system containing courses, students, staff etc...

Not really made a start yet, but I'll let you know if I get stuck.

[Morals]

I know naff all about Java, but SQL wise I may be able to help.

[Sidders]

That's what I mean. I've got to use Java to implement SQL commands. BTW, do you use C++ because Java is very similar.

[Morals]

No mate, sorry, haven't a clue when it comes to C++ or Java.

[Gaspode_The_Wonder_Dog]

we got flat screen monitors today. nothing to do with anything but there ya go....