please help me track transactions to a shifting list of people in an Access 2003 database?
July 24, 2008 9:08 PM Subscribe
How do I best store information about transactions with contacts and companies in an Access 2003 database?
I'm hunting down more resources for beginners, since the Access 2003 bible is fairly impenetrable, and it's difficult to understand what does what. I have a basic understanding of relationship databases and SQL and that's about it, no VBA, and no resources for outside training. I've made very simple Access databases and tinkered with results of the form/report wizards.
So I'm tasked with creating a database that tracks distribution of foo objects at the request of someone internal, to particular external partner. After my first attempt at the database, some of the requirements changed, and so I'm re-designing it, and not sure if I'm on the right track.
An external partner is either:
I've got:
1. where do I store address information? In the first db, I had a Contact table for just the external partners, no Company table and each person, even if they were at the same company, had the address info in there. So, a lot of address duplication.
I thought, oh, in my revised db, I'll just put the address info in the Company table... but not every external partner has a company, because some are just contractors.
So I could just kludge and use the name of the contractor (from the User table) as a record entry in the Company table for address purposes, but that doesn't seem quite right. Do I need a separate Address table, linked to either the Company or User table by a non-required key?
2. Often I find out on short notice that a given distribution going to User Kate at Company ACME should now be sent to User Jim at Company ACME - but that since they have the last set we sent, they don't actually need new foo things. How do I capture that, without losing my previous transactions to Kate at ACME, but just changing the contact to Jim for future transactions?
Currently (in the db I made and am using) I'm handling this by entering in an entirely new set of distributions to the new contact to record that Kate now has foo objects until the next set needs to be sent. That's definitely wasted effort. I'm thinking that by associating the transactions with my new Company table, instead of Users, I could then just change the contact associated with the entry in the Company table.
Any help is gratefully accepted, and apologies for the length.
I'm hunting down more resources for beginners, since the Access 2003 bible is fairly impenetrable, and it's difficult to understand what does what. I have a basic understanding of relationship databases and SQL and that's about it, no VBA, and no resources for outside training. I've made very simple Access databases and tinkered with results of the form/report wizards.
So I'm tasked with creating a database that tracks distribution of foo objects at the request of someone internal, to particular external partner. After my first attempt at the database, some of the requirements changed, and so I'm re-designing it, and not sure if I'm on the right track.
An external partner is either:
- a company - with a contact person, where the contact person can change often, but it remains the same distribution
- a contractor (who may or may not have a company name, but is just one person - unlike distributing to a company, if that person no longer gets the distribution, it's marked inactive and over with.
I've got:
- a Users table (with a field indicating the type of user - internal contacts, external contact people)
- a Foo table for the items being distributed
- a Company table for the companies
- a Distribution table to record the date and particulars of each transation at request of internal contact to external partner
1. where do I store address information? In the first db, I had a Contact table for just the external partners, no Company table and each person, even if they were at the same company, had the address info in there. So, a lot of address duplication.
I thought, oh, in my revised db, I'll just put the address info in the Company table... but not every external partner has a company, because some are just contractors.
So I could just kludge and use the name of the contractor (from the User table) as a record entry in the Company table for address purposes, but that doesn't seem quite right. Do I need a separate Address table, linked to either the Company or User table by a non-required key?
2. Often I find out on short notice that a given distribution going to User Kate at Company ACME should now be sent to User Jim at Company ACME - but that since they have the last set we sent, they don't actually need new foo things. How do I capture that, without losing my previous transactions to Kate at ACME, but just changing the contact to Jim for future transactions?
Currently (in the db I made and am using) I'm handling this by entering in an entirely new set of distributions to the new contact to record that Kate now has foo objects until the next set needs to be sent. That's definitely wasted effort. I'm thinking that by associating the transactions with my new Company table, instead of Users, I could then just change the contact associated with the entry in the Company table.
Any help is gratefully accepted, and apologies for the length.
Is access sacrosanct? It sounds like a pretty big database and may, ultimately, end up tied to other systems. This is exactly where access fails. It's meant as a personal db. You may want to look at a beefier RDBMS - there are options besides SQL server, postgres comes to mind
posted by askmehow at 11:22 AM on July 25, 2008
posted by askmehow at 11:22 AM on July 25, 2008
Response by poster: It's actually not a big db - distribution of stuff (docs actually) is not super frequent, but it has to be tracked. Ideally, yeah, it'd be tied to other systems, but I'm not a DBA, I'm just trying to track something.
seanyboy, thanks. I'm going to take some of this and run with it.
When you say the table Addresses should have a Company ID, shouldn't that be the other way around?
People have a Company which has an Address
People
fk_company_id
Company
fk_address_id
?
Would you mind if I emailed you some further questions?
posted by canine epigram at 1:27 PM on July 25, 2008
seanyboy, thanks. I'm going to take some of this and run with it.
When you say the table Addresses should have a Company ID, shouldn't that be the other way around?
People have a Company which has an Address
People
fk_company_id
Company
fk_address_id
?
Would you mind if I emailed you some further questions?
posted by canine epigram at 1:27 PM on July 25, 2008
No - the address has a company ID. This is usual for databases. Companies could and often do have multiple addresses. If there's a direct 1:1 address:company correlation, I'd just put the address info on the company record.
General rule is: If you have two tables, then the ID linking one table to the other goes on the child table.
I placed AddressID on the people record because you're sending stuff to a person who resides AT A SPECIFIC address. If a company had multiple addresses, you'd want to know which address to send the stuff to.
You could argue that a physical address could be shared by multiple companies, but traditionally, the company name is classed as the first line of the address and so this is unlikely.
If you're worried about it, pop the address fields onto the company table. It'll make things easier for you and it's not a huge problem if you end up with a small number of companies with more than one company record.
Feel free to mail me, but I'm a pretty busy guy and I don't know MS Access. You may well get a mail back saying I can't help you any further. I'll see what I can do.
posted by seanyboy at 1:35 AM on July 26, 2008
General rule is: If you have two tables, then the ID linking one table to the other goes on the child table.
I placed AddressID on the people record because you're sending stuff to a person who resides AT A SPECIFIC address. If a company had multiple addresses, you'd want to know which address to send the stuff to.
You could argue that a physical address could be shared by multiple companies, but traditionally, the company name is classed as the first line of the address and so this is unlikely.
If you're worried about it, pop the address fields onto the company table. It'll make things easier for you and it's not a huge problem if you end up with a small number of companies with more than one company record.
Feel free to mail me, but I'm a pretty busy guy and I don't know MS Access. You may well get a mail back saying I can't help you any further. I'll see what I can do.
posted by seanyboy at 1:35 AM on July 26, 2008
This thread is closed to new comments.
The rule of thumb here is to try and model your data before working out what to do with it.
Items are give to people by internal contacts; people belong to companies; companies have addresses.
Moving Items creates a transaction.
this gives us the tables...
Items
internalContacts
people
companies
addresses.
If you're mad on normalisation, you'd have an "item currently with person" table, but my gut reaction here is to denormalise this info back into the items table.
Looking at the links between the tables...
Items
- CurrentPeopleID
- LastInternalContactID
people
- AddressID
Addresses
- CompanyID
The transaction file should be used to control this data, and should contain ...
- Date & Time
- PeopleID
- AddressID (This is calculated, but kept for posterity)
- InternalContactID
- ItemID
- I'd also add text versions of the above IDs into this table for historical purposes.
Notes:
- Don't put internal Contacts (who give the stuff out) and external Contacts (who receive) the stuff into one table.
- Adding an external contractor should have its own screen, but the data should be pushed into the company, address and people files. If you think about it, a contractor is a person who works for a company that employs only one person.
I've hard wired this to only allow a person to be at one address. It's easy to think that they may flit between multiple company addresses, but modelling that will just make everything very complicated.
In the situation above, you should be able to say (A) gave object (B) to person (C) and have all the company & address stuff automatically filled in.
I've also probably missed some stuff. This is an area where I'd sit down with you for a while, think about things for a bit, have a couple of fights with coworkers about what the system should do, knock up some screens, rinse and repeat. It's very hard to get these things right first time in.
posted by seanyboy at 4:50 AM on July 25, 2008