Over 1 million tech questions and answers.

How to combine fields in Access 2010 based on 2 equal fields?

Q: How to combine fields in Access 2010 based on 2 equal fields?

Hi all,

I?m really in need of some help from some Access experts! I?m a newbie to Access, but I got myself busy with quite a big project... I?m trying to map out the product supply to shops in the whole country. I?ve come quite far for a starter, but I keep having 3 questions, it would be great if you could help me with this!

I have, amongst other tables, two tables called Stock_counted and Reported_quantity.

The table Stock_counted has the following fields:
- Shop_number
- Product_code
- Quantity_counted

The table Reported_quantity has the following fields:
- Shop_number
- Product_code
- Quantity_in
- Quantity_lost_or_returned
- Quantity_out

1. What I would like to do is to make it one table based on Shop_number AND Product_code. So in one record I would like to have Shop_number, Product_code, Quantity_in, Quantity_lost_or_returned, Quantity_out and Quantity_counted. How do I do this, making sure that the quantities end up behind the right product and shop? It is possible that some shops don?t have all items, while other shops might not have all products reported, or both. On top of that, I have another table with a price for each product, which needs to be related to it as well.

2. It happens that some products are found more than once in one specific shop and therefore appear more than once in the table, with the same shop number and product code. How do I automatically SUM these quantities, so that this product only shows up once per shop?

3. Finally, I have some products which are the same but have slightly different descriptions and therefore different product codes. Is there a way to link them all under one (existing, one of the descriptions) code without having the other codes showing up anymore?

I would really appreciate it if anyone can help me with this, I?m desperate!!

Thank you so much,

Access 2010 user

Preferred Solution: How to combine fields in Access 2010 based on 2 equal fields?

I recommend downloading and running Reimage. It's a computer repair tool that has been proven to identify and fix many Windows problems with a high level of success.

I've used it in the past to identify and fix everything from blue screens (BSOD's), ActiveX errors, corrupt files and processes, dll/exe/sys errors, recover lost memory, Windows update problems, defragging, malware removal etc.

You can download it direct from this link http://downloadreimage.com/download.php. (This link will automatically start a download of Reimage that you can save to your computer.)

A: How to combine fields in Access 2010 based on 2 equal fields?

Quote: Originally Posted by Josephine87

2. It happens that some products are found more than once in one specific shop and therefore appear more than once in the table, with the same shop number and product code. How do I automatically SUM these quantities, so that this product only shows up once per shop?

select shop, product, sum(quantityfield1), sum(quantityfield2)
from sometable
group by shop, product

Read other 1 answers

I have one table that has information on all my gears, called tblGearFeed. This table has 3 columns of information (CAM, Feed, and Gear A)

I am using a form called frmQuotation.

I have "CAM" and "Feed" fields that users will fill in. Based on these two fields, I need the "Gear A" field to lookup the cooresponding data from tblGearFeed.

I have been trying to use the following function:

=DLookUp("[Gear A]","[tblGearFeed]","[CAM]=Forms![frmQuotation]![CAM] And [Feed]=Forms![frmQuotation]![Feed]")

This formula is not working.
Can anyone guide me as to what I am doing wrong?

A:Solved: Access- DLookup based on two fields

If you do an AND it will only bring up the values in each field that equal your parameters. Use an OR instead.

Read other 3 answers

Hey there again, I've come across another issue with my program:
I have a search form that allows the user to select a piece of equipment. A macro passes a value (EquipmentID) to a new form, and all of the fields are suppose to draw info from the table based on EquipmentID.
My problem is getting the fields to automatically update once you change the value. I started with using the ResourceSource of the forum, but users can cycle through enteries and once the Equipment ID changes the form doesn't update. So I'm down to several options:

Somehow use the ResourceSource correctly
Use a macro/code to fill the info in
Fill in a invisible field and pull the info from there

If you've got a suggestion on how this should be done, or you could let me know more on what's actually happening in Access, please let me know.

A:Access 2003: Autofilling fields from a Query based on a Value

The form, if it remains open does not get the new Value unless you "Requery".
To test if this is the problem you should be able to use Main Menu>Records>Refresh,
if that updates the form with your new value then that is the problem.
To me it would make more senese to have the New forma a Subform to the first form, even if it is as a Tabbed Subform.

Read other 1 answers

I am fairly new to database and I don't know much about VBA coding.
I am creating a database and I have created a form for end-users to submit create a new record. but I want all the fields except one to be required.
I tried to make them required in the table but it gives me error when I enter data through the form. Can anyone please help me with this.

I have the following fields to be required

First name
Last name
Date of birth
Employee level
employee status
Employee ID
Hire date
Phone number
Model ID

Thank you in advance

A:Access 2010 Require Fields

Read other 6 answers

I cannot change entry in ID field of Facilities Table due to 1-1 relationship to Test Data Current Table. I just want to modify it from "70100" to "AI-70100".

A:Modifying Fields in Access 2010

Is there just the one record that you want to change?

Read other 3 answers

Here is my problem. I have a table with details of enrolled students. Many of the students have the same name with only an initial or middle name to differentiate them.
I have a field each for forename, initial/middle name and surname in the table.
I need each field to be able to accept duplicate entries but where both forename and surname are identical, the initial/middle name must be a required field.
Problem is, not everybody has a middle name, so I cannot set the field property to required. I need it to be required ONLY in the case that the forename and surname are identical.
Anybody got any ideas?
I tried using ID, forename, initial/middle name, surname as PKs but that doesn't work.
Please explain in simple words, I am not a technical expert, just an admin.

A:Access 2010 unique fields

Read other 8 answers

I have a Table in Access 2010 which has several true/false fields. Is there a way for me to pull a report that lists which fields are true for each record while not listing the other fields?

A:Access 2010, List True Fields

Read other 8 answers

Usually iin MS ACCESS 2010 when you create a field in a table for attachement, the sub fields are there in the " Add Fields" top a form but this time after entering the field of Attachment in a table , i dont see any of the subfields. Did I miss something here.
Your help is appreciated.

A:Solved: MS Access 2010 sub fields attachment

Sorry, I have never used Access 2010 or it's Attachments field.

Read other 2 answers

I am using Access 2010, I have certain fields on the form that need to be entered in Arabic and certain in English. I have set the keyboard language for the text box to be "Arabic" (for text in Arabic) and "English" for text in English. When I do this, it takes a little more time to move between fields. How can I overcome this problem. In Office Xp, I never had this problem.

A:Access 2010 - Moving between fields takes time

how did you set the thext boxes to use different languages? I have never done this so I need to configure the same to test, also have you gone through all the settings and advanced settings of language Bar, Regional settings etc..

how much more time are you talking?

Read other 3 answers

Now in the form, only Names is updated in table tblOrders. I want to update BizUnit in tblOrders too, what should I do?

A:[ACCESS 2010]2 tables with same 2 fields but only one field updated.

Why do you want to add the BizUnit to the Orders table?
I notice that you also have emails in there as well, they also should not be in there.
Only data relating to the Order should be in the orders table.
The data is then brought together using a Query for Output purposes like Reports & Invoices etc.

Read other 1 answers

You guys have been a huge help in the past with issues I've had with Software Licensing database I created in Access, so I'm hoping you can lend a hand with a new issue I'm running into:

i copied some data from a table to do some comparison and clean up in Excel. I realized a little late that I should have pulled more fields than I did... I have done the clean up and now I'd like to get the additional fields to finish the job. I've imported the cleaned up data as a new table (Table A) and want to run a query against the original table (Table B) to pull those additional fields. Not all of the records in Table A are present in Table B, and vice versa.

In the query design, I show Tables A and B and join the First Name, Last Name and Application fields. The Join Properties are to include all records from Table A and only those from Table B where the joined fields are equal. Table A has 1606 records, so my hope is to have the query return 1606 records with the missing fields added. I'm bringing in all of the fields from Table A and adding the missing fields from Table B.

The query returns 1899 records. I ofound one user who only has four records in Table A, three records in Table B, and the query is returning 12 records for them.

What am I doing wrong?

A:Access 2010 - Adding fields to a table using a query

Read other 16 answers


I got a form A and a subform B. I use B to display data retrieved from a table while use B to simutaneously update that data. More particularly, if I click a row in B, that row will be display on A and the user will be able to edit that data. The forms were designed by someone in MS Access 97. I converted it to MS Access 2003 format. Therefore, I have to link form A and form B. However, I couldn't find Link Master Fields and Link Child Fields in MS Access 2003!

Any ideas? Thanks in advance!

A:Link Child Fields and Link Master Fields in MS Access 2003

climbingrose said:


I got a form A and a subform B. I use B to display data retrieved from a table while use B to simutaneously update that data. More particularly, if I click a row in B, that row will be display on A and the user will be able to edit that data. The forms were designed by someone in MS Access 97. I converted it to MS Access 2003 format. Therefore, I have to link form A and form B. However, I couldn't find Link Master Fields and Link Child Fields in MS Access 2003!

Any ideas? Thanks in advance!Click to expand...

It really sounds like form A and subform B are both using the same table, and are not a Master/Child field link situation. But rather, form B (the subform) will have code in the "On Current" event that will find the current record on the form B within the form A, therefore allowing that record to be edited. Review your original post, I think you used the "B" one place where you meant to say "A". No matter what, you need to clear up with us out here if both form A and form B are using the same table.


Read other 1 answers

I need to combine 27 tables with same record ID's but all tables have different fields. How can I do this in ACCESS 2007? Many thanks.

A:Solved: combine tables with different fields in ACESS2007

Read other 14 answers

MS Access:
I have several fields in a form that uses two tables. I would like to set up a command button so that when you are on a current record, it will copy the field information from the record you are on to other fields in the same record. I do not know the SQL commands to set this up.

For example, there are name fields, address fields, and phone to be copied. Reason: One is contact information; one is survey information. In most cases the information is the same but on rare instances, the information needs to be entered (when different).
This is not an update for all records in the database. This is based individually on each record.
Not sure if you need to do On current, on click, or after update.
Not sure if default value in form can be used.
Please help.

A:Copy data fields in same table to other fields in same table MS Access

Read other 16 answers

I am trying to create links to specific locations with each document between two documents. The document names will change. I can update the current name of the other document by creating a custom property called "otherDoc". I have solved most problems, however, I am unable to insert a field into the address of the link (to make the link dynamic) without utilizing the showfieldcodes command (which is very memory and time consuming since the documents I am working on are very large). Below is my code. I was wondering if there is any way to set the address as a field? Or somehow input an address and it would be understood by word to be field.
Sub link_to_other()

'pastes hyperlink to the other document
Selection.PasteSpecial Link:=True, DataType:=wdPasteHyperlink, Placement:= _
wdInLine, DisplayAsIcon:=False

'selects the hyperlink that was just inserted
Set myField = Selection.PreviousField

'creates variables
Dim temp_address As String
Dim temp_subaddress As String

'sets variables
temp_address = ActiveDocument.CustomDocumentProperties("otherDoc").Value & ".doc"
temp_subaddress = Selection.Range.Hyperlinks(1).SubAddress

'changes the hyperlink the the dynamic value
Set SCut = ActiveDocument.Hyperlinks.Add( _
Anchor:=Selection.Range, _
Address:=temp_address, SubAddress:=temp_subaddress)
End Sub


Read other answers


I'm working in Access 2000. I created a database in which you select a course number and the course name automatically appears in the "course name" box. I created this database but cannot for the life of me figure out how I did that.

Now I need to do it again...I've looked at the properties of the fields in the form and everything, and cannot find where you point it to fill in one field based on a another.

Can anyone help me with this?


A:Access2000 -Automatically fill in fields based on a selection

Read other 6 answers

I have a form designed to track processing times for various activities. This information is entered from a Production Log that the employee completes throughout the day. The main form has the following fields: Name, ArrivalTime, DepartTime, ProductionDate. After the user enters data into these fields, the cursor moves to the subform where the activity id is entered, the start and end times for that activity, the beginning & ending sequence number, and several other fields.

Since some activities don't require entry into all fields, I would like to disable certain fields based on the value entered into the activity id field. I have 4 activity ID's that I want to trigger the disable event on 3 other fields in the subform. I need to know how to code this, where to code it, and then to check in the form's BeforeUpdate event that the above is correct.

Thank you for your help!

A:Solved: Disable Fields on Subform Based on Value of Another Field

Read other 10 answers

Every day I send out excel documents that I then convert to pdf's. I use the same excel template on each document. The only thing that changes in each document per recipient is the "name" "company" & "email" fields in the excel document.

Question: Is there a way I can get these documents to interface with outlook so that when I select more than one person to receive the document (as many as 30 people will receive the same document), and auto populate the contact info "name" "company" "email."

This way would save me a few steps so that I don't have to pick a contact... type all their info in the fields... send. Select my next contact... enter appropriate info in the fields... and repeat and repeat.

I'm emailing job specific information to everyone working on the same job. I'm sending it to multiple people, but need it to look like they were the only one who received the document. Just like when you get an email about the "deal of the week" that says, "Dear Mr. Johnson, How's your summer going?.... " while someone in NYC gets the same letter that says "Dear Mr. Thompson, How's your summer going.... " from the same sender. Of course the deal of the week changes each week.

How can I make this happen? If there is a better way outside using excel to create pdfs, or a totally different method entirely. I'm all ears. I just need to send the informatio... Read more

Read other answers

Is it possible to create user defined fields in outlook and then make them sortable?
We would like to Categorize clients by
By type and office
Be able to sort by office
By Client Level
By Portfolio type

A:User Defined Fields Outlook 2010

Read other 6 answers

I've been searching all kinds of fora for this problem and although I find information on how to do this with (legacy) Form Fields, my problem involves Content Control fields and I can't seem to fix this.
Basically I have Sales Reports that are being filled out on a weekly basis by all Sales Reps. These contains all kinds of different Content Control fields (Drop down, rich text, dates etc...), and the data in there should be copied to an excel sheet. Meaning: copying the contents of the fields from all the different reports into 1 excel report.
Below is a Macro I found which works for legacy forms (FieldForm) (pre-2007) but I cannot seem to make it work for 2010. I pinned down the problem to being just a wording problem in MS, but then again, it might be a little more complicated

Many thanks for your help,

Dim vField As FormFields
Dim fso As Scripting.FileSystemObject
Dim fsDir As Scripting.Folder
Dim fsFile As Scripting.File
Dim wdApp As Word.Application
Dim myDoc As Word.Document
Dim vColumn As Integer
Dim vLastRow As Integer
Dim x As Integer
Sub AddFormFields()
vLastRow = ActiveSheet.UsedRange.Rows.Count + 1
vColumn = 1
Set fso = New Scripting.FileSystemObject
Set fsDir = fso.GetFolder _
("Q:\Sales Reports\Unprocessed")
Set wdApp = New Word.Application
wdApp.Visible = True
For Each fsFile In fsDir.Files
wdApp.Documents.Open (fsFile)
Set myDoc = wdApp.ActiveDocument
For Each vField In wdApp.Documents(myDoc).FormFields
vV... Read more

A:Exporting data from Content Control Fields in Word to Excel 2010

Read other 6 answers

how many total fields can you have in an access table
I'm using access 2000

A:access fields

I believe the limit is 255, but I am not 100% sure. Even so, you will most likely never have that many fields in one table...

Read other 1 answers

I must be having a brain fade here but what i want to do is real simple, just can't get it right.

I have a field on an MS Access form and all I want to do is check if something has been entered into it.

Something along thw line of :-
if field is blank do then
do something
else if field is equal to spaces then
do something different
end if

of the life of me I can't get it to work :-(

A:Blank fields in Access

I'm presuming this is just pseudocode for a corresponding Vb project.

If so then i recommend:

Option Explicit

Dim wksp As Workspace
Dim db As Database

Private Sub command1_Click()

Dim entry As Recordset
Dim search_string as string

search_string = txt_search_string.text

Set wksp = DBEngine.Workspaces(0)
Set db = wksp.OpenDatabase(App.Path & "\DATABASE NAME.mdb")

Set entry = dbfacilities.OpenRecordset("SELECT * FROM [TABLE NAME] WHERE [FIELD NAME] = '" & search_string & "'")

If entry.RecordCount = 0 Then
MsgBox ("The field is blank")

txt_search_string.Text = ""

'unsure of what you wanted here...
End If

Hope this helps.

Read other 3 answers

I have two tables tEp and tLn.

I have a field in tLn for tEp but I need to be able to select multiple values, Ive followed guys on office and here: http://www.databasedev.co.uk/multivalued-fields.html

But I cant get the Allow Multiple Fields checkbox!! It just doesnt show!?


A:Access Multiple Value Fields

Worked out this only in accdb, is it possible to do something similar in mdb?

Read other 1 answers

In Access 97 SR-1 is it possible to create a lookup field in a table that is also a required field? I have created a table in Access containing several lookup fields. Now the user is requesting that the fields also be required - that the person doing the input via a form would not be able to advance to the next record before choosing an option in each lookup field in the current record. Setting the required switch to "yes" after the field has been created as a lookup field does not prevent the user from advancing to the next record in the form.

Please advise any suggestions. Thanks!

A:Access 97 required fields

You can set the "lookup" field back to a standard field and use a Combo box to do the look up for you.

Read other 2 answers

I have this table that I want to have a drop down list for the builder and then the next field another drop down list that only lists those tracts that the chosen builder has. I am trying to create a form right from the table

A:Access lookup fields

Please read this previous post

Read other 1 answers

I have a form with Qty, Price, Total, MTD(MonthToDate), YTD(YearToDate). I am only storing Qty and Price in the Table. I can get a sum for Total, but how do I get a sum for MTD and YTD?

Should I have a field in my table for the Total, MTD, and YTD?

What is the best way to set this up?

A:Summing fields in Access

Read other 10 answers

Ok, i have a form that has a calendar...when you click on the "hiredate" or "birthdate" the calendar appears, and when you click anywhere on the detail area, it disappears...however, whenyou change a date on the calendar you must click in either the "hiredate" or the "birthdate" for it to take the change....i would like to make this automatic, so that when a person clicks the calendar the coresponding field...hiredate or birthdate, is changed....is this done with the "on update" event? any help would greatly help my grade!
if so could someone give me an example?


A:Access: One Calendar for Two Fields?

Yes, well, a couple of questions, so that our solutions change your environment the minimum amount possible:

1. Are you sure that the calendar that appears is on the same form? Or is a special calendar form appearing and disappearing? You might want to poke around in the db window, and/or post back the details (macro or code behind the events, for example, on click) for hiredate and birthdate.

2. Also, what's behind the calendar? The tricky part of what you're talking about is having the same control (or form containing a control) for two different text boxes; that's why you're having to click in the appropriate box, I'm guessing: to tell the calendar which box to put its value in.

I'm guessing that you didn't build this initially? If these questions seem to complicated, just zip up an example version of the related table(s) and the form(s) in an .mdb file and send them to me. (Must be under 1MB.) But if you can post back the details on what's going on behind your controls, we can probably give you a faster response.

Read other 3 answers

Here's the situation:

I have been given several access databases with contact information in them. The contact information has all the usual info (address, phone, etc.); however, the name format is two different fields: a first name, and a last name. My goal is to import all the database information into ACT!

I was planning on doing this by exporting a text file with comma seperated values; the only problem, though, is that the existing ACT! database doesn't have two name fields, and instead only has one "Contact" field.

Sooo.. my question is, would it be best to try and convert the names to one full name in access, or is there some way to import and convert them with ACT!? And, whichever way is best, how exactly would I go about doing that?

Thank you very much!!

A:Merging two fields in Access and/or ACT

Read other 7 answers


I am currently building a shop type database...

I have made a 'transaction form' On the form is things such as transaction ID, customer details, staff details etc, then there is a subform, where the products on the transaction get entered into.

On the subform (Transaction/Product link Subform), there are the fields (among others...) 'Product ID', 'Price' and 'Quantity', I have then made an unbound field to calculate the subtotal which has '=[QuantityOrdered]*[p_Price]'

On the main form, I then wanted to have a 'total' field, whereby it adds up all of the subtotals from the subform, the problem is is that through the various methods i have used i keep on getting #Error or it will only show me the subtotal of one record, when I need it to show me the total subtotals from all records.

Any solutions? please

A:Calculated fields in Access

... it will only show me the subtotal of one record, when I need it to show me the total subtotals from all records.If I understand correctly what you said, I think you need to make a calculated field on the form which will say something like=Sum(the name of the calculated field on the subform)and put that total field in the footer of the form. The footer should contain form totals.

Read other 4 answers

I am building a query in Access 2007 and when I select and drag a field from a query into the lower section of the building query screen it only brings the one field (like I want), but when I run the select query I am getting all the fields in the original query. How can I get it to only show the one field?

A:Access Query too many fields

Ensure that the query does not contain a column with the heading tablename.* or queryname.* where tablename and querynames are the names of your query table or query.
The "*" means all fields.

Read other 2 answers

I know how to concatenate field values in a record but I'm wondering if it is possible to concatenate field values in separate records. My reason is that I have a form showing data from a query made from two tables. I want only one record in the underlying data and if I join two tables where one has multiple matches then there will be multiple records in the query. Table 1 has a record with joining field f1 (no duplicates allowed) and Table 2 has multiple records for f1 with different f2 values. Here's what I mean:
T1 has f1 = 90000
T2 has
f1 = 90000, f2 = T
f1 = 90000, f2 = Y
f1 = 90000, f2 = N

Is there a way to set up a query operating on T2 to give a table with the value
f1 = 90000, f2 = Y,T,N

If so, then a join on f1 with this table would yield only one record for the form I display. As a fallback position should I just do a summary query on T2 and show the resultant records in a small subform?

Thanks in advance for any advice on this?

A:MS Access Concatenate Fields

Read other 6 answers

I have generated an access report that shows the process comments for the different locations we have in the organization. What I need to do is to count the total number of comments we have per day for the daily report I generated. I used a textbox under the gourp footer with the expresson "=Count([Comment])". It worked fine to get the count of teh comments under each goroup. I need to gererate a grand total for the report summing the values of the textbox with the above expression. Also, is there a way to count the text fields one time as a grand total for the report. Thanks in advance.

A:Summing calculated fields in access

The unbound text box that you have used for the "=Count([Comment])" should have a name like text2, so you use the following in a another unbound text box in the report footer using the correct name for your counting text box


I am not sure about your last question "is there a way to count the text fields one time as a grand total for the report"

Read other 2 answers

have two tables like

Staff Table
Staff ID
Staff Name
...and more

Staff Salary Table
Staff ID
Staff Name
basic Salary
...and more

Now I have created the Staff Salary form in which I got the staff ID from the staff table using the lookup wizard query in the Staff ID field of the Staff Salary Table
SELECT [Staff Table].[Staff ID] FROM [Staff Table];

but I need one more

When The staff ID was selected from the drop down list, I need the relevant staff name (from the Staff table) also be displayed in the staff salary from .

Can you please let me know how to do this..

A:Getting the data from different fields [ MS Access problem ]

Read other 12 answers

Hello All.

I am working on a piece of code to check what field names are in a query and then I need to pass those field names into another query.

I found this code here on the forum and it gives me field names but I need to skip a few known field names as well.

Any ideas?

Sub FieldNames()
Dim Rst As Recordset
Dim f As Field

Set Rst = CurrentDb.OpenRecordset("qryAccessorial_CrossTab")

For Each f In Rst.Fields
MsgBox (f.Name)
End Sub

A:Getting fields names in Access 2007

Read other 13 answers

Hi all,

I am making a report in Access 2003. I have salaries that will be displayed by the month. However, since we are only in April, the months May-December are not in the query that the report is based on. Is there a way that I can make the report display a 0 in the place of the months that aren't yet there? The months that are there will display the salaries.

I appreciate any help! Thank you!

A:Access 2003 non-existing fields

Are you using a Crosstab query for the Report?
If so you can force the Headings to be entered by typing them in the Haedings Property of the Query.

Read other 3 answers

I've looked for this and people don't seem to have the problem. I have a form that populates a single database. on enter a new refreshed form is presented to the user - but now my user has requested that 3 fields on the form not clear. She wants to be able update them if needed, but generally they will not be changed during a batch of input, after she has entered them the first time. How do I STOP them clearing?

A:Access form clears all fields

Read other 6 answers

Hoping this is one of those easy to answer, dumb questions.

I'm using Access 2002. I've created a form with subforms, and linked the data collected on the subforms to the main form via the master/child links.

What I now want to do is:

1. On the subforms, enable the user to select a value from a drop-down list, and then populate the remaining fields on the subform with data based on the user's selection;

2. Enable the user to edit the autopopulated fields, yet maintain the integrity of the original values so that they can be used again and again.

I'm a stumped as to how to accomplish this. As you guessed Access isn't exactly my forte, but I'm a quick learner.

Any ideas? Thanks for your time.

A:Autopopulating editable fields in Access

Hi Shug02,

Try this:

In the combo, add the extra data you want to use to autopopulate the form as extra columns.

You can do this by clicking on the Row Source in the combo box's property sheet. Increase the number of columns in the combo by setting the column count to the required number, and set the width of the additional columns to 0.

Now the combo will invisably return the associated data along with the original data... but this will be cunningly hidden from the user.

In the AfterUpdate event add an Event Procedure along the lines of:

Private Sub ProjectName_AfterUpdate()
Me![ProjectManager] = Me![ProjectName].Column(1)
End Sub

This will put the data in the first column of the ProjectName combo into the ProjectManager control on the form. If the column has a width of 0 it will be hidden from the user.

Read other 3 answers

Hi there,

Let my preface this post by saying that I have some experience working with Access, but nothing official and so what I know is as a result of hacking my way through. Also, let me start by saying that I apologise if this seems long winded, but I am unsure how much info to give.

I've inherited a database at work which I need to develop further, but it uses some methods I am unfamiliar with, though I think I understand enough to know they need to be there.

Basically the database is a customer transaction system and its that particular section I am trying to sort.

I have a table (tblPupilTransaction) which is meant to be updated whenever a transaction occurs. It has :

Transaction Date
EAN (item code)
Billing Status

However, because there will only be a static group of customers that can purchase from us, we have a lookup table (tlkpPupil) which has :


My difficulty comes when developing the form that would be used to input the transactions. I had thought to use combo boxes to link FamilyName, GivenName and PupilID into one selection, but that doesn't automatically complete the exisiting PupilID field (which cannot have a null).

Likewise, when I use a combo box to put in the product data, the price is not automatically complete (these also have the appropriate lookup tables).

So, in short, I want to know how I create a form which will input into tblPupilTransaction w... Read more

A:Bound Fields on Access Forms

You are on the right track. Have the data in the Combo boxes. All it takes then is some simple VBA code in the Combos After Update Event Procedure to pass the values to the fields.
However, the only thing that should be stored for the Pupil and Product is the ID numbers.
The rest should only be "Displayed".

Read other 3 answers

I was wondering if there was a way to jump to a specific field in a Access database from VB6 using an SQL statement or something simliar.

Any help on this matter would be greatly appriciated,

Thanks in advance,


A:Jumping to Fields in VB6 from Access Tables

Read other 8 answers

I have a table in an Access database. This table has three columns in it, and I want to add a fourth column. I want the value of this fourth column to be "Primary" for every record.

What's the easiest way to accomplish this? I'm an Access newbie.

-- Richard

A:Auto-fill access fields?

Read other 6 answers


I have developed a simple database and used the built-in 'access search' facility that is applicable to all forms. There are around 7 fields in each table.

Problem: When I select more than one field (through the dropdown list), it lists all data instead of listing only data related to the first selected field.

Question: How do I link the fields so that during a 'search', if I click the first field and select a value (e.g. field size) then if I select the next field (e.g. colour) - it will only list colours pertaining to the selected field size - not all the colours of all the field sizes in the database table?

Any assistance appreciated.

Many thanks


A:Access 2003 - linking fields


I requested the moderators to move this thread to the Busines Applications section. You will get more attention there, and most probably, the quickest solution as well.

Read other 2 answers

I've got a form in access and all I want to do is make a "total" box that adds 5 fields with numbers (not all those fields may be populated at the same time). So if the first field has a value and the second does not, my total field is blank. If both those fields are populated, it adds. What am I doing wrong? My syntax thru the expression builder is =[1st Account Balance]+[2nd Account Balance]


A:adding fields in access database

Read other 16 answers

I will be working with an extremely large database and am wondering if the maximum number of fields,in MS Access (255),is absolute. I may also have several tens of thousands of records. Can the program be tweaked to allow for more fields? Is it RAM dependent?

A:Maximum Number of Fields in Access


I strongly suggest that you never create a table with that many fields. Instead, think about how you are laying your data out. Access is a relational database where specific data should be kept in individual tables.

For instance, a payroll database should contain several tables, including: personal employee information (empID#, SSN, Name, Address, etc.), payroll info (empID#, PayLevel, amount of pay or range of pay), gross pay by period (empID#, date paid, gross amount, taxes withheld, net amount), etc., and they would all be related by the empID# field.

By doing this, you should have a much more manageable amount of data in multiple tables.

You can email me a sample of your data if you want. Just make sure there's column headings and about 100 records would be great.

I've emailed you a document about "normalization," a term used to describe the way to set up tables in a relational database. Hope it helps!
[This message has been edited for tactfulness by Dreamboat (edited 03-29-2000).]

[This message has been edited by Dreamboat (edited 03-29-2000).]

Read other 2 answers

I produce my departmental purchase plans on MS Access so that I can attach my own symbols eg prioritising and run queries. I have been told that I can transfer/link/export individual fields from Access into a template order form set up in MS Word. Is this possible? How is it done? Is there an easier way?
Any suggestions?

A:Linking Access Fields to Word

Hey there.

Is there some reason a mailmerge from Word won't work?


Check out how to set up a mail merge.

Read other 1 answers

Please help. I am trying to calculate a percentage of my yes's. I have several yes/no fields in which I'll need to calculate a rate. I would prefer to create a report that will calculate this for me. However, right now, I am really just looking for the easiest/quickest way to do this. So if I need to run a query first and base the report off the query, I'll do it. Please help ASAP if you can.


A:Solved: Calculating Yes/No Fields in Access

Read other 16 answers

Hi, I receive an excel file everyday that I need to import. However, the column header of the fourth column of data changes daily (it's the corresponding day's date). I would like to import/append the data to the previous day's import. Is it possible to import and append fields by their position number, e,g, the fourth column is not called using its column header but is called by its position, say "field4"(?).

A:Access: querying fields generically

PMCD, welcome to the Forum.
I don't think what you want to do can be done with just one Import query because of the name change and Append Queries use the field names.
You can Import the Data to a temporary table, change the name, append the data to the master table and the run a delete query to remove the data from the temp table.
What you could have is an SQL statement renaming the field, although I am not sure if SQL can handle the field name change, and then run the append query.
You can definitely use VBA code to change the name and then run the Append query though.
Or you change the field name manually prior to running the Append query

Read other 1 answers