Best Way To Adjust Poorly Designed Table/query

Nov 5, 2004

I'm going crazy!! Someone who didn't really understand relational database theory (not like I'm an expert, but. . .) built a DB that is much more complicated than it needs to be. That person left and I got the job. I have to work with our IT people to hopefully redesign it completely (I would without asking, but they control the SQL server and the connection to the web.) but in the meantime, I have to work with a mess.

Okay, the question--I have to print a report of which organizations used which software company.

The table fields are:

Organization
Comp1
Comp2
Comp3
Comp4

etc.

the answers are a "1" for a yes and a "0" for no in the "Comp" fields, so there is an answer in every field of a record. Essentially, it's a spreadsheet.

Off the top of my head, I know I could query each Company name with the court individually and then union query them all together. But isn't there any easier way? We're talking about 15 queries if I do it that way!

Anyway, thanks for any help you can give--this is driving me crazy (over 40 table set up in similar disarray. . . .)

View Replies


ADVERTISEMENT

Table Was Designed Wrong

Jul 13, 2007

I have this Access application. The Incidents table was created incorrectly. apparently the design was not thought over. The Incidents table was created to store employee accidents. However, since the employee data is based in nicely normalized data, all Incident reports will have the employee's PRESENT data, instead of the Department, Cost Center, Location, etc., that the employee was at the time of the incident. I want to de-normalize the Incidents table and add the Department, Cost Center, Location, etc., that belong to the employee at the time of the incident, I have added the columns to the table, but I am having problems getting the data from the normalized tables to the Incidents table in the Click event of the AddIncident command button. I have tried to create queries, but can not get them to work. I was unable to send the mdb, but attach is the AddIncident event. In the Fields statement, I want to get a field data from another table. For example, the present Employees table, which always has the employees present info, contains the AutoNumber Key to the rest of the normalized tables, Department, Cost Center, Location, etc; I want to get the actual Department name, Cost Center number, etc, and put it in the Incidents table. The Employees table has the data I want for the time of the incident. But the employee may change Department, Cost Center, Location, etc, before his next incident. :confused:

View 4 Replies View Related

Tables :: Transaction Table Setup To Adjust Inventory

Apr 22, 2013

I have a database that I use to put in orders for our shop and keep track of our part informations as well as paint and packing materials. Everything works good on this but I am trying to create a table for adjusting quantities on hand for packing material based on the part quantities and for adjusting paint in stock based on information given to me after the job is run.

There is already a relationship between parts and packing material as well as parts and paint. When I put an order in I would like to have it adjust out that many packing materials that are related to that part. Once a job is run I need to be able to adjust out the amount of paint used.

For the paint side of this I want it to track the paint used by order, we are trying to get a grasp on how much paint we are using for parts so it is important for me to know how much and when.

I am thinking I need a table that connects paint to orders and has quantities in it, then create a query and do the calculations from there... I do not know how to accomplish that but it sounds like it could be right...

View 1 Replies View Related

Using A Form To Query/adjust Subform

Aug 21, 2004

First off, I'm really new to all this so I'm a little overwhelmed. I know what I want but may not describe it well.

I'm just trying to get a basic issue to work.

If I have 2 databases, one which has names, and another which has information - in memo form. What I'm trying to do is create a form which will allow me to bring up a persons name and have the subform query the information database for all information (events) which have this persons name listed inside.

I have tried both filters and queries and while I can get everything to work if I type in text in the queries in and/or filters if I try to use the value of the name in the main form nothing ever works...

Thanks...

View 4 Replies View Related

Check In Is Not Designed To Be Opened Without A Filter

May 16, 2015

What should I do? I really don't know what filter is that.

View 4 Replies View Related

Error Message On A Form Designed As A Menu

Apr 3, 2008

I have designed a form to act as a menu to open other forms. I created the form and placed command button on the form. Using the Access Wizard, the following VBA was created by Access to open the next form:

Private Sub cmdCCDolClr_Click()
On Error GoTo Err_cmdCCDolClr_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmCCUS_Dollar_Clearing"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdCCDolClr_Click:
Exit Sub

Err_cmdCCDolClr_Click:
MsgBox Err.Description
Resume Exit_cmdCCDolClr_Click

End Sub

I have done this several times in the past with success. However, this time, I get the following error message when I click on the command button to open the second form.

The expression On Click you entered as the event property setting produced the following error: A problem occured while GCM (name of dB) was communicating with the OLE server or Active X Control.
*The expression may not result in the name of a macro, the name of a user-defined function, or [EVENT PROCEDURE].
*There may have been an error evaluating the function, event or macro.

I tested other forms that were designed to open secondary forms and they continue to act correctly. Is there an easy fix to this issue. There are 16 command buttons and all get this message. Do I have to delete the form and recreate it. A nusiance, but not difficult.

Looking forward to hearing from you experts out there.
Thanks, Alan
__________________
Alan Sidman

View 3 Replies View Related

Printing Labels And Reports On Different Printers Than Designed

Oct 18, 2011

I have a small receiving inspection database that prints lables for each item received. However when they use the program on a different computer the lables do not print on the lable printer at that location.

View 1 Replies View Related

Adjust Margin With Vba Code?

Jan 10, 2006

I know in access 2003 i can use:

printer.leftmargin = 0

but in access 2000 there is no printer object. Is there a way of setting the margin with vba from access 2000?

View 5 Replies View Related

Modules & VBA :: How To Adjust File Attributes

Jul 9, 2013

Below is code to adjust file attributes.

Code:
Public Shared Sub Main()
Dim path As String = "c: empMyTest.txt"
' Create the file if it exists.
If File.Exists(path) = False Then
File.Create(path)

[Code] .....

View 3 Replies View Related

Queries :: Adjust Column Width To Best Fit Using VBA

Mar 20, 2013

How to adjust the column width of the fields to best fit in a Query using vba codes? Manually it can be done by selecting the Query - Click on More dropdown of the Reports section in the ribbon - Click on Column width - and select best fit as the option.I want this to happen by a vba code and not manually doing it everytime because the field length changes as it is a dynamically created Query.

View 3 Replies View Related

Make + And - Adjust Date In Field

Jun 12, 2012

I am trying to make the + - (numpad) adjust the date of the current text field. The problem I am having is I believe the input mask intercepts the keypress and my keypress event is never fired. Here is my code:

Code:
Private Sub LeaseDate_KeyDown(KeyCode As Integer, Shift As Integer)If (KeyAscii = 107) Then
LeaseDate = DateAdd("d", 1, LeaseDate)
End If
If (KeyAscii = 109) Then
LeaseDate = DateAdd("d", -1, LeaseDate)
End If
End Sub

Access makes the ding noise when I try and press + and - ...

View 4 Replies View Related

Forms :: How To Individually Adjust Width Of Controls

Jan 17, 2015

I have created a form using Create|Form.

I have now opened it in Design view and wish to adjust the width of the controls - text boxes etc but when I try to adjust one, all of the text boxes in that column are adjusted.

How do I individually adjust the widths?

View 8 Replies View Related

Reports :: How To Adjust Text For Subforms With Dynamic Heights

Jun 27, 2014

I've created an Access Report of a letter to be printed and mailed to customers. The majority of the letter is text, however there is a subform in the middle that will have a wide range of records displayed in it. This causes the subform's height to adjust from 1/2" to several inches, depending on the number of records. I need the text below the subform to start just below the last record in the subform, no matter how many records are displayed in the subform.

Access 2010

View 3 Replies View Related

Modules & VBA :: Adjust Required Field - Space Bar Character

Aug 30, 2013

How can I adjust my required field so that you cannot simply enter a space bar character to circumvent the requirement?

Here is part of the code:

If IsNull([txtRequiredReason]) Then
MsgBox "The Reason field is required"
DoCmd.GoToControl "txtRequiredReason"

Else

View 1 Replies View Related

Modules & VBA :: How To Adjust A Sample Database To Suit Specific Needs

Oct 29, 2014

I would like to make some changes to the Northwind sample database.I need to have the Customers section completely removed as my company issues products to our own staff, not to external customers, so there is no Customers and shipping details needed.

View 2 Replies View Related

Reports :: Adjust Section Width To Avoid Blank Pages

Nov 18, 2014

I have set up a report in Access. However, when I look at it in print preview, I get the message that the section width is greater than the page width, and that some pages will be blank. It is obvious that the Detail Section is the issue, but I can't figure out how to reduce the section width. The detail section only contains a few text boxes that are well within the margins of the other sections.

How do I adjust the section width so there won't be any blank pages?

View 6 Replies View Related

General :: Adjust Cell Size According To Length Of Data In Datasheet

Jul 9, 2013

I have subform (Datasheet) is there some code e.g. after update, which can adjust size cell to lenght of data?

View 11 Replies View Related

Forms :: Make Frame Automatically Adjust To Image Width And Height

Aug 7, 2013

Is it possible to make it so that whatever the dimensions of the image i paste the OLE object's frame will also be the same? I'm having a problem, not necessarily a problem more like a preference, I'm inserting pictures of each individual inside a database and whomever last took these pictures didn't make sure there was a standard size So all the images are different sizes and when they're put inside the OLE frame you can see the image inside it but the frame itself also shows its white background because the image isn't the full dimensions of the frame. I was thinking if it's possible to make the frame automatically adjust to the image's width & height.

View 3 Replies View Related

Tables :: Find Record Button Added To A Form To Adjust Search Function

Apr 6, 2015

Is it possible on the Find Record button added to a form to adjust the search function so it defaults to a specific box on the form? I have a form for tracking employee's and on my Find Record button I would like it to default to the Last name instead of the Record number.

View 3 Replies View Related

Query Returns Results From One Table/query But Not From Other Table/query

Mar 2, 2007

Advanced apologies for the long question.

I have a table providing a list of all members of a local bowling club, with the following fields - MemID, MemName (table is tblMembers)

I also have two more tables providing details of matches we play in a local bowling league. The first table holds the basic match information (called tblMatches) - MatchID, Date, Opponents, ScoreFor, ScoreOpps. I've used a MatchID field as more than 1 match can be played on a single date.

The other table provides details of who played in what match and on what bowling rink and is called tblMatchDetails, with the following fields - MatchID, MemID, Rink (numeric), Result (numeric - 1 for win, 0 for loss). Each match uses different rinks, and not all rinks are used in a match, however, each rink can only be used once in a match.

We have 10 rinks, and I was looking to see if I could get a table to show me the match details along with the rinks in numerical order with who played on them in what match and their result.

For example:

Field Row: Date - Opponents - ScoreFor - ScoreAgainst - Rink 1 Player - Rink 1 Result - Rink 2 player - Rink 2 result - [and son on upto] - Rink 10 player - Rink 10 result.
Row 1: 01/01/2005 - Example BC - 4 - 3 - [blank] - [blank] - Fred - 1, etc
Row 2: 08/01/2005 - Other BC - 5 - 2 - John - 0 - [blank] - [blank], etc

I produced a query for each rink in turn which looked in tblMatchDetails and provided the following (the queries are called qryRinkOneDets - or whatever rink numebr was used):

MatchID - PlayerID with a criteria field of rink = "1" or whatever number rink it is for.

I then produced a second query providing a link of the matches (tblMatches) and the rink queries (qryRinkOneDets, etc), with the joins between the MatchID fields in tblMatches and the individual queries to provide every record from tblMatches and only those records from qryRinkOneDets where MatchID was equal.

However, the results I got only showed the match details and no info from the Rink query. If I change the join from RIGHT to LEFT, then I get the records from the Rink query but not from the Matches table. If I then do a join where only the MatchID's from the table and query are equal (an INNER join) then I get no records at all.

Can anyone figure out what I'm doing wrong?

Many thanks in advance

View 3 Replies View Related

Extracting Data From Linked Db2 Table Using Access Make Table Query

Aug 29, 2006

Hi,
I am extracting data from linked db2 table using access make table query. First I create a select query and can view the linked db2 data, but when I change to a make table query I get an error message, "invalid argument", when I run the make table query. There is no selection critera specified. Has anyone had this happen? and Do you know a solution?

Thanks for your support
GinnyP

View 1 Replies View Related

If A Make-table Query Result Is Null, How To Have A Default Message Appear In Table ?

Jan 21, 2005

Hi everybody,

Beginner here needs help !

I'm building a make-table query for which if the result is null (no record correspond to the set of criterias), a default message like "there was no activity during the period" would appear in the table (not a message box...I need the message in the output table). The best I could think of is an IIF function but it doesn't seem to work... Is there any way to do this without using VBA?

Thanks in advance !

View 1 Replies View Related

Queries :: Update Query (table To Table) Not Updating All Records

Nov 26, 2013

I'm using an UPDATE query to update records in one table (tblMain) from another table (tblTemp)

Here is my SQL :

Quote:

UPDATE [tblMain]
INNER JOIN [tblTemp] ON [tblMain].[MainField1] = [tblTemp].[TempField1]
SET [tblMain].[MainField2] = [tblTemp].[TempField2];

I only want to update the records in tblMain which have a corresponding record in tblTemp (linked by MainField1 / TempField1)

If any record doesn't appear in tblTemp, I want tblMain to retain the existing value for that record.

However, it appears that in such situations, the record in tblMain has it's MainField2 value set to null / ZLS.

I've tried using LEFT JOIN and RIGHT JOIN and also tried WHERE clauses but the result is the same every time.

View 3 Replies View Related

Queries :: Join Multiple Fields From One Table To Same Table In A Query

Nov 21, 2014

I have a table that is basically a survey form. The same series of options was available for 35 questions, and the table used to have a text string written for each answer. Because of all the repetitive data, I created a second table that assigned a number value to each of the nine possible options in these 35 separate fields. What happened is that, instead of the same text strings repeated over and over (and taking up real estate), now each of the 35 columns had a single number in them.

Now comes the day of reckoning and TPTB want a query with the raw data and the original text strings back in instead of the numbers. I was thinking doing something along the lines of a DLookup, but I can't seem to make that work in a query correctly. Apart from calling the same table and linking it over and over to the different fields in the original data table (see photo for how insane that is).

View 2 Replies View Related

Can I Attach An Append Table Query To A Field In A Table

Jan 26, 2006

Hi,

I have a field in one table that needs the sum from fields in another table.

How do I go about doing that?

Thanks,

Trena

View 1 Replies View Related

Changing The Name Of The Created Table In A Make-Table Query

Sep 23, 2007

Dear Access Expert

I wanted to know if it is possible to change the name of the Table which is going to be created using a Make-Table Query via code (VBA).

For example if my Make-Table query currently creates a table with the name "Table1" I want to change it to name "Table2" and then change it Back to "Table1" or "Table3" etc.... depending on the users selection.

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved