New help setting up a tracking database Results

Hi. I've spent days trying to figure out how I can make my database work properly. I am trying to design a database that will keep track of a vending business. Here's what I've got:
Table - Accounts (this is basic information about a particular account)
Table - Machines (this is information on each machine, and has a one-to-many relationship with Accounts - [1 account has many machines])
Table - Sales (this is the table that keeps track of each collection transaction per machine, and has a one-to-many relationship with Machines - [1 machine has many sales transactions])

After having all my forms set up exactly as I wanted - everything working smoothly....a HUGE thought came to me and that was - "What happens when I need to physically move a machine?" Sometimes an account will have you remove one of their machines, and then that machine can be moved to another account. The way I have everything set up, the sales are tied to the machine. If I simply change the account on the Machine, all the sales transactions will carry over to the new account.
I need to somehow figure out how to tie the sales to both the machines AND the accounts. If I delete the machine and re-enter it for the new account, the sales information is still there, but I have no way of knowing which account it was financially tied to.

I hope I'm making sense in describing this. I've tried adding the accountName field to the Sales table. But it won't auto-populate. I'm so frustrated.....Any help would be greatly appreciated!!!!

Thank you!!

I've been working on this awhile and it's frustrating me to no end...I'm sure it's something simple that I'm overlooking, but I can't find it.

I'm working on a housing database that tracks developers through to unit resale. In a flowchart, the forms work like this: Developer--> Project --> Household --> etc.

At frmDeveloper, each project gets a Project ID that's the primary key that links all of the other tables (there are 9 in total). FrmProject has a subform, sbfrmUnits that lists all the units for a project/development. Each unit should have a household entered at frmHousehold...

This is where I run into problems; the database is not permitting me to have multiple households for a Project. So if Project X has 100 units, I enter those in the subform sbfrmUnits and that works fine.

When I try attaching households to each unit, I end up overwriting previous units for a project, leaving me with one unit/one project in my table. If I go to add a new unit, it overwrites the existing data.

I've tried setting the data entry property to 'Yes' but creates other problems. Any help you can give me would be tremendously helpful.

To describe the situation better:
I have 9 tables corresponding to 9 different forms - one's a subform
I carry ProjectID (which is autonumbered in the first table: tbldeveloper) as the relationship link to the other 8 tables.
I am experienced in Access and with VB; I'm sure this is something stupid that I'm missing, I just can't find it.


I am very new to access, and don’t have any kind of training other than this forum and googling various things, I think I’m having a hard time wrapping my head around tables and relationships, how to set them up, and how to put into and pull information from them correctly.

I am making a database for work (manufacturing), Its tracking orders, going through our quality department, how long inspectors are working on them, and how many, if any; Non-conformances (NCs) they find.

My main problem at the moment is trying to find a way to relate the NCs to the orders. Since there can be multiple NCs Per order, I can’t think of a way to setup a Foreign Key. So I have a relation setup between the two using the SWO# but with no Primary key on my NC Table.

Can you do this? Or will it give me problems in the future? What would be the right way to relate the two tables?

I attached an screenshot of my database table relationships.
Any help would be appreciated,

I am a buyer in a small purchasing department and in the process of creating a database to use to track RFQ's (Requests for Quote) that we send to our suppliers. Describing the process; When we need to get a quote for an item or items we need to purchase we create a RFQ that has information like RFQ Number, Date, Buyer Name, Due Date, Originator, Project Name. Each RFQ will go to multiple suppliers and it will also have multiple lines that allows us to enter multiple items. When we get the RFQ's back from our supplier we enter the cost from each supplier for each item that was on the RFQ. I am looking for help in setting up the relationships.

So for I have the following tables:




I know I need to add fields to some of these tables so I can relate them but I am stuck given I have muliple Suppliers and multiple Items for each RFQ.

Any help would be apprecaited. I am new to Access so pardon my inexperience!


I am having trouble setting up my tables. I was given the general appearance of how the form should look and am now trying to work backwards to come up with the tables.

General Background -

When the company I work for brings on a new mutual fund group there are 184 tasks that must be completed. Each fund group is assigned a number. I need a way to track which tasks have been completed and when and which still need to be done. I want to be able to see all 184 tasks on a page at once with the variety of completed dates. Now there will be mutiple fund groups in the database and each one of these have the same 184 tasks but with different completed date etc. So visible in my form I want one fund group with is 184 tasks below it. And when you scroll to the next record you get the 2nd fund group with its 184 tasks.

I cannot figure out how to have all the tasks display on one page and be for one fund group number.

I am not sure if I am explaining this well enough. But any questions or help would be great. I have been staring at my computer for the past 2 days trying to set these tables up. Thanks!

I know, I know...chemistry. *yawn*. But, can you help a chemist out?

Here's the situation:

I work in a chemistry lab in which I routinely make up drug standards and reagents that are diluted, mixed, combined, and used in various procedures.
I have been tasked with making an Access database in which we will be able to better track/inventory/order/use/locate our various standards and reagents.
More than just locating items and counting units, I really want to be able to use this db to do some powerful calculations down the road. So, I'm really trying to get it set up right from the start.

Here's my problem:

For simplicity's sake, let's say I have two types of entities I need to track in the database: Chemicals and Reagents

Chemicals: a substance that is made up of one component.
(examples: sodium chloride 99%, aspirin 100%, or ethanol 100%)

Reagents: a substance that is composed of more than one component AND/OR (this is critical) reagent
(examples:[25% aspirin in ethanol], or [25% asprin, 75% tylenol, in ethanol])

Now, if all Reagents were always composed of just Chemicals, the table relationship would be pretty easy to set up as a one-to-many.
However, it does happen in practice that Reagents may not only contain individual Chemicals, but also previously made Reagents. They may contain just more Chemicals, but they could also combine any number of previously made reagents, and any number of novel Chemicals. Or, a reagent may be composed of only two or more previously prepared Reagents, with no Chemicals.

When I went to set up the tables and the associated data entry forms, I found that I was having difficulty with making a new Reagent with previously made Reagents.
Specifically, it wasn't a big deal to have each Reagent have a subform data entry that allowed me to populate as many Chemicals as I desired within it.

However, when it came to designing the form so that a Reagent could have a subform that pointed to another Reagent as a component of it, I ran into trouble.

Isn't it self-referential to have the Reagents table pull data for a drop-down from that same Reagents table?

Another option that was suggested was to create a third type of entity, some sort of "super-reagent" category, in which I would be able to add both reagents and chemicals in separate drop down fields. However, intuitively (which may be misguided), this strikes me as not elegant and imposes a structural limitation on the number of times a reagent may be used to create a new reagent.

Hopefully this makes sense!

Any ideas? Query? Totally different table setup? 50mg of morphine IV every 4-6 hours?

Many thanks.

I'm unsure if the thread located here (you'll have to modify the web addy as I'm a new user):
contains a similar problem? Still can't quite get my mind around it.

Hello I am relatively new to programing MS Access Databases and I designing a very ambitious (for me at least) new database for the startup company I work for. We manufacture specialty chemicals for research and the database I am designing is supposed to track a customer’s quotes and orders. So far I have been able to set up the basics and have the individual product and quote work correctly, however I have hit a snag.

I want to be able to have a shipping address and a billing address for each customer, however I also want the ability for the same customer to have a different shipping address for a different order.

I have the billing address as part of the customer info table and a separate table for shipping info which is a child of the quote/order table; however I don’t know how to specify that only one customer will have the same shipping info. How do I specify that the shipping address is exclusive to the customer and the table will let me select a shipping address from the customers list of addresses?

Any help is appreciated.

hi all
i have a table on my database from which i generate a monthly report by running a query.
when i run the query (date from to date to) i have no problem. it shows me all the relevant records.
when i want to generate a report, i input the start date and the closing date for the report.
it then gives me an error (reserved error (-1524); there is no message for this error.)
i have gone into the table and basically isolated all the corrupted entries.
there are 359

what i need to know is how to repair these entries without losing the information.
im new to access and this is a simple(kinda) database which i set up to keep track of what the staff in my department are doing on a daily basis.

im running access 2007.

if anyone can help me it will be greatly appreciated


I am laying out the specs for a new DB for our company that will track applications that our offices receive. I am at a point in the table layout that I need an outside opinion. I hope this is clear and concise:

Our company has 4 branches that handle different types of insurance. 90% of our applications will be processed by only one branch. The other 10% of applications will need several types of insurance and be split between any combination of the remaining three branches.

For an application that is split, any one branch wants the ability to see on one form what the other branches are doing with it via an [Action] or [Status] field. I have attached two versions of the database structure. My gut tells me that Version 1 is the way to go, but I just wanted to check with the experts. I also want to make sure that I have the multi-field keys set up correctly. I know there is also debate on how one’s keys should be set up. Thanks for your help.

I’m a complete Access beginner with some rudimentary questions. I have done quite a bit of reading online and some basic tutorials, and am a bit overwhelmed with information. Any pointers to set me off in the right direction would be much appreciated.

What I aim to achieve with Access is, I think, relatively complicated. I understand that concept design is key to creating an effective DB, so I would like to outline my status and goals in the hope of receiving some ideas that will help me avoid obvious mistakes at this early stage.

My company uses Excel to collect employee data from international subsidiaries. This is mostly qualitative data such as name, work location, job title, language ability, training programs attended, future potential, etc. There is also a little quantitative data, like # of subordinates, years of service. In total there are/will be around 35 fields. We get this from each company separately as we do not have an international intranet or shared server.

My impression is that Access will be difficult to use via e-mail due to the likely file size. So I would like to find a way to consolidate all these Excel tables into Access to enable better searching/filtering/reporting, etc. The data in the Excel sheets would be updated 1/yr, and the new data will need to be synchronized into Access.

Data will be used for tracking purposes. For example, to generate reports on things such as employee responsibilities (e.g. # subordinates; P/L accountability), employee development (i.e. promotion through the ranks), turnover (e.g. who left the company, when and why), employee performance and future potential, etc.

1. The data we want going forward is a bit different to what we have collected up to now. I guess we should not import any current data, and should instead design new Excel tables and the Access DB based around what we want for the future. Correct?

2. What problems might I encounter with importing data from multiple Excel files? Would there be additional issues if I want to break the data from one Excel sheet into several different tables in Access? (e.g. have personal details in one table, language skills in another, etc.)

3. The big one: Given the rough outline above, what might be the best way to approach the design/structure of the Access DB?
Any other tips would also be appreciated.

Many thanks.


Many thanks in advance for any help you are able to give.

I am creating a database for work. I work in service in the NHS. The purpose of the database is to track patients' history in the service, and to be able to see their current status. In the service, patients can have group treatments, individual treatments, and can be on a waiting list for a treatment. patients may have been on many different waiting lists for different treatments, and have had many different group and individual treatments. The service changes, so there needs to be potential to expand - e.g. we might offer a new group treatment at a later stage.

So, I have a patient details table:
patientID(PK) - autonumber
address, etc

a group treatments table
groupID (PK) - number (not an autonumber, as each group has a specific number which I want to be able to select, e.g. group 8 was a CBT group)
type of group (combo box: triple, CBT, etc)
start date
end date, etc

an individual treatments table
individualID (PK-autonumber)
type of treatment (combobox: 1-2-1 CBT, 1-2-1 GET, etc)

a waiting list table
waitinglistID (PK - autonumber)
waiting for

I also have some junction tables:
groupeventID (PK)

start date
end date

date on waiting list
date off waiting list
status, etc

I have a main form (frm_patientdetails) on which there are 3 subforms (sf_jun_groupevent, sf_jun_individualevent, sf_jun-waitinglistevent). The subforms are based on queries that join together, for example, tbl_waitinglist with jun_waitinglist). It does this by selecting all the keys on the junction table, and all the keys on the one-side table (e.g. tbl_waitinglist) except the primary key.

I want it so that users can select an event on the subform which then updates the junction table, but not the one-side table (e.g. tbl_waitinglist).

I have set this up on the groupevent subform (sf_jun_groupevent), and now you can select a group from a combobox, and this populates the jun_groupevent table. However, when I tried to set this up on waiting list event subform (sf_jun_waitinglist), everytime I add a new waiting list event on the subform, it also adds it to the one-side table (e.g. tbl_waitinglists: forgive my terminology). so, for example, if i want to put patient A on the waiting list for CBT, I select CBT from the subform, which is fine, but now there is an extra record for CBT in the tbl_waitinglist.

This does not happen on the tbl_group - which probably means it is something to do with the differences between the two tables.

There are two main differences between tbl_group and tbl_waitinglist.
(1) The PK of tbl_group is a number not an autonumber, unlike tbl_waitinglist.

(2) tbl_group has a type of group field which is a combobox based on a value list. tbl_waitinglist has a 'waiting for' field which is a textbox. I did this because, as I said before, the service may add a new treatment, and therefore a new waiting list, at a later stage. It occured to me that it may be useful to have an 'administrator tools' switchboard, where people can go to, for example, a form based on tbl_waitinglist, and add a new record for 'waiting for' field when a new treatment becomes available. Of course, it would also be a good idea to do this for group and individual treatments, but this only occurred to me after I had done group treatments.

Am I missing something fundamental about database design (probably), am I thick (likely), can you help? I am pulling my hair out here. Does anybody know what I am doing wrong?



I've come to the conclusion that I can't get this one on my own so I'll try to explain what I'm hoping to do.

I'm working on our student issues tracking database for the financial aid department which oversees more than 20 campuses. If a student has a problem preventing them from getting FA then we track the issue and the steps taken to resolve it. Many documents and communiques regarding the student issue have to be logged.

My main form uses the following fields:

SSID LastName FirstName Campus. Of course I've set the SSID to NO DUPLICATES.

Subforms on tab controls take care of categorizing details which are related to SSID

But what is happening is that student ex. Aaron Adams, which there is only 1, is being entered multiple times because of SSID typos or misreading a handwritten document or SSID numbers getting reversed.

Campus Liaisons and FA Packet processors first search the database by SSID, but having miskeyed the SSID they decide to add the student which they think is a NEW one, but it's really the same Aaron Adams. They could look up the student in the MASTER DATABASE, but they don't always do what they are told. I don't have permission to relate my tracking database to the MASTER DATABASE for reasons beyond the scope of this forum.

What happens is this:
123-456-789 Adams, Aaron (Campus)26 AND
123-465-789 Adams, Aaron (Campus)26

There could really be 2 Aaron Adams on the same campus but with SSIDs such as in the example it's more likely that the SSID was miskeyed and one of these two is a mistake.

Here's what I want to do:
When a new student is added to a campus
Check for duplication by LastName, Firstname on that campus (I can get this far by query) Display the possible duplications found including the SSID Ask if any of the possible duplicates found may be student they are attempting to re-enter IF YES then there should be a button to click for the recognized record and go to that record in the main form IF NO then the data entered should populate the main form as a NEW student. I'm using a form with unbound controls to gather the data.
From there I open a query of the StudentData table with a button
docmd.openquery "testqryStudentData"

I've attempted to
but I can't seem to get the syntax right pulling the LastName, FirstName and CampusNum form the unbound control form "testfrmStudentAdd"
(I've used Sendkeys to simulate this until I can figure out "applyfilter" )

I may be going about this all wrong. Can anyone help or make some suggestions

Thanks in advance!


Hey, I'm not incredibly new to Access, but I do need some help on something that may be simple to you guys. I'm updating/improving a database that a Post Office is using to track packages across a college campus, and they want me to build a button that they can click to automatically send an email to the student to let them know, "Hey, your package arrived. Please print this email and bring it up to the counter to pick up your package." Simple enough, just a macro button for SendObject, have the student's email be in the "To" entry, and set it to not open up for editing. Just auto-send. Right, so this is the problem:
The Post Office has to keep records of upwards of 20,000 entries, with roughly 2000 students and 1000 faculty each fiscal year. The email needs to be a field that auto-completes itself as other information is filled in for the student. Right now, this is the format:, which looks like this in the field format:
[First Name] & "." & [Last Name] & "???" & "@" & "???"
So, I'm not sure how to fill in those question marks. I know what needs to go in there, but not how to format the field to pull the information needed.
The student's ID number is a six-digit figure. The three digits following the name of the student are derived from a combination of the student's current year and the last two digits of the student's ID number. So, for instance, freshman are a 0, sophomores are a 1, juniors are a 2, and seniors are a 3. The last two digits of the number in the email are the same as the last two digits of the student ID number. So, for Mary Sue's example in the above, her status is a senior (inferred by the 3) and the last two digits of her ID number are 59. Make sense?
So, if I have a drop-down field for selecting whether the student is a freshman, sophomore, junior, or a senior, I need the email field to automatically fill in the appropriately related number (the first digit of the three digits for the email). Then if I set up an ID number field, I need the email field to automatically take the last two digits of the ID number. Finally, the second set of question marks needs to denote whether the person is a student or faculty member.

Please, any advice would be useful. This project is huge. Thank you!

Ok. I work in a welding shop and we are in the process of creating a database that allows us to keep track of our inventory coming in and going out.
Each piece of new material we bring in is assigned a Trace Code which is simply three letter. First piece brought in is AAA. Next piece AAB and so on...When you get to AAZ the next one becomes ABA. Ultimately when you get to AZZ your next series is now BAA. Is there a way for me to set up the form to automatically do that?
I have already set up the form and table and they are named Inventory Receiving List. The field for this data is named Trace Code. Any help anyone could offer would be greatly appreciated.
Please be mindful in answering that I really am not very strong with formulas and modules.
Many Many Thanks in advance!!

Hi.. I am new to databases and am trying to set up something for my rental property buisness. I have a building Table, that keeps track of the apt. address and such; and I have a tenants table that has info like phone number, emergency contacts and the current balance. I want to create a from that will log monetary transactions. I've created another table that has The fields transaction number (a running count) tenant name, address, date (auto filled) ammount paid, and balance. I would like to have a drop-down list of all the apt. address, and then use that record to automatically fill in the name and address parts, and then do some math with the numbers in the amount paid field. You know, subtract a month's rent on the first, add the rent when it comes in. and each record gets added to the trasaction table for future use.

I have the tables set up but I can't seem to get any help configuring the form items.

Any help would be great


I am building (re-building) a database for keeping track of a group where each member/contact can have varying roles and where some of the members are placed into "teams" led by various other members. I'm very happy with what I've built so far, but I am needing help in the next step.
I have:
tblContact - all contact info for everyone
tblPosition - the positions or roles each person may be in
tblTeams - where all information about the various teams I create is stored
frmContactInfo - for inputing each new member to tblContact
frmTeam - for building the teams in tblteams.

On frmContactInfo I have a listbox for choosing positions, including if that member is a team leader. On frmTeam, I have a list box for choosing specifically the team leaders. This part is working great i.e. does what I want.

Now I have placed tabs on frmContactInfo and put frmTeam on one as a subform. So far so good. What I'm aiming for is my end user to be able to open the main form, fill in the new member data and choose his or her positions from the list box. If a certain position is "checked", I'd like the subform to open, or get focus and the control "teamname" to get the member's last name. I only want this form to get the team name from members who have that specific position, not from everyone. After filling in the rest of the data I'd like for the focus to return to the main form or the subform to close, whichever is best.

I do NOT know VBA code, and get confused rather easily with such things so if someone could walk me through setting this up (I assume it will involve a macro) and use as much non-geek-speak as possible I'd really, really appreciate the help.

hi Folks
Would really appreciate your help with this..essentially these forms are to track treatments (each of which has a number of sessions for customers)

I have 3 forms: Main form for Patients (mFrmPatientDetails) .I have this set up as with combo box: (There is an underlying tblPatients table which has all their details)

Then I have the subform (sFrmTreatment) in this based on treatment table: tblTreatment
TreatmentID (PK)
PatientID (FK)
Treatment number (to be entered by user)

This sFrmTreatment form will have a subform (subsFrmSessions)
SessionID (PK)
TreatmentID (FK)
PatientID (FK)
session descriptions etc.
So far they all link together correctly but I did this before on another database and the forms did not always work well together :-(

How can I set up these forms to do the following for the user?
Show Patient Name and MR rather than Patient ID but in the background be using the PatientID to link together. So that the users are sure who they are entering the data for
Treatment number to increment for that patient (would it be enough to have that set up as an autonumber ?)

What I am noticing is that when I choose a new patient from the combo box - that doesn't seem to update the mFrmPatientDetails (because I have also left the PatientID
PatientMR visible so I can check...

Essentially what I want is the following..
User picks up patient from combo box.
They add the treatment number and date of commencing treatment. They can move (via buttons back and forth between the treatments for that patient)
They can view the details of the multiple sessions for each treatment for that patient...
What do I need to do to make this more stable (and also hide the ID keys as much as possible from the user)

I am very very new to Access and have been building a program to track tasks. The problem I'm having is I have my Assets table open by use of my Assets form. There is a subform called Tasks, in a database format, that only list related tasks to the Asset that is on the Assets Form. When I go to the Tasks Subform and click on a record, I want that record to open in another form called EditTaskForm for editing the data within that task. When I click on that record and it opens the EditTaskForm, it always pulls up the first record within the Tasks Table. I have used the following VBA code that I found on the web to try and locate the record and edit that record. The VBA that I'm trying from the Tasks Subform is as follow:

Private Sub Form_Click()
Set rst = Me.RecordsetClone
rst.FindFirst "[TaskID]=" & TaskID
If rst.NoMatch Then
' do something here if there is no match or just ignore it
MsgBox (TaskID)
Me.Bookmark = rst.Bookmark
DoCmd.OpenForm "TaskEditForm"
End If
Set rst = Nothing

The MsgBox listed in the code above actually shows the correct TaskID, but when the TaskEditForm opens, it always opens to the first record within the Tasks Table instead of opening the record requested. The two related tables, Assets and Tasks use the field "TaskID" in both tables. The relation is set to these fields.
How can I get past this point and be able to edit the record I selected?
Help with this would be HIGHLY APPRECIATED.

I need help with a query and the control source of a combo box...I guess there might be several answers here, but here we go...

I am building a new (my first) database to keep track of certain data received monthly from several different countries. I was going to save all the data in tblReviews which has an individual ReviewID for each review (each monthly review from a particular country).

I want to give the user the option of looking at, for example, sales for December 2011 for 10 different countries. This is where I run into trouble...I want to have a combo box for the month and another one for the year. How would I set this up to then be able to utilize the user's selection in a query? Would it be easier to save the review dates in two individual fields?

Also, can a query take information from check boxes (where the user would pick which countries he or she wants to compare?) Thanks!

I'm sorry if this has been posted before. My internet speed is dismal today, which makes searching the forums difficult...many searches are coming back with a lovely error screen thanks to timing out. But, I digress...

I have a database setup with one main table consisting of two fields - one auto-generated system ID and one manually entered case ID. All subsequent tables link back to the auto-generated system ID through relationships (or link to the parent table through the appropriate field if not directly related to the main table). Most are on one form using tabs and subforms. This part works very well.

This is a confidential and sensitive database that tracks sexual predators and services provided for their (child) victims. Therefore, I would like to have the information pertaining to the alleged perpetrators on a separate form that pops up from the main form on a button press. Since the alleged perpetrator may either be in the system already or may not exist yet, I need a way to enter a new person or select an existing person, and I need to be able to add up to five alleged perperatrators per victim. I would like to set this form up similarly to the "main" form in that it will have two tabs with subforms - one for the alleged perpetrators and one that contains information about the incident (without information about prior incidents unrelated to the current one). The suspect table is linked to the main table through the auto-generated system ID and the incident info table is linked to the suspect table through an auto-generated suspect ID field. Given what I want to do, I see that I might need to change this relationship. I will play with it.

I need to make this database as user friendly as possible for my client, which is why I'd like to have this information pull up separately. To me, it seems like it would aid in statistical tracking and also save time on data entry, but I know they don't care about the amount of typing they do, so if I'm making it harder than it needs to be, then I'll just put everything on the main form. That's easy enough.

I can work with VBA, but I am a meddler and by no means consider myself even an intermediate user, although I am an advanced Access user. I used to do a lot with Access in the past, but my databases have been fewer with larger gaps in between lately, so I've grown a bit rusty. I'm using Access 2007 at the present time. Thanks in advance for your help.

Not finding an answer? Try a Google search.