Are there any Access geniuses about??

I'm trying to build a waste transfer database at work and I can't get my head around how to autofill fields. I'll do my best to explain the problem...

I've got two tables, a waste producers and waste types table. Waste producers can have several waste types and the waste types can be relevant to several waste producers.

When we are entering information on a form (waste transfer notes), I would like to have a drop down box that we can select the waste producer from, I would then like a second drop down box for the waste type to show only the waste types for that waste producer (so we aren't scrolling through a list of every waste type)

Can anyone point me in the right direction? as I am well and truly stuck:nut:

What fields do each of the tables have? It sounds to me like you may need a third table

Is this for a company of any serious size?

If so I suggest using something a bit more substantial than access.

You could do an SQL query to populate the box eg:

populate the manufacturers from a table of them, then do something like

SELECT FROM WHERE =

This would populate the list for you.

You would need a table to combine which manufacturers create which waste types. The same could be done to allow you to say which types of waste a company will handle when taking it from you too.

You would need:

- Manufacturer table including an ID number

- Waste type table including an ID number

- A table linking these two, so which manufacturer ID creates which waste ID's

I have actually got quite a few tables but I was trying to make it a bit simpler to understand (I probably over simplified it)

The idea of the database is to have a main table for waste transfer notes that is populated by a user from a form. I want all the data to be accurate/consistant so I would like to use a number of drop down boxes that lookup information from other tables such as customers, customer sites, waste types, disposal site, driver etc etc. If I can get the drop down boxes to reduce the number of options after a selection in one of the previous boxes, it would cut down on time and mistakes.

I have a customer table with the usual info on. Name, Address, Site Address etc. I then have a seperate table of waste types that relates site names to specific waste types. Eg In-Vessel Composting can produce compost and leachate so there are two entries.

Therefore on the form when I select In-Vessel Composting from the drop down box, I want the following 'Waste Type' drop down box to only have Compost and Leachate in it.

Hopefully this makes a bit more sense...

Is this for a company of any serious size?

If so I suggest using something a bit more substantial than access.

We have about 20 drivers and do about 1500-2000 loads a month - there will be same number of entries on the database

I did debate an SQL database however I'd have to learn SQL before I could do that:P

I also thought that if I design and test the database in Access, I can upsize it at a later date to an SQL database

If you are doing 2000 records a month, I would get somebody to design it properly for you in an SQL system.

Access will not IMHO like you trying to do lots of work on your data a couple of years down the line.

Also you can use my SQL statement in access to populate the box.

A very simple and probably not optimal one is:

Customer Table, Waste Types and Customer/Waste link table.

You would have a unique ID number for customer table and waste types and then in the link table you want to store a list of customer ID vs waste ID

eg is your customer is number 1 and your two waste types 7 and 8

1 --> 7

1 --> 8

would be stored in the link table.

This would then fill the drop down box by taking the user input for the company name and relating it to the ID number (1) which can be done by the drop down list. Then return all the numbers (waste IDs) in the linker table. GO through these one at a time looking for the ID number in the waste type then in the drop down list you can store the ID (hidden index) and the name as a string.

This would then when selected store the number.

Unfortunately, the money isn't there to get someone in at the minute.

There is software out there that will do what we want, but it isn't a module system and we will end up paying a lot of money for modules that we won't use (and it was about £20k for a multi user package:eek:).

We also looked at getting at systems that links to handheld computers our drivers use (like the delivery company's use) therefore cutting out the paperwork and data entry but there is nothing on the market that is suitable at the minute and would be a huge sum of money getting something altered to our requirements.

As long as I can make a stable database that covers our requirements for the following 12-24 months I will be happy

Thanks for you help cheezemonkhai, you've given me a few good ideas:thumbup:

hmm, i would have said access is absolutley fine for your needs, what your suggesting seems pretty reasonable and easy to implament.

Also, for the handheld computer thing Ive done some stuff with text messaging in the past whereby you can get access to text, through a site that does messaging(over internet) to the end user and although ive never used it you can also text the other way, costs last time i looked were 10p per message. Getting the user to adopt a standard text format could be an issue and access would have problems sorting any deviations althoug it would be ok with a little thought.

Unfortunately, the money isn't there to get someone in at the minute.

What's your budget? SQL backend with a form front-end can be done to a decent standard by most hobbyists, you might find someone on here would knock something up for you for less than £100.

We also looked at getting at systems that links to handheld computers our drivers use (like the delivery company's use) therefore cutting out the paperwork and data entry but there is nothing on the market that is suitable at the minute and would be a huge sum of money getting something altered to our requirements.

If you had an SQL back-end you could then create web pages/web services to front that which would then be available to any device which has a browser and interweb connectivity - though whether this would be any use to your handheld devices would depend on what the devices were capable of...

There's a chap on here under the user name DaveU who runs a company providing IT/telecoms solutions to the logistics industry - not sure if it might be worth seeing if there's anything he could do to help meet your requirements?

Rob.

hmm, i would have said access is absolutley fine for your needs, what your suggesting seems pretty reasonable and easy to implament.

Access will be fine for 12-24 months if done correctly, however systems always run a lot longer than designed so a few years down the row things will start to crawl, especially if more than one person is accessing the data at the same time.

Far better to do an SQL backend, knock up a web interface that is mobile phone/PDA compatible and let the drivers update it from a modern phone.

You can then have a real web page to enter any that were missed and do all the admin for the office.

I'd say that's under £1k total.

Access will be fine for 12-24 months if done correctly, however systems always run a lot longer than designed so a few years down the row things will start to crawl, especially if more than one person is accessing the data at the same time.

If it's one person's database then Access is passable, if it has more than one user or is used over a network or needs to not lose data, SQL would be a much better bet. And given that both MySQL and MS SQL Express are both free, there are even fewer excuses...

I personally wouldn't rely on text messaging for sending update messages back to base - they're not guaranteed delivery and have latency which can run into days, so you'll either have well-behaved drivers getting pulled up because they sent a text and it wasn't received, or slacker drivers using it as an excuse for not sending back the updates...

Rob.

The drivers would need a purpose made machine as the paperwork has to comply with the duty of care regulations and we would have to supply a printer so tickets could be printed where required (sites without internet access).

Hmm:rubchin:. How much maintenance does an SQL database require and how easy is it to add additional information/tables in the future?

Maybe I should look at SQL stuff before designing my database on access. Any hints as to where I can learn the basics?

The drivers would need a purpose made machine as the paperwork has to comply with the duty of care regulations and we would have to supply a printer so tickets could be printed where required (sites without internet access).

Cheap laptop with wireless mobile and a printer, running off 12V supply in the van? :rubchin:

How much maintenance does an SQL database require and how easy is it to add additional information/tables in the future?

In theory, none - you don't have to do things like compress it and that, it just runs. So really, in terms of day-to-day maintenance it's just a matter of checking that your replication/backups are working so that you aren't vulnerable in the event of a server failure.

Maybe I should look at SQL stuff before designing my database on access. Any hints as to where I can learn the basics?

SQLCourse - Lesson 1: What is SQL? could be a good starter. Then from there you just need to figure out which version of SQL you want to go with and learn about that...

Rob.

Agree just get somebody in to give it a tidy up once a year, make sure you have your backups.

If you craft your pages correctly, the lorry driver could fill them in from an N95/iPhone on the move in the browser and all the data generated would be fine.

Laptop as rob said is the other option. Could even add a cheap thermal printer to do the confirmation receipts for the company

Don't forgot you need to purchase a SQL server license too... that will come it at a quite a few quid too....

In my last job we used to do a lot of Pocket PC/Palm sync stuff to backend SQL Server - normally using Sybase Mobilink as the sync engine. Wifi use when in the school building, and bluetooth/gprs when outside if wifi coverage.

We used to use b/t brother thermal printers when we did some work for the Dorset Police.

The problem with using SQL Server (Express or otherwise) or MySQL is that there is no nice IDE for front end development so you need to either use Access as a front end, or learn PHP or Java (or some other thing) in order to interface to MySQL.

I would do as you had originally planned - prototype in Access, then move to a different back end as and when you have refined your database structure, and are ready for bigger things.

To answer the original question, follow Mark's suggestion of a link table to link producers and types. Build a query that retrieves from that table using the content of the producer combo as the criteria. (the syntax is something like [forms]![formname]![comboname]). Then put the product type combo box on the form, and as the control source for the form, specify the query you just built. The only trouble is you may need an event procedure to blank it out and refresh it, otherwise if you go back and change producer, the contents of the product type combo will not change.

Have a look here: Access Topics: Tutorials - this covers the basics. There are lots of others if you Google for "acess forms tutorial".

Are there any Access geniuses about??

I'm trying to build a waste transfer database at work and I can't get my head around how to autofill fields. I'll do my best to explain the problem...

I've got two tables, a waste producers and waste types table. Waste producers can have several waste types and the waste types can be relevant to several waste producers.

When we are entering information on a form (waste transfer notes), I would like to have a drop down box that we can select the waste producer from, I would then like a second drop down box for the waste type to show only the waste types for that waste producer (so we aren't scrolling through a list of every waste type)

Can anyone point me in the right direction? as I am well and truly stuck:nut:

Looks like your after a lookup table

If you are doing 2000 records a month, I would get somebody to design it properly for you in an SQL system.

Access will not IMHO like you trying to do lots of work on your data a couple of years down the line.

Also you can use my SQL statement in access to populate the box.

Access is the most unreliable application in MS Office, IMHO. I wouldn't use it for any remotely heavyweight use!

The problem with using SQL Server (Express or otherwise) or MySQL is that there is no nice IDE for front end development so you need to either use Access as a front end, or learn PHP or Java (or some other thing) in order to interface to MySQL.

This is where Visual Studio Express Editions come in - design the DB structure using Management Studio, then integrating that into either a standalone Windows App or a Web Page/Web Service can be pretty much a case of dragging and dropping (hurrah for DataGrids), and you don't need to know enough of a programming language to write an app from scratch as the IDE is very helpful in this respect. And, best of all...totally free.

Usual disclaimer about ease-of-use being subjective applies.

Rob.

If you post up some screen shots or send me a PM...

I will do my best to fix it and explain it for you.

The problem with using SQL Server (Express or otherwise) or MySQL is that there is no nice IDE for front end development so you need to either use Access as a front end, or learn PHP or Java (or some other thing) in order to interface to MySQL.

I would do as you had originally planned - prototype in Access, then move to a different back end as and when you have refined your database structure, and are ready for bigger things.

To answer the original question, follow Mark's suggestion of a link table to link producers and types. Build a query that retrieves from that table using the content of the producer combo as the criteria. (the syntax is something like [forms]![formname]![comboname]). Then put the product type combo box on the form, and as the control source for the form, specify the query you just built. The only trouble is you may need an event procedure to blank it out and refresh it, otherwise if you go back and change producer, the contents of the product type combo will not change.

Have a look here: Access Topics: Tutorials - this covers the basics. There are lots of others if you Google for "acess forms tutorial".

Sorry for the lack of reply, a fairly self inflicted ban and other things to do all weekend meant that I've not bee on until now

MySQL CC will let you do maintainance and there are plenty of SQL design tools out there which will creat a design then do an SQL DUMP into the DBMS to create it.

I was busy scraping wallpaper paste at the new house so unfortunately I didn't get time to look at the database over the weekend:rolleyes:.

Having talked to the boss about it, we've decided to go ahead with the access database for now. It will give me experience of designing and developing a database and it will also allow us to have a working database while I learn more about SQL.

I'm actually quite happy as I enjoy a good challenge and i'll learn loads about access through trial and error. I expect there will be a lot of head scratching over the next week or so as I try to work out how to link tables and forms but I'm sure someone on here will be able to help:D. Thanks for all your help so far everyone:thumbup:

edit:It also means that I will have designed another important system for the company which should help me get another pay rise next year!

Since you're going for access I suggest spending a large amount of time up front designing and reviewing the design as once it's up and running it's a nightmare to try and maintain or change the DB structure.

Good luck, and if you get a bonus, do we get a consultancy share