Tech Problem Aggregator

Counting tables in a MS Access DB

Q: Counting tables in a MS Access DB

I assume I need to use SQL. I know how to access a specific database and find the record count. Does anyone know how I access the MS Access DB and know how many tables are in that database? I need to export this count so another programming language (autoit) can pick it up.
Thanks

A: Counting tables in a MS Access DB

If you can get record count, you can get table count.

dim db as database
set db = currentdb
debug.print db.tabledefs.count

1 more replies
Answer Match 71.4%

So I have a huge amount of data that are all projected visit dates that range over a course of future years. I want to pivot the data out so that I can relay back counts of Month/Year as needed to an individual text label. Everything I find online tells you how to pivot if you have data in a cell but not how to manipulate and work with dates in a cell instead.
Attached is my data.

I want to be able to use a pivot to tell me (for example) 'There are 68 visits occurring in January 2013 and those visits belong to site 1 (20 visits), site 21 (40 visits), and site 42 (8 visits)'. Or that it can tell me, for site 1, 20 visits occurring in January 2013 are for V2 (3), V5 (10), and V20 (7).

Any help is appreciated.

Using Excel 2007.
 

A:Excel 2007 pivot tables counting dates help needed

It looks like you don't have your table set up properly to get the pivot you're looking for. In order to do what you want, you have to have a column for Visit# and you would list your visit number there. Get rid of all of the other Visit columns. Then you have another column for date. Keep your columns for patient number and site number. Then you can create the pivot you want.

So basically, you want to convert all the visit columns (V2, V3, etc) to one column by patient and site, with the same patient listed many times in the same column.

The problem is, you want a pivot table that compares three values (three columns), Site, Visit#, and Date. But you have your dates separated across 52 different columns and that just won't work.
 

3 more replies
Answer Match 57.12%

I have what I hope is a simple issue in Access. I have a simple set of results of targeted email campaigns. I have a Access report grouped by Salesperson and within that group, each email recipient is listed with an indication of whether or not that person opened our email. Such as:
Smith, George Opened
Smith, John Opened
Smith, Sarah Not Opened.

I am looking for the proper function/syntax to count the total number of "Opened" messages in each group. I've tried a DCount function, but haven't gotten it to work. I know I can work with a count in a query, but I'm interested to see if I can do this right in the report and learn something new regarding functions.
Thanks!
 

A:Conditional Counting in Access

6 more replies
Answer Match 57.12%

I have a need to count the number of employees that are assigned to an associate director. I have them identified in the Employee Info Table as Associate Director ID = 24 and 9. On a form called frmDirectors Report Date, I have a choice to pick which Director, then the printer function calls the report and subsequent query to limit the data. One calculation on the report need to know how many employees in total are assigned to the selected director in a calc that figures average employee hours. I cannot figure out how to add the count to the existing query or any other way of approaching this issue. HELP. I have included the MDB for reference.
 

A:Counting Records In Access

I have already answered this question on your previous thread.
 

1 more replies
Answer Match 57.12%

i have an access file that has two tables. one table lists all the foremen and the other has the inspections. i want to print a report that shows how many inspections each foreman has. some foremen will not have any inspections. the two tables are linked together already.
 

A:counting records in access

If the inspection table has the field references the foreman, then use the following select statement.
select foreman,count(foreman) from inspection table group by foreman
Regards/Agoo
 

1 more replies
Answer Match 56.28%

Hi there,

I have a query that currently displays all data from certain fields in my database when I run the query.

One of those fields in the query is 'Date' another field is 'Name of student' and the other is 'description of duty'. What this is showing is on a particular date, what students have to do which duties around the school.

I need to create another field in the query which will count the number of students doing a particular duty on a certain date.

So for example:
On the 28/11/2011, there are 5 students doing the duty of cleaning up the common room.

Currently when I run the query this is shown as a date in one field, the duty in another field and the students doing the duty in yet another field.

What I want is another field called 'number of students doing duty' which would show (for the example I gave before) the number 5.

Any help ASAP would be appreciated.
 

A:Record counting in Access 2007

madbull94, welcome to the Forum.
This sounds a bit like School or College course work to me.
This may take more than one query to do what you want, one query to Group the Date and Duty and then a second query based on the first query with the original table added and linked via the Date & Duty fields to count the number of Students.
 

1 more replies
Answer Match 56.28%

Hi. Lets say that i have a table that contains check boxes. My report lists all the checkboxes, and down in the page footer, I want to have a text box that counts the number of checked boxes. My checkbox name is called checkbox and i've tried many variations of =Sum and =Count and can't seem to get it to work. My textbox always ends up showing =Error when I run the report. Any suggestions?

Thanks.
 

A:Counting Checkboxes in Access Report

Have you tried this variation?

=Sum(Iif([checkbox]=True;1;0))

The problem with this is that you probably want a total just for the page in question. Access doesn't do this without a little more complexity than you might expect, but it does do it. This article from MS shows you how it works:

ACC2000: How to Create Page Totals on a Report
 

3 more replies
Answer Match 56.28%

I am trying to use "Count" to count each individual record in a Query. I only have 25 records in the database so far, and each time I run the query record #2 counts as "5". Everything else shows a "1". I have looked at my table setup, and all the records are set up just alike. I can't figure out what's wrong.

Anyone have any ideas?
 

A:Help counting records in Access Query

What kind of query? A Totals Query I hope?
What field are you "counting"?
Are you SURE it's record #2 and not 5 records for that field?
 

3 more replies
Answer Match 56.28%

Access 2000, Windows XP OS

I need to sort records in groups using the fields:

DATE-OUT , EQUIPMENT , LOCATION , INSTRUCTOR,
1/5/05, LCD-Pro, LAB 101, JONES,

and get a count for the equipment used in the location field and a count of the equipment used by each instructor. In other words what I need to know is

1. How many LCDs were used in LAB101 between 1/1/04 and now.
2. How many LCDa did Instructor JONES use between 1/1/04 and now

The DATE-OUT field has records from 1/1/02 thru 6/9/05, I need to use all the records from 1/1/04 to now. I tried using >12/31/03 in the criteria box, but when I use the group by for the LOCATION and INSTRUCTOR I get separate dates. I just need the date range not each date. They need to be sorted by EQUIPMENT, which only matches any reference to LCD in the field and they need to be grouped by LOCATION, and INSTRUCTOR. When I use group by for the two fields LOCATION and INSTRUCTOR I get nothing. Please help. Thanks.
 

A:Access Grouping and counting records

6 more replies
Answer Match 56.28%

I have a report with students
I have grouped them by age and now I want to count the number of males and the number of females and have it show on the report
 

A:Access 2000: counting in a report

I removed your extra post and renamed this one. Next time, you can EDIT this one using the Thread Tools dropdown.
 

1 more replies
Answer Match 56.28%

I have a table with about a dozen Yes/No boxes. The Table has a column for each Yes/No choice. Each user will go to a form with 12 choices and can click from 1 to 12 choices. I would like to be able to have a report for each user that will show the number of selections.

Example:
User #1 picks 4 of 12, User #2 picks 8 of 12, and User #3 picks 12 of 12.
I would then like to be able to run a report that looks at at each row in the table and counts how many "yes" then divides that by the number of choices and displays a percentage.

Output on form would be something like:
User #1: 33%
User #2: 67%
User #3: 100%

What would be the best way to perform this task with me stating that my Access 2007 skills are in the beginner stages? Thanks in advance for any suggestions.

Danny
 

A:Counting Yes/No Boxes in Access 2007

6 more replies
Answer Match 56.28%

I have a database for a senior center membership. Members are from a number of area towns. I am trying to write a report showing the demographics by town. ie gender, age etc. I have grouped a count of members by town, and now I want break that down by age groups. It would look something like this:
ANYTOWN 50-60 60-70 70+
members = 20 10 5 5

NEXTOWN
members = 10 3 3 4

ETC.

I found a function on line that will compute ages from DOB and assign them to age groups. I use this function in a query that gives me the demographic info I'm looking for, including a column named "AgeGrps" My problem is "How do I get a count of each age group by town in the format shown above?" Any help will be greatly appreciated
 

A:Counting Age Groups in an Access Report

Ron, it may take 2 queries to do this, one to put them in to the age groups and then a second one to Group and count them by Town. Or you could possibly use a Crosstab to do the Grouping and Counting.

Can you post a zipped Access 2000-2003 copy of the Database for us to look at?
 

1 more replies
Answer Match 55.86%

Is there a way to count the number of times a certain character appears in a string in access?

I have a field in Access containing the following string: "1_2_3_4_5_6".

I want to know how many times "_" appears in the string.

I tried:

count(instr(fieldname, "_"))

but that doesn't work.

Any ideas?
 

A:Solved: Counting # of characters in string in Access

You can use VBA

Code:
vArray = Split(vString, "_")

vCount = UBound(vArray) - LBound(vArray)
where vString is your string variable
Rollin
 

2 more replies
Answer Match 55.44%

Not totally sure how to explain my problem, so I will start by explaining my goal:

I'm trying to add a new report to an existing database (the creator left the company, leaving no one who knows how to use Access). I've learned a lot, mostly how to mimic the existing items, and have managed to create a few queries and reports on my own...but this one has got me.

I have a "template" Excel workbook which I load into the database using a macro I wrote. It creates two tables, one for a "running total" (this is for a monthly report) and one "temporary" table which I reference for month-to-date stuff (year-to-date queries/reports reference the actual database's table).

This template consists of columns containing our companies divisions, each row capturing a specific transaction processed by that division, and a column identifying whether that transaction was "late" (via ones and zeros). See below mock-up for an example:

The goal is to use this imported data to populate a report, summarizing each divisions performance with a column for each specific transaction as exampled in the following picture:

I'm able to create queries that capture the number of transactions per division...but I'm having trouble translating the queries into a report. After trying for a few hours, I achieved the following:

But I'm guessing I need to build a query that can create a separate column for each transaction type? Or is t... Read more

A:Solved: Access 2003: Grouping and counting for reports

6 more replies
Answer Match 53.34%

Hi.
I have a database used for reporting company purchases. Each purchase type has a 6 digit code. I have been asked to add an extra level of detail to the database to improve the accuracy of identifying the purchases.
There are about 60 purchase types, and I've been asked to add unique 'sub-types' to a handful of those types. In other words I'll have:

Type 1
Type 2
___sub-type 1
___sub-type 2
___sub-type 3
Type 3
Type 4
Type 5
Type 6
___sub type 4
___sub-type 5

...and so on...

Is there a simple way to add this 'next level' to the main table? The outer 'type' field in the main table uses a lookup table (which appears as a drop down menu / combo box on the various forms). Would it be done with some kind of sub-table, or with a group of lookups from the Types table for each Type that has a sub-type?
Am I making any sense??!!

After I get passed that, I'll also have to include the sub-type code field in a number of forms (which already contain subforms).
Ideally, the sub-types that would appear in the drop-down menu would be dependent on whichever type had just been entered - in other words, using the exxample above, say type 6 was selected, I'd like the only available options in the sub-type code drop-down list to be sub-type 4 or sub-type 5.

Then come the queries and advanced reports.... I just need to get this started, so any ideas are hugely appreciated.

Gram
 

A:Sub-tables? Access 97

10 more replies
Answer Match 53.34%

Hi there,

This is probably a dumb question, but I truly have forgotten how to do this. Ergo, I seek knowledge of this forum. I have about ten tables, consisting of some common fields and more unrelated fields. How can I combine them and use the info in queries to create a few forms?

Laugh if you will at my ignorance but give me a few hints.

Thanks in advance,

Zoegirl
 

A:Access tables

Never mind all. I figured it out. I had a brain fart!

Zoegirl
 

1 more replies
Answer Match 53.34%

I have designed a query in Access 2000 which creates a table. I am searching for a solution which would schedule the query to run say every two minutes (making sure those confirmations don't appear "You are about to run a Make Table Query, etc, etc) AND ftp the data up to our website. Is this possible with Access? or a third party piece of software?

Thanks
 

A:FTP Access Tables

12 more replies
Answer Match 53.34%

I'm following some transaction specs to make a table that will capture data to be sent out in a data string to another company. Problem is, there are more than 255 fields, so there would be 2 tables. (Even though the information in some fields is static, the fields can't be merged.) How can I end up with one long data string from two tables? Thanks for any help!
 

A:Access tables

13 more replies
Answer Match 52.92%

This is my 1st attempt at a database. I wanted to record employee info for a manager.

I want a report to detail the grades of employees under a certain manager, but also detail the managers name who compiled the assessment as this can be different. To do this the only way I found it possible was to create a separate table of managers names which I called 'compiled by' as well as a list of managers name to which each employee was linked and was related to dept name.

The problem I have is that when a new manager's name (when setting up the database for a new dept.) is entered via a 'new manager' form I would like the name and their id recorded in both the 'compiled by' table and 'manager details' table. I.e.

I can't figure out who to record the same info from one form into two tables(, or alternatively a more elegant way round using the two tables).
 

A:Access: Writing to 2 Tables

15 more replies
Answer Match 52.92%

I am using a table in a form that based on what is listed in the nomunclature feild I can lock the required feild but not all the required feilds will be locked only the ones I specify. The code I am using is below. Please can you give an advice as to why this code is not working.

Private Sub Required_GotFocus()
If Form!NOMENCLATURE = "12RE59*" Then
Form!Required.Locked = True

End If
End Sub
 

A:Access Tables with locks on

Have you tried using On Current event procedure instead of the Gotfocus?
 

2 more replies
Answer Match 52.92%

How can I link or build a relationship between tables so that I can run a report off of more than one table? I can't follow the help instructions in Access that well and hoped that someone had a simple way of explaining it. I have attached the file for your review. I will be adding on more tables and I want to be able to pull information from each table to build reports.

Thanks,
Kammmie
 

A:Linking Tables In Access

8 more replies
Answer Match 52.92%

I am trying to combine the data from 2 tables both tables have a ID number which forms a "relationship" between the two tables. The "master" table is considerably longer than the subordinate table.
When I create a query using data from both tables, the query only shows records that are common to both tables.

What I'd like is a final result whic has All results from the master table and fills in those values from the other table.

I hope I am explaining what I'm triing to do.
Previously, I was trying to do this in Excel

see this post

The point is I need to maintain the integrity of the "master" table.
Any thoughts???
 

A:merging tables in Access

16 more replies
Answer Match 52.92%

I haven't used Access very much yet, but I'm learning as I go. I took a big Excel spreadsheet and imported it into Access. I can do a query and turn the info I get into a new table in a new database. Before I search any further, I just need to know if my next step is possible to do? I want to put the various query/tables that I create from the original, all into one, so I'll have the original and a new db composed of just the bits I query out. I looked up merging in the help, but only found info about mailmerging into Word. I'm using Office XP.
Thanks!!!

PS. If you can just point me in the right direction....
 

A:Access, queries and tables

7 more replies
Answer Match 52.92%

Hello tech guys,

Something strange is going on with my union tables. For these I tend to create the tables I need in queries and then copy-paste the SQL versions across into a new query (with "UNION" added and ";" removed here and there).
Well, suddenly this has stopped working for more than two queries. I am trying here to put 3 queries together in a union table and as I say, it works for 2, but always on the 3rd I get the message

'MS Access DB engine could not find the object ". Make sure the object exists and that you spells its name and the path nake correctly'.

My code is as follows, can anyone see the problem?

SELECT Query5.[Date Logged], QUEmployeeDetails.Employee AS [User], [Brand] & ", " & [Model] & " (" & [IMEI] & ")" AS Handset, [Provider] & ", " & [Lease Type] & " (" & [Serial #] & ")" AS Simcard, Query5.[Date from], Query5.[Date until], TABPhoneHandsets.HandsetID, TABSimCards.SimID, Query5.Recipient, Query5.CombinationID
FROM ((Query5 LEFT JOIN TABPhoneHandsets ON Query5.Handset = TABPhoneHandsets.HandsetID) LEFT JOIN TABSimCards ON Query5.Simcard = TABSimCards.SimID) INNER JOIN QUEmployeeDetails ON Query5.[Employee Name] = QUEmployeeDetails.EmployeeID

UNION SELECT Query5.[Date Logged], QUStudentDetails.[Student Name] AS [User], [Brand] & ", " & [Model] & " (" & [IMEI] & ")" A... Read more

A:Access - Union Tables

Can you post a zipped copy of the database in Access 2000-2003 format to look at?
I am not very good working SQL, I prefer the Query design view.
 

1 more replies
Answer Match 52.92%

I want to be able to create a one-to-many relationship based on just the first 3 characters of data.
Here is an example:

Table 1 (one table)
"Code" field will contain data with 3 numbers followed by 1 letter (111P, 112C, etc)
Table 2 (many table)
"Code" field will contain just the 3 numbers (111, 112, etc.)

Is this possible?
 

A:Access - Linking tables

Normalize the first table by splitting the code into its number component and text component, creating a multiple field primary key index based on those two attributes. Then create a relationship based on the number attributes.

chris.
 

2 more replies
Answer Match 52.92%

New to Access and am having difficulty with making relationships with access. I have a database which is based off of a form and we use in our company and I created a series of tables:
patient - list contact info
Accidents observation
Mental Status
Ambulation
Transfers
Status Prior to occurence
Equipment involved
factors
Report - list date, time & nursing staff notified
Relative Contact

I'm looking for assistance in relating these tables so information is updated correctly and I can run reports. Any assistance would be helpful
 

A:Relating Tables in Access

How many fields in each table, do you have an accident key field that is used in each table?
If you want help on here it is best to post a zipped copy.
If you want my help send me a private email by clicking on my name.
 

1 more replies
Answer Match 52.92%

I have two identical tables, with different information. How can I import one table into the other table so that it is just one table? It will let me import the table into the database, but not into the other table. Can this be done?
 

A:Solved: Access Tables

Never mind. I figured it out. I had never used the Append Query before.
 

2 more replies
Answer Match 52.92%

I'm trying to link a table..when I open the form to search..it's not linked..where did I go wrong..

I went to:
(1) get external data
(2) link tables
(3) the table..I want to link

my plan is :

1. general table (demographic info)
2. Link HCV-Data1 to general table
so that when you open the general the person's name appears..then open the HCV-Data..to view the data

 

A:Access (Linking tables)

The steps you took are for using a table in another database file as if it was in the current file. From what you describe, I think you are trying to JOIN the tables. That means that you want all records in table B to be related to a record in table A. The steps for that would be to open the relationships page. You can either use the icon in the toolbar or select Relationships from, I think, the tools menu. (I'll have to double check that later but it's definitely in one of the menus.) The table with the general data, (the table on the 'one' side), must have a primary key and the table with the related data (on the 'many' side) must have a foreign key. In other words each record in table A must have a unique ID. The column that holds that ID should be set as the Primary Key in table design view. Each record in the other table must have an ID that corresponds to a record in the first table. In the relationships screen, add both tables to the screen. Click on the field in the second table that contains the ID and drag it to the field in the first table that contains the ID. Click OK in the box that comes up and a line will appear between the two table showing which field joins them. Now when you view records in the first table, you should be able to click on the little plus sign next to each record and view all related records in the second table.
 

1 more replies
Answer Match 52.92%

I am a new Access programmer. Have been a Visual Foxpro user for many years. I am working in a network environment. I am attempting to add fields to an existing table, but I am receiving an error stating that I cannot add fields to the table because it is linked to other programs. I would like to unlink the table. Have tried other suggestions but have failed. Table that I am attempting to unlink disappears. Any suggestions?
 

A:Unlinking Access Tables

9 more replies
Answer Match 52.92%

I will probably embarrass myself with this most basic question, but I am a newcomer to Access.

The question below illustrates the essence of what I am trying to accomplish:

I have two tables in a database. Table A has 2 variables, ID (primary key, autonumber) and Last Name. Table B has 3 variables: ID, Last Name and Age. What I want to be able to do is use a Form to enter ID and Last Name in Table A, and have those data show up identically in Table B. (I can then use another form to add Age to Table B.)

Any help would be greatly appreciated!
 

A:Relating Access Tables

16 more replies
Answer Match 52.92%

we have been using an access database for a few years now and to speed things up i thought i would create a new table for each year and delete those records out of the current data. problem is i didn't check with everyone and now it apears that the old data is still needed to create some reports. how do i add all the old records back into the current year?
 

A:combining tables in access

11 more replies
Answer Match 52.92%

In my db I have a tbl called tblInitial_Cost(500 records). In that table I have two fields, Clin and Clin_Price. These need never to change!

I have another tbl, tblI_Cost that has these fields plus the following; UIC, Quantity.

What I need to be able to do is have a form that brings up the following fields:
UIC - from the tblMain (there are 500 UIC records)
Clin - from the tblInitial_Cost
Clin_Price - from the tblInitial_Cost
Quantity - from the tblI_Cost

This form will be brought up by an On_Click event from my frmMain form. frmMain is bound to tblMain. I need the new form to do a lookup on the tblI_Cost based on the UIC field on frmMain. I know how to do that with code. What I am having problems with is that I need the new form to display every record from tblInitial_Cost, but I also need it to pull up the UIC and Quantity fields from the tblI_Cost.

Once modifications are done to the form, I need it to save the info in the tblI_Cost. The tblInitial_Cost needs to stay the same.

After all of that I have a qry that I will run that will take all fields from tblI_Cost and add an expression at the end that multiplies [Clin_Price]*[Quantity] to give me the disiered count.

I have included several screen shots that will hopefully give you an idea of the data I am working with.
 

More replies
Answer Match 52.92%

How can I use ms access to combine several tables into one table?

Thanks
 

A:Combine several tables into one in access

12 more replies
Answer Match 52.08%

I am a novice to Access; I am presently using Access 2003 with Windows XP (I expect an upgrade to Access 2010 and Windows 7 by the end of April, if that matters).

As part of my doctoral program I am trying to create a form with multiple tables in order to input the test results for various individuals at 1-5 time points from 1-12 tests (each test producing results in a number of areas). My master table (‘Demographics’) holds the primary key as “ID” that is related to a table named ‘Test Sequence’ (holding a primary key “SequenceID” and the foreign key “ID”) that is then related to each of the individual tests each have “ID” and “SequenceID” as foreign keys.

The idea is that a single individual could have each test (with its multiple resulting values) done over a period of time but no more than 5 distinct times. Therefore, Mr. Smith could come in for Test Retake 2 to complete Test F which outputs 12 values. So, Mr. Smith could have Test Retake 1 thru 5 and Test 1 thru 12, if my math is right he could have possible results posted in 60 different combinations and each of those combinations would need to hold the actual 12 test result values.

I have managed to create the form (using the form wizard) that included the 3 tables listed above (Demographics, Test Sequence, Test) but I am stuck with data entry to hold the data to the individual and then to the individual over the various retest time periods with the variety of tests.

Any help would be very much apprec... Read more

A:Access Dat Entry in a Form with 3 Tables

16 more replies
Answer Match 52.08%

Hi gang,

I have several tables in Access to dedupe by phone numbers within the sample - the other fields contain different info in the different rows (e.g. John Smith, 123-4567 in one row and Mary Smith, 123-4567 in another), however, so asking for "First" in a query does not get rid of them. Because of the size of the samples, I don't want to purge them manually.
What do I do?
Thanks in advance!
 

A:Solved: dedupe Access tables

8 more replies
Answer Match 52.08%

i'm a real novice.... I imported 2 excel files to access, table a and table b. table a has rows of Vin #, customer name and address. table b has rows of vin # and email address. I want access to compare vin #'s and combine tables so I have name, address and vin# in one table.  help........
 
joe

More replies
Answer Match 52.08%

so my issue is i have two tables i have been using to keep tabs on the 75 pcs i have here

the one is the hp workstations we purchased, the other is from these non branded workstations we build.

the primary key in the two data bases is the name of the workstation... there are no duplicate names
i could also use the IP addresses as the primary key...

i want to take the two tables and marge them into one output that updates and grows when i add more pcs to the two tables. the third should automatically update and grow.

any halp would be appreciated.
i know there is a way to do this i just cant recall it.
 

More replies
Answer Match 52.08%

I put an Access 2000 mdb on a CD, opened it on a machine with Office 2003.
When I open the mdb, I get warnings about "...expressions that will harm your computer..." !? Anyway, I've updated everything MS has, Jet 4.0, and all service packs.
When I open the mdb, I'm missing tables and forms and who knows what else.
What happened? How can I fix em?
They are there when I open the CD on my Office 2000 machine....
 

A:Access 2000 to 2003 - no tables?

7 more replies
Answer Match 52.08%

I’m back with yet another of my loony database questions. Again, I’m setting one up and need a connecting thought , if you will. Basically I’m working with a couple of tables for a contact management-type database. It’s more complex, however, because I need to relate the tables by using an auto number field, I believe. Here’s the scenario:

A parent/client comes to us for service. We collect contact information such as address, phone number, etc. and the parent/client is automatically assigned a number in a specific format. (Actually, the “number” could be a combination of numbers and characters that mean something to us – more complex.) Now, this parent/client may have children that might (or might not) require service, too, and I want them to have a unique number but still need to relate them to the parent. These children may have different last names (which could change) so they can’t be related that way. I’m looking at a main form/sub-form for entry but need that magic connection so I can query statistical information based on unique numbers as well as information based on related numbers. Does this make sense? I’ll need to know, for instance, information based on an individual client but also the client’s children. I want the unique number automatically assigned but in a specific format that would distinguish a parent from a child yet relate the parent to the child (or children). Maybe something based on the Dewey Decimal system: Parent: 4.0.0 Child: 4.1.0. o... Read more

A:Access 2000 - relating tables

6 more replies
Answer Match 52.08%

Hi friends, I have some Compiled VB6 projects installed on my Windows 7 laptop. (Mostly copies of Software supplied to the clients) They would be working fine, then all of a sudden, may be after weeks, or months, I get an error when launching the software 'Unrecognized database format' On checking the database manually, I see one or more tables are disappeared. Any ideas? thanks in advance.

A:Tables disappear from my MS Access database

What version of Access are you using. In Access 2013, you can repair & compact the database by clicking on File at the top LH corner & when that opens you will see under Info, Compact & Repair, click on that.

Compacting the database will definitely reduce the size it uses on your hard drive & usually speeds up the opening time as well.

You could also try repairing Office. To do that open Control Panel, select Programs & Features, scroll down to Microsoft Office, Right Click on it & select Change, that will open a couple of Repair opens to try.

1 more replies
Answer Match 52.08%

Does anyone know why numbers on a report would come out different than the numbers in a table?

For example, the table is set up to show 35 as $35.00. On the report it comes out as 35 (no dollar sign or .00).

Any reason?

Kristy
 

A:Access Question Tables/Reports

The report doesn't know that it is a currency field unless you specifically have it set in the table as such. Otherwise you have to specify in the report the format you want to see.
 

3 more replies
Answer Match 52.08%

Hi guyz,
Having trouble with access tables,
Working with a program, created in VB for access.
Within this program there is a browse button created for browsing folders related to people within the database. i.e. everyone in the databse has thier own folder on my server.
However there is a linked table called mastervalues relating to what program to use for browse button, (Explorer) and the path of where all the folders are held.
On the other hand though, i also have an unlinked table which is called mastervalues1
this table holds exactly the same values as the linked table.
To be perfectly honest i have no idea why there is a linked 1 and 1 unlinked as i didnt program the software im using.
Now that you know what im dealing with i'll get straight to the point.

Using 5 workstations in this small office, 1 was recently upgraded to XP from 2000professional.
The path entered for this table to browse the folders for each person in the DB, no matter what machine its changed on, obviously because its linked changes on each machine, however i want each machine browsing to a different folder. So the path has to be different on each machine.
The recently upgraded machine is the only 1 this works for, as it takes the path from the unlinked table, however if i try to delete the path from the linked table on all the other machines, and put the path into the unlinked table.
When Browse button is clicked i keep getting an error message "folder cant be found, please contact admini... Read more

A:Solved: Access linked Tables HELP!

Why can't you just overwrite the Folder path in the "mastervalues" table with the new one?
The unlinked "mastervalues1" is just a copy of the original, when you "import" a copy of the table it can't have the same name as the original so Access adds a 1 on the end of the name.
 

2 more replies
Answer Match 52.08%

I wish to combine two tables--Sales and Addresses--into one table.
Then I would like to create a form for this new combined table.
The common field to each of the tables is BuyerNumber.
There is no primary key in either table.

When I use a query to create this combined table, and use Make Table, I do get a combined table.
Then I create a form using the combined table.

However, in the form, when I type in the BuyerNumber, I want the info from the Addresses part to automatically fill in--but it doesn't. I've tried DLOOKUP but that doesn't seem to work.

I want what I type into the form to show up back in the combined table. It doesn't.

Any suggestions?
 

A:Solved: Access--combining two tables

9 more replies
Answer Match 52.08%

I am trying to perform a query on two tables that will only return the date when the date field in both tables is the same. When I look at the two individual tables I can see the date field is the same in both but whether I do a JOIN or WHERE the querry is not returning any data. I have checked to verify that the date field in both tables are defined the same way. Any ideas what I am doing wrong here? Thanks
 

A:Comparing dates from two tables in Access

15 more replies
Answer Match 52.08%

I have a database that is made up of 2 tables, Job and Customer. I want to set up a form that lets the user search for an existing Customer and then add a new Job for them. I have the search form set up, and that works, but I don't know how to set up a form to add a new record to just the Job table and have the new record still joined to the customer name in the Customer table.

Hopefully my explanation makes sense, and has a fairly easy answer. Thanks for your help!
 

A:Access - Updating joined tables

Well... I'm not sure the answer qualifies as "fairly easy".

You should first spend a little time looking at the Northwinds and Solutions db apps that are delivered with Access. They have examples that are similar to what you're trying to do.

The short answer is to make a subform on your search form. This search form should be linked to the the main search form through your CustomerID field; when you add a new record, you'll need to verify that the CustomerID is being set correctly. The easiest way to manipulate this stuff is using code; I'm assuming you're not familiar with VBA, or you wouldn't be asking.

You can do it using a macro. You add a CustomerID field to the subform, make it hidden, and then use the BeforeInsert event of the subform to set that field value to the master form value.

You'll be sure to let us know how much of this sounds like greek.
 

2 more replies
Answer Match 52.08%

Hi friends, I have some Compiled VB6 projects installed on my Windows 7 laptop. (Mostly copies of Software supplied to the clients) They would be working fine, then all of a sudden, may be after weeks, or months, I get an error when launching the software 'Unrecognized database format' On checking the database manually, I see one or more tables are disappeared. Any ideas? thanks in advance.

More replies
Answer Match 52.08%

Has anyone discovered that they were unable to changed the location of their linked tables? I have recently come on this problem and can't find any solution. I am trying to do this in VBA code and broke it down into what whould be a straightforward change.
Code:
Function test()

DoCmd.SetWarnings True
CurrentDb().TableDefs("Job").Connect = ";DATABASE=C:\Jobs_be.mdb"
CurrentDb().TableDefs("Job").RefreshLink

End Function

I get no errors when I run this code but the tabledef.connect string just wont change at all. Even when stepping through line-by-line. Neither database is set as read-only. I checked that all tables and databases exist and are spelled using the proper case. Nothing seems to work!

If anyone could shed some light on the situation I would greatly appreciate it.
 

A:Access: Changing Linked Tables VBA

Did you do a compact and repair? Sounds like your sys tables are messed up
 

3 more replies
Answer Match 52.08%

we have a database that is being updated via a webform (this is all done by an outside company, so i have nothin to do with it).

basically, the database has lost about 500 rows from its main table. The data still exists in the file (it can be seen using notepad), but the database wont show it.

I've tried repairing the database, as well as exporting to external formats, but the hidden rows remain hidden.

are there any 3rd party utils available to load mdb files, or some descriptions of the file format (i'll write my own one if i have to)? does anyone have a clue how to sort it?
 

A:access 97 losing data from tables

How big is your database megabyte-wise? There are also other limitations to Access like table size, tables open at one time. Has the datatype for the table been changed perhaps? Is there a way to view a sample of what you are seeing, maybe you could just send the tables with no data in them, or an example of the data. Who's the outside company? Maybe their security measures are lapse and someone broke in.
 

1 more replies
Answer Match 52.08%

I obtained an old MS Access 97 db that contains "Linked Tables". How do I find the location of the source tables?

The database is on our Network... and I can't find the source tables (which are in another Access db).
 

A:MS Access 97: Location Of Linked Tables

With the database open, try the Tools menu >> Add-ins >> Linked Table Manager

If that add-in isn't already there, you may have to add it yourself.
 

2 more replies
Answer Match 52.08%

trying to access fields of the table but not able to this....tried these codes..

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

Set Rst = CurrentDb.OpenRecordset("TableName")

For Each f In Rst.Fields
MsgBox (f.name)
Next
Rst.Close
End Sub

'-----------------------------
Sub FieldNames1()
Dim fld As Field
For Each fld In db.TableDefs(tbllist.text).fields
fldlist.AddItem fld.Name
Next
End Sub0

thanx in advance
 

A:Getting Field Names from Tables in MS Access

What was wrong with the first function?
 

1 more replies
Answer Match 52.08%

I can't seem to figure out how to get the data together into one table, one report for each. I hope I can explain this right.

I have 3 tables from a seperate database that I am using.
Table 1 - is the Item Master. I only need the Item # and Item Description from this table.
Table 2-3 all have Item #, Year, Warehouse fields in common. But then they each have different fields
Table 2 has Qty Received Period 1, Qyt Received, Period 2.... and so on.
Table 3 has Qty Sold Period 1, Qty Sold Period 2..... and so on.
Table 4 has Qty Issued Period 1, Qty Issued Period 2... and so on.

I need to have one record be combined for a Item #, warehouse and year - and have tables 2-4 data in the same record together. (This is where I am having issues) Do I use the aggregate group function? Is Access the best way to do this?

Then I need to have the final product be something to have the user pick the year, pick a single or multiple warehouses, and pick single or multiple Item #.
 

A:Access - how do I combine records for these tables

You need to break this down in to single steps at a time.
Create a query of the Table 1 data that you need, then add just table 2 data and get what you need. Then add table 3.

What version of Access are you using?
 

3 more replies
Answer Match 52.08%

Hi. I have a main form that has three command buttons, each of which opens a table. Is there a way to set the default size of the window that opens when I click on one of these buttons? In other words, is there a way that I can do this and still efficiently utilize my window space in order to see both most of the form and the table that pops up?
 

A:Auto-resize tables in Access

I am not sure that I understand what you are doing, it is not "normal" in Access to directly Open a Table as that gives users direct Access to all of the data.
Normally on would open a "Subform" on your current (Main) Form, or you could have a "Tabbed" Mainform and show the Subforms on the Tabs.
 

2 more replies
Answer Match 52.08%

Is there a way to have a field in a form linked to multiple tables?

I have a form that is used to enter data into a Basic Data Table, it works just fine. However, I want the form to recognize one field called (Project) which is a text (lookup) and enter certain fields into multiple tables. The project field can have only the values ABR, CBR, Grass and SWLK. Additional tables are ABRUnits, ABRCosts, CBRUnits etcetera. The ABRUnits table will then be used to calculate the ABRCost table.

If I was working in Excel I could do this no problem but I lack the VBA knowledge in Access to build a command button to do this.

Any help would be greatly appreciated
 

A:Access 2010 Forms and Tables

BJKrukowski, welcome to the Forum.
You should not have to use VBA to do what you want, just get the Table Designs and Relationships correct.
 

1 more replies
Answer Match 52.08%

Hello everyone,
I hope someone can help me.

I have an excel form with 50 columns. I have a code that grabs specific columns from the excel and transfers it to my access database.

2 columns from excel come into access in a table called "EO".
Another column comes to another table in access called "EO2"
I need this columns to be merged into one table if possible. The 2 tables do not have any columns in common but both tables have the same amount of rows.

table EO table EO2
name | code amount

i want the final result to be a table that has all 3 columns: name code amount

any help would be greatly appreciated
thanks
 

A:Solved: Access merge tables help

8 more replies
Answer Match 52.08%

I have a MS Access file (*.mdb) and I want to redesign it. When I open it I can only see the form view. I need to see the tables too. In the Menu Bar nothing is available and I can only enter data in this file.
(Actually its a tool database that a colleague made for me but I don't like his design.)

Can it be done?
 

A:Solved: View tables too in MS Access

6 more replies
Answer Match 52.08%

Hi All,

I have been advised to avoid lookup tables like the plague in my database. (http://www.mvps.org/access/tencommandments.htm)
If I have a field in my record which I want to limit to 1 of 40 different values, what is the alternative to using a lookup table?

Thanks
Simon
 

A:Solved: Access Lookup tables

You still use a Table for the values, but instead of making the Main Table field a lookup, which ties it to the lookup table, you use a Combo or List box, (which is "bound" to the field on the form), on the input Form that is based on the Lookup Table. Preferably via a query which can sort the data for you first.
You should find examples on the Databases that I sent you.
 

1 more replies
Answer Match 52.08%

OMG. Hopefully someone can point me back onto the right track. I am attempting to create a database (Access 2000) to keep track of a visitors log. I created a table for each month, and entered the information for each day into tables. Table has about 6 fields with the last field being hyperlink to original document. Each month has same fields. (perhaps this is the problem) I would now like to create a report which includes all the months. I know there should be relationships but there are none. When I click on relationships tabs it only brings up one table. O dear. Please tell me I didn’t spend hours entering this data incorrectly. Should all the data be in one table? P.S query doesn’t work either.
 

A:access 2000 relationship & tables

8 more replies
Answer Match 52.08%

I need to create a reporting system that will read an access table and create a final report in excel format. I want to write vb code in excel that will allow the user to select one of several tables ( a separate table for each of 15 categories of information), and also a date or date range. i want to be able to retrieve that subset of data into a working worksheet and do various excel functions on that data to get the info for the report. then i will create the report. i have another reporting system that i will borrow from to do the summarization on the data for the report. i only need to know how to write the code to open an access file - i will have the path needed - and import only a subset of the data in that file -- that is for a specific date or date range -- i tried by just opening the file and it does create a query but it brings in the whole table...and using the real data tables that will be much too much data to process....thanks.

 

A:Read Access tables from within Excel

Check out this link - Chris does great VBA (see post 6):
http://forums.techguy.org/business-...um.html?highlight=Export+from+Access+to+Excel
 

1 more replies
Answer Match 52.08%

Hello,
I have Access 97 running on this machine. When I try and open a database created at work on I think Access 2003 on this machine it gives me a "invalid forma" message and I cannot open it, is there any remedy for this? thanks alot...dano2
 

A:"Invalid format" when trying to open up MS Access 2003 tables in Access 97

You'll have to open it in 2003, and hit Tools-->Convert... to 97
 

1 more replies
Answer Match 51.66%

I am trying to figure out if I should setup more or fewer tables. I have what I think is a rather complicated database. I am setting up for a shelter that has residents admitted and discharged. I have demographic information on the women admitted, their children, where they came from, etc. then I have discharge information, ie like where they go, their status, etc. Is it best to setup a separate table to archive off the people when they are discharged?

Then I also I want to survey these folks 6 months, 1 year and 1.5 years after they leave and I want to keep track of that as well. I can't figure out if the survey info should also be kept in a separate table(s). Is there a source to help me with setting up the number of tables. Much info is still 1:1...Any direction would be much appreciated. Some examples I see setup separate tables for things that I would just have in a single table...I'm a bit confused about the pros and cons of setting up more or fewer tables.
 

A:Access: How to setup tables for survey info

Depends on information.

Where they come from: Is this something that you could choose from a list? Then make "Came From" a separate table. Otherwise, don't.

Where they go to: Is this something that you could choose from a list? Then make "Went To" a separate table. Otherwise, don't.

Surveys: One separate table with a field that links to person ID; and another that indicates whether info is for survey 1 or A (6mos), survey 2 or B (12mos), survey 3 or C (18mos); and then your survey fields.

For guidance: http://support.microsoft.com/support/access/content/reldesign.asp
(if you see the webcast--I haven't seen it and would love to know if it's any good)
 

3 more replies
Answer Match 51.66%

Hello - I am a relatively new Access user. I am using a previously developed Access database to obtain data about on-time performance for a bus system for statistical analysis. I have one table that includes the time that a bus arrived at a particular timepoint - simplified version of fields:

Date
Bus Route
Timepoint location
Time arrived

I need to link other data to this table, such as the weather, ongoing incidents, etc. However, all of this data is based on date ranges. I need to be able to connect two tables based on if the "time arrived" in the first table falls between two times in the other table. Here is a simplified setup of fields as an example:

Date
Time Begin
Time End
Wind speed
Amount of rain

I have tried to join them in a design-view query. My SQL skills are limited, so any help for resources would be appreciated.
 

A:Solved: MS Access - linking two tables using a range

transitmama, welcome to the Forum.
There are 2 ways to do this, one uses 2 queries and the other just uses one query, so we can try the Single Query version first provided by Jimmy The Hand.
Basically iwith the Query in Design View you add the second Table to the query without any Join between the tables.
Then in the Date field's First Criteria Row put the following
Between [daterangetable].[Date1] And [daterangetable].[Date2]

Where daterangetable is the real name of your Table that use Date ranges.

If you could post a zipped copy of your database in Access 2000/2003 format (Less any real personal data) I could help you more.
 

3 more replies
Answer Match 51.66%

Hello,

I have multiple tables that has info as such:

table_1:
f_name | l_name
bob | simth
john | doe

Than I have another table (same database)
table_2:
f_name | l_name
mike | brown
me | stupid

How do I combine them so I get:
table_All:
f_name | l_name
bob | simth
john | doe
mike | brown
me | stupid

Any help is appreciated
 

More replies
Answer Match 51.66%

Hey folks!

I need help big time. I am in the eleventh hour of creating a really complex database for a buddy of mine and I am in need of a special task.

I need a form the user can use to create a whole NEW table with predefined fields.

For example:

The user needs to track a particular brand of item. So with this db they will have a whole table just for that brand of item. Lets assume the user has a new brand they need to add so they go to this form, they select a name for the table and then the user clicks a create button. Then a new table is created with fields that have been predefined by me. Then using another form the user can make changes to they new table.
What I need help with is how I can use a form to create the table.

I hope what I am asking was a little more clear than mud, lol.

Thanks in advance for any help!
 

A:Access 2007: Creating tables using a form...

12 more replies
Answer Match 51.66%

Hi All, I have two Tables in same Access Database file. Table 1 has fields: ID, Cardno, CustomerID,Date,Frame,Prescription, and TABLE 2 has fields CustomerID, Name, Age,Address,Town.. As you can see the only common field in both Tables is Customer ID. I want to combine the fields from TABLE 1 and TABLE 2. Such that it displays me CustomerID, CardNo, Name, Date, Address,Town.. How can i do this..Either in one of the two tables, or create a third table if need be... I tried QueryWizard, it shows customerID as the common field between both tables, however on clicking RUN, it says Type Mismatch in Expression.. I am not technical person, nor have i used Access before, so please answer in simple ways, illustrations would be easier, we have 300,000 data in both tables and need to search Patient records cant go back and forth between one table and other..so thus need of one table displaying all data.. Thanks..
 

A:Combining 2 Tables in access 2007 into one Table

7 more replies
Answer Match 51.66%

Hi all,

Major thanks in advance for all you can do to help--

I am trying to create a small, simple db to track volunteer work using Access 2003 (although it says something about 2000 all the time- compatibility mode?).

I used to do database development stuff all the time, but it's been 7 years and i can't make my brain work as well as it used to.

I've set up two tables with the associated fields:

tblContacts

IDNo (unique ID - key) - Number
FName - text
LName - text
Address - text
City - text
State -text
Zip -number
email - text
Active? Y/N

tblHours

IDNo
Date
TimeIn
TimeOut

The tables have a one to many relationship using IDNo.

Now, what I'd like to do for the data entry person is create a form that will allow her to select the volunteer name/ID (preferably by last name, drop down list, but I am flexible), and have the FName, LName info appear. Then the data entry person would put in the date, timein, and time out info, and the form would write the 4 fields (one from tblContacts, three from the form) into tbl hours.

Can anyone recommend how to do this or an easy alternative way to do this? I am trying to allow the user to look up people by last name, but I can't use that field as the unique ID since they repeat (alot). but I want to keep my back end simple, so am trying to make the Hours table just the ID.

does this make sense?

Thanks so much, y'all,

E
 

A:Access 2003 form and two tables question

7 more replies
Answer Match 51.66%

Hey,
is there anyway I can populate the tables in a Microsoft Office Access 2007 Database using C++?

Thanks in advance
 

More replies
Answer Match 51.66%

I have 9 queries (which I can convert into tables) that I need to have added to an excel spreadsheet onto certain tabs. I am using Access 2010.

I have read different ways of doing this either through a macro or linking them.

When I try to do the macro it puts me into the GUI screen and i am not sure how to get to VB to manually enter a macro.
I will probably be adding more queiries once I can get this step down. I am trying to create this for a speed for another user.

Any suggestions?
 

A:Access query/tables to Excel workbooks

I would suggest "Linking" them would be the obvious choice.

I do have some VBA code for transferring multiple queries to different sheets in a workbook, but it is a fair amount of work to set up and may be temperamental.
 

3 more replies
Answer Match 51.66%

My database contains three main tables:

tblContacts – with fields: ContactID, LastName, FirstName, Email, Telephone
tblCourses – with fields: CourseID, CourseName, CourseLocation, CourseDate, Examiner, NoOnCourse; there are separate lookup tables for the fields for CourseName, CourseLocation and Examiner
tblCoursesAttended – with fields: AttendedID, ContactID, CourseID

On a form, I want to show the courses that each contact has attended (if any) – this information must show (i.e. be visible on the form) the CourseName, CourseLocation and CourseDate, and I want to be able to add more courses to this.

For example:

Joe Smith has attended Maths in London on 04/05 May 2009 and English in Manchester on 10/11 June 2010.
Penny Black has attened Maths in London on 04/05 May 2009, English in Birminham on 23/24 February 2010 and History in London on 03/04 March 2010.
Bob White hasn’t yet attended any courses.

I now, say, want to add a course to Joe Smith’s record.

I just can’t set this up to show what I want. Am I over-complicating things by having an Attended table? I have tried using a query, using a subform, using a combobox, and using a dropdown. Nothing has worked. If you could point me in the right direction – explaining with db-baby speak – I will be eternally grateful.
 

A:Access form - adding information to tables

16 more replies
Answer Match 51.66%

Hi,

I am trying to learn how to use Access properly for tracking. I have created a table with a unique i.d. for each record and will have around 60,000 records. I will have several tables, one at the moment, containing information for some of the records in the main table. I have created a form with the information from the first table and the second table. I linked and/or made a relationship between the tables. When I open the form it only shows the records that have information from both tables have in common. In other words, it only shows the records that are on the second table. I want the form to show all 60,000 records but include information from other tables. Every table I will create will have the ID. I also want to be able to input information into the form and have it update the tables, so I hope this will work.

I hope you can understand what I am trying to explain. I will try to be more specific. Each record in the main table is a fish. A variety of tests are run on each fish, each test is a different table. I want a form which shows information on each fish and each test run on that particular fish.

Please help. My frustration with not understanding this is running...how can I grasp excel but not access. grrrr
 

A:Access 2007 using multiple tables in form

6 more replies
Answer Match 51.66%

Hello, im new to this website and was looking for some help in Access 2007 with the criteria section of a query ive made.

Here are the basics:
The query will be used for a report,
I want to view all 'services' of "BS" or "LLC" in a given date (parameter) on a single report.
There are currently only two tables on the database and each table has different fields - apart from the Service Type (BS or LLC) and Service Month.

I would like to know if it is possible to view all of the "Service type's" in a given month in a single report, is possible.

P.S) Fairly new to Access, i knew the very basics a week or so ago but i am a quick learner, also i am not experienced in VBA/SQL however for a simple problem like this i am sure i can use it to implement it.

Thanks in advance.
 

A:Solved: Access 2007, Criteria from two tables.

9 more replies
Answer Match 51.66%

Hi,

I have made a copy of my database and saved it to a flash drive. I want to be able to clear out certain fields in the tables that contain sensitive information so that I can work with another consultant with out privacy issues being compromised.

My question is...
Can I delete the data in those fields (using the copy database only) and not affect the live database? I'm afraid that if I alter the copy on the flash drive while on my desktop it will affect the live database which the tables are linked to on a server. Will the data on the copy know or link to the live tables on the server or are they not connected once I save a copy? Is there a way to "un-link" the tables on the flash drive so that they do not affect any live data?

Normally I wouldn't hesitate to play around and figure it out for myself but I can't take that risk with this data. Thanks for any help anyone can offer!!
 

A:Copy of Access database and linked tables

If you have live links to your server in the database and tinker with it away from the server but then log that file back in, you would alter those tables. However, it is possible to break the links by opening the db, copying the table that is linked and going to Paste. You will be offered a choice of several options - paste as a linked table (no), paste structure only (no), something else(I forget, but no) or paste structure and data (yes). You will have to call it something else for the moment - later, you can rename the new files to what the old linked ones were called so that all your dependencies/queries/forms/reports/macros work.
When you are on a non-networked PC you can do what you want with these new tables.
 

1 more replies
Answer Match 51.66%

I've tried searching in this forum, but I either didn't find any, or I'm using the wrong keywords.

What I'm trying to do is get information from Table 2, with information from Table 1, but the criteria is that they if the (e.g.) names match, change the [address in Table 2] only if the [date in table 2] is more current than the [date in table 1]

As far as I can tell from the help files, they only provide comparisons between a given date #DD/MM/YYYY#, which means I can extract thru a query dates before, after, between a given date.

E.g. (Dashes just makes the table easier to read)

Table 1
Name Address Date
Aa-- 123---- 2008-01-01
Ab-- 234---- 2008-01-01
Table 2
Name Address Date
Aa-- 987---- 2008-02-01
Ab-- 876---- 2007-01-01
Query -->
Table 3
Name Address Date
Aa-- 123---- 2008-01-01

This Big database contains over 3/4 mil, and the dates range from 2002 to end of 2007. And I have about 7 smaller databases to compare and update with.

If anyone can point me in the right direction, it would be very much appreciated.
 

A:Access 2007 Comparing Dates from different Tables

7 more replies
Answer Match 51.66%

I am creating a Task Tracking Database. Using a command button on a form one “Add New Task” and another “View or Edit Task,” they both are using the same form and the form’s Record Source is tbl.Main. “Add New Task” I want it to go to a blank form to enter new task (next available #). “View or Edit Task,” I wanted it to start at record 1,so user can click through task to find the one they want to edit.
Do I need to copy form and name them 2 different names? If this is what I need to do, how would the data get updated in both forms simultaneously? I have tried to link them and that does work. Or can I have one form and code it to go to next available # for “Add New Task,” then code it to go to 1st task to “View or Edit Task” and how would I do that. Any help would be appreciated.
Thanks
 

A:MS Access-2 identical tables needing to update each other.

9 more replies
Answer Match 51.66%

Hello, I am using Windows 7 and MS Access 2007. I have 2 tables. Both tables have the "NAME" field. I also have the "OK" field with data type of "Yes/No" in the second table. These two tables have different records of NAME. But sometimes, these NAMEs match each other. What I want to ask is, how can I link these two tables so when there are records with the field of "NAME" that match in two tables, the field of "OK" will be filled with value "Yes". Otherwise, the value is "No".

Any help would be appreciated. Thanks.
 

A:Solved: (MS Access 2007) Linking 2 Tables

9 more replies
Answer Match 51.66%

i've got a few calculations for fields based on other fields. I've managed to run these calculations properly in Forms and they are stored in the formatasheet view, but not written to the underlying Tables. i there anyway i can create a bound field with calculations in a Form? any ideas please....urgent!! thanks
 

A:Access 97: Writing Calculating data to tables

Love your name.

Ok. Don't do the calcs on forms.

Do a query with your table fields. Add fields and create expressions for your calcs. Create the form based on the query instead of your table.

You don't NEED to store calcs in tables....that's the whole idea. All calcs are done "on the fly" (when you run the query, form, or report), so there's no need to store them in tables. You're just wasting space.

Understand?



Let me know.
 

3 more replies
Answer Match 51.66%

I have a huge problem. I'm currently creating an Access database at work. The old system included a couple of reports that featured pivot tables. The only way I was able to create the pivot tables was by using a data access page in Access2000. However, I can't get the pages to print. The database is supposed to be distributed throughout the company on April 2nd. But, these reports need to be in working order. If you have any comments or suggestions, please let me know. Thanks.
 

A:Access: Pivot Tables, Printing Problems

11 more replies
Answer Match 51.66%

I would appreciate any help anyone could give on setting up related tables in Access -- it is my first time doing so, and I am not sure if I quite have the hang of it.

It is a database of software companies and their partner companies. The two tables (simplified for this forum) are:

Table 1: (Software company info where each row would be info for a different company)
Software_ID (Autonumber)
Software_Company_Name
Locations
Table 2: (this contains information about partner companies)
Partner_ID (Autonumber)
Partner_Company_Name
Partner_Company_City
Software_Company_Name1
Software_Company_Name2
Software_Company_Name3
My questions were:
1) In table 1, if I have multiple fields for locations (like Austin and Boston), is it wise to have multiple columns like: Location1, Location2, etc? Or is it better to have only one column for Location and then repeat the Company Name for each location?

2) As Table 2 shows, each partner has relationships with multiple software companies. I wasn't sure then how to relate the two table together

3) How exactly would I relate the two tables? Would the data in the fields have to be the same for them to be related?

Thanks so much in advance!
 

A:Advice on constructing related tables in MS Access

14 more replies
Answer Match 51.66%

04/21 - Moved question to this forum from Development Forum​
Workstations: 13 XP Pro systems
Server: Win 2003

Currently using MSAccess 2k database for client listing and associated notes, etc (one-to-many / tbl_client to tbl_notes). We recently purchased a software package, and I believe I read in the literature that it is built on sybase platform?? In talking to the company about migrating data, one of their techs stated that they Export access tables to csv comma del format, bring the new systems table open in foxpro, append the data from the csv files. He said that foxpro accepts the tables better, and keeps the table in a correct format for the commercial program to read/use them.
So I contacted a friend who had Visual FoxPro 6 on his laptop, and he let me use the laptop for a short period of time.
I exported a few of the MSaccess tables to csv format, and then opened FoxPro.... BIG SURPRISE...
Never used or even seen screen shots of FoxPro, so when it opened up, I guess I was expecting something "similar" to access or whatever, but that was not the case.... Foxpro uses a command window, and, ... well Im lost.
I opened one of the commercial programs tables in foxpro and it took me a few minutes to look at the data currently in their table, figured out it was some "browse" command, but absolutely - totally different than what I was expecting.
QUESTION: How do I append data to existing tables, from csv files in FoxPro
QUESTION: Is there some down ... Read more

A:Access table data INTO foxpro tables

8 more replies
Answer Match 51.66%

Right this one is seriously picking at our tiny brains ...

We have created a report in Microsoft Access and realised halfway through that we needed to add a table to the report. So how do we add this table to the report without having to start again ? ?

Tell me its something grossly simple and that its only the effects of 10 hours at work stopping us finding it

Thanks guys

Marc
 

A:Adding tables to already created Access reports

CreATE A join query using both tables, you need to drag a link to the related fields (KEY). If it automatically joins them make sure it is related data other wise delete the join. Add all the fields from the first table and some (or all) from the second

Eg if table 1 has an item code

and table 2 has transaction history by Item

then join the 2 fields, keep in mind that you may need to join more than one related field, study the data, if you see duplicated records then you need to refine the joins

Then go into the report design and change the control source to the query. The report should still display as it did with the table, but now you will have th additional fields.

You have to be careful because in my example above is a One to many scenario, so if you want One to one you may need to make a summary query first to restrict the data .
 

1 more replies
Answer Match 51.66%

Hello all,

I am wondering what the best approach is for updating a table that is the main table in a form and is related to 1 other table. The situation is that every two weeks I receive an Excel spreadsheet that contains ALL CURRENT employees within our organization (~950 rows). I am then taking this spreadsheet and creating an Access table out of it. But, every two weeks there's a new spreadsheet probably with some employees no longer on the list because they have left the firm and some new hires now on the list.

Is there an easy way to update this list without negatively impacting the relationships that this table has with the other table, queries, and a form?

currently I am looking into the Append and Delete queries.

Thanks in advance for your help.
 

A:Solved: Access 2003 - Updating Tables

10 more replies
Answer Match 51.66%

Hi, I have 2 tables

-Masterfile
-New Data

What i need to do is match all the data in the "New Data" table to the data in the "Masterfile" if a match is found then do not append that record in to the masterfile. Is this possible using queries?
 

A:Access 2003 Matching Data in 2 Tables

10 more replies
Answer Match 51.66%

Hello,
Thanks for reading. I would like to create one form that will enter the same data into multiple tables. Example: tblContractor and tblContractor Contacts. I want to enter the Contractor Company Address, City, State, etc. from Contractor Contacts Form and poplulate the same fields in the Contractor table.
Can you someone show me how to make this happen?
Thanks so much!!!
Stephen
 

A:Solved: Access 2007 - One Form to Many Tables

16 more replies
Answer Match 51.66%

I have a problem creating an Access form for data entry into multiple tables.

i am running Win XP/Home SP 2 (the entire version description is Version 5.1 (Build 2600.xpsp_sp_2_gdr.070227-2254 : Service Pack 2)).

The Access version is 2000 (9.0.2720)

I am trying to create a form for data entry into fields for 3 different tables. I searched this forum and found nothing relevant. I have not found the sample databases helpful. Although reasonably skilled on applications such as Word, I am a novice with databases.

I am trying to learn how to accomplish this task without using the wizards. I first tried to create the form without using a wizard. Here is the sequence using Create from Design View:

Click on New/Design View

Right click on Form space (not Section Detail space)

Click on Properties/click in Record Source field and then on Table 1 from the drop down box.

The Table 1 field names show up in the Field List window.

Drag desired field names to form

Right click on Form space (not Section Detail space)

Click on Properties/click in Record Source field and then on Table 2 from the drop down box.

Drag desired field names to form

Right click on Form space (not Section Detail space)

Click on Properties/click in Record Source field and then on Table 3 from the drop down box.

Drag desired field names to form

The form looks acceptable in Design View. In Form View, all the fields are displayed, but the last Record Source selected (in this case, Table 3) has been applied ... Read more

A:Problem with Access forms using multiple tables

16 more replies
Answer Match 51.66%

I have a application that was developed in MS Access 2003, VB modules with database tables linked, queries, imported EXCEL spreadsheets and the DB is in a MDE form for the user. I am the new admin/developer. I added 2 new codes to a master table, that is linked to other tables. this table contains a master key, document code associated document name, import flag (checked) and an active flag (checked) These new codes (document name) are typed in a spread sheet that gets imported in the application into the main input table, they import into the main input table correctly with the correct document name, this table is not linked to anything, but the other linked tabled that use this information once in that application to and display to the user have have the incorrect document code on them, it is using a document code, form other existing records in the table and not the document code associated with the imported document name. I have tried updating all the links to the master table, and still no luck, can some one tell me what is wrong. Thanks
 

A:MS Access 2003 Application with Linked Tables

Can you Rephrase your problem, I am not sure what you are asking or where exactly your problem is?

How are you updating? have you tried relinking individually and going directly to the file location and cliking on it? Refreshing links by selecting all at once will not always work
 

3 more replies
Answer Match 51.66%

originally had 1 big table with 190 records. i had to split it to 2 tables with a relationship set on a specific field. i modified my form to combine the contents of both tables via my relationship. as i went back and added 10 more records, these records can be found/seen directly on my first (original) table but they were not inserted in the second table.
on my form, i can only see up to the original 190 records, and NOT the last ten records added after the table split.
is there something that i need to do to my form to go beyond 190 records?
 

A:Access Form is not showing all records in my tables

EVM welcome to the forum. You will get a quicker response to Access questions on the Business Programs Forum.
You sound a little confused over the use of the second table, it's data will not be automatically updated when the first table is updated on your Form. You need to have form, usually a Subform on your mainform to update the second table.
 

1 more replies
Answer Match 51.66%

To all who use Access,

I'm currently having a problem trying to find a way to disable cut and copy in Access tables.

I'm currently using the KeyPreview and KeyDown code to nullify the Ctrl+C and Ctrl+X input in one form, to disable the user from mass duplication of the information displayed. However, the problem I face is that VB can't be used on tables to prevent copy and cut.

My other alternative was to use the "AutoKeys" macro to disable ^c and ^x. But I realised that this is a program-wide disablement.

My idea is to have datasheets (tables and split forms) have the Ctrl+C and Ctrl+X function blocked, while other objects can still enable the Ctrl+X and Ctrl+C for ease of data replication (i.e. copy and paste the company name to save time typing the value every time).

Would anyone have an idea on how to go about this matter? Would it be possible to use the macro "AutoKeys" or 'KeyDown' response with a condition that tell what tables/forms/reports it should apply to?

Also, just as an extra, I've already locked the navigation pane so that no one can delete or rename the objects in the database.

Thanks! Your help will be much appreciated.
 

A:Solved: Disable cut and copy in Access tables

Resolved:

I decided to use a combination of LockNavigationPane, AutoKeys and KeyDown to control the flexibility of Ctrl+C, DEL and Ctrl+X.
 

1 more replies
Answer Match 51.66%

Hello,

Iam trying to create a from where i can insert data in multiple linked tables.
But iam having some problems ... I tried to had some subforms, but the values aren´t uptade from one subform to the other.

Can someone tell me what is the best way to insert data in multiple linked tables using a form.

Thanks.

 

A:Problem Access - Form with multiple tables

8 more replies
Answer Match 51.66%

Of all of the years I've worked with Access, I've never needed to do this; well here I am being asked to get a list of field names from a table. I'm aware of a few ways (set a list box to be field list, and using the db analyzer). I also found some code that doesn't work to read the database fields and dump them into a file (I'll paste to this note - apparantly I'm not running the correct version of ADO to use it).

Any help would be greatly appreciated. It dies on a "User-Defined type not defined" error inside the funtion at "Dim MyDB As New ADOX.Catalog"

I found the code on a developer article, but not sure I'm implementing it correctly. I feel like I'm out of my league on this one, but willing to give it another shot.

Thanks in Advance

Jim
Code:
Option Base 1
Public Sub Enumerate_Table()
On Error GoTo ERROR_PROC
Dim aryFields()
Dim lngCount As Long
lngCount = 0
strInput = InputBox("Please enter the name of the table for which" & vbCrLf & _
"you wish to list FieldNames & Descriptions." & vbCrLf & vbCrLf & _
"Output will be placed in tab-delimited text file.", "Table Name Input", "MainTabl")
If StrPtr(strInput) = 0 Or Len(strInput) = 0 Then
Exit Sub
Else
strSQL = "SELECT * FROM " & strInput
Dim Adofl As ADODB.Field
Dim rs As Ne... Read more

A:Solved: Getting Field Names from Tables in MS Access

6 more replies
Answer Match 51.66%

I have a problem combining 2 tables in access. The two tables are linked to a third joint table. I need to sort the data, calculate the sum of some values in each of the two tables and collect them in the same query but not in the same column.
I'm pretty new working with access and may not be explaining my problem proberly so I constructed a simple database (attached: Test 2) but very similar to the data I'm working with where I've made a query where my problem should be fairly self explaining.

Hope someone can help me with this I'm sure easily solved problem
 

A:Solved: Access 2007 - combining 2 tables

16 more replies
Answer Match 51.66%

I spent almost a whole work day trying to get my database to convert to an MDE file only to find, once I succedded that, besides a slight speed increase, It did not solve the major problem for the change in the first place. I want 2+ people to use the same form at a time and I thought that making an MDE was the solution to that.

I have a screenshot of the error that I got and will post it as an attachment (very nice new feature).

The tables the form access are:

- Jobs for a combobox
- Jobs to display information
- Jobs & Change Orders & Purchase Orders & Job Details in a subquery of this form

Please help me. I almost got violent trying to fix this thing.
 

A:Access 2000 - Forms Locking Tables

6 more replies
Answer Match 51.66%

Maybe it is just late in the day and the brain has quit. But I need help. I have a form that I have to base on two tables. So I wrote a query to pull the fields needed from each table, since visually trying to use sub-forms could get very messy. Now the problem is how to I save any of the data the user enters/changes in the form. In other words, I want to be able to update the appropriate table, but can't remember how to do that.

Thanks in advance for any help.
 

A:Access 2000 Form based on 2 Tables

For Access to update changes using information in both tables, there needs to be a relationship between them. Although you have created a query using fields from both tables, unless they are linked Access won't know how to treat the information
 

2 more replies