Tables :: Checking Table Layout?
Apr 9, 2013
We have an application which is used in a couple of offices and on a number of terminals. The data is stored in a back-end database on the server on each site.The databases are getting to the stage that we cannot roll out changes to the back end table layouts relatively efficiently. What I want to do through vba if possible is basically have a blank local version of each table and say:
check tab_1 on linked_backend, if tab_1 layout <> tab_1_local layout then update the back end layout to mirror that of the local layout.
We cannot overwrite the data, but on occasion, we need to add an extra field to a central table as new bits of the application are developed.I've tried a few searches here, and am probably using the wrong phrases, but not getting any results.
View Replies
ADVERTISEMENT
Feb 12, 2012
Have a look in the attached screenshots
I'm wondering why the relationship interface is still missing many tools that perform the work more efficiently. The interface misses the following (comparing with what we have in the ArcGIS software)
1. Pan tool
2. Zoom in/out too
3. Zoom all tool
4. Automatic arrangement
View 2 Replies
View Related
Apr 11, 2007
17099Good Morning
I was wondering if someone can please assist/advice. I am currently a bit stuck and feeling like I am walking into a dead end the whole time.
I am currently creating a DB with the purpose of Capturing Potential Candidates provide them with an ID. Fill in their basic details Name Surname, submit to a report, which I will then export, email to them, they will fill in the outstanding information requested, send it back and will import it back into the DB with my various queries to run searches on candidates for placements.
My only problem is that I have quite a lot of fields...
So I Decided to Create 4 Tables
In each table I am using the same ID for a person so should be a 1 to 1 Relationship.
1 General Information
2 Info Relating to Skill
3 Work History
4 Qualifications.
I have all of these separate tables due to the fact that with all the records in one i exceed my 255 table fields allowed
But then if I decide to create a query so I can run some of the details i get errors because there are to many fields. Even if i run one on the other. My last one will still have to many fields.
Basically I am hoping someone can just assist in what would be the best way for me to lay out my tables. And what would be the best to link these tables.
Is it better to have all the records horizontally, or as a one to many with duplicates.
Please find attached a copy of the tables. I would really appreciate any advice
Regards
View 3 Replies
View Related
Jun 27, 2005
I am working on a complex database to store Business information as well as specific into about that Business... here is the tricky part...
The individual companies are sorted by Business type (i.e. Computer Software Companies, Child Care Providers, Commercial Building Contractors, etc.) and each Business type needs to hold a simi-customized set of information (i.e. Commercial Building Contractors needs Total AZ Billings for Commercial Bldg., $ Awarded for Completed Contracts, % of Work Subcontracted, No. of Local Employees, and Areas of Specialty). I will refer to each type of information as a Category. Each Business Type has between 4 and 7 Categories. Within a Business Type there are between 10 and 30 companies listed. There will be just over 200 Business Types listed within my database structure, as well as around 100 unique types of Categories.
Primarily, I need to be able to query and format each Business Type, to include the Companies information as well as each Category that is associated with the Business in a flat spreadsheet style layout.
Now, I intend to create a unique report (if needed) for each Business Type as needed to customize the look (i.e. column widths and any other special formatting needed) of each Business Type,
Here is where I stand currently with this project…
I have 4 tables,
1. Business (it includes basic contact info that is common to all business)
2. Category (is basically a list of all possible categories)
3. BusinessCategory (a Many-to-Many relationship container that links the BusinessID to the CategoryID and includes the corresponding Value)
4. BusinessType (a list of business types that a business can belong to)
This structure seems to work in that it can handle the customization of categories for each Business within a BusinessType. The problem I am running into with this structure is creating a query to handle the data and create a “flat” table for a BusinessType with each Business and it’s categories.
It may be that I can use this structure to do what I need done, however I think that there must be a better way to structure my data to work the way I need it to work. I really appreciate your input on this!
View 2 Replies
View Related
May 2, 2007
Hello everyone.
Im just wondering if anyone can find any problems with my table layouts or relationships are anything else?
The ER-D can be found here:
(URL address blocked: See forum rules)=17320&d=1178117475
Thanks, Ant...
View 3 Replies
View Related
Jan 22, 2008
Hey all.. Been out of the Access loop for quite some time now.. Last project I did was in Acc97, about 5 years ago! Been working on SQL Server and web programming lately...
Anyway, I have a project that needs to be done in Access XP. It is a basic resource allocation DB. Listing of employees, projects, and time associated with those projects per Employee. I want to keep this normalized, and so I have this basic table layout..
tblEmployee -- Has EmployeeID, name, etc
tblProject -- Has ProjectID, name, etc
tblEmployeeProject -- Has EmployeeID, ProjectID, year, month, hours
tblWorkingHours -- Has an autoNumber ID, Year, Month, Working Hours, Holidays, business days
Basically, the tblWorkingHours has 12 entries per year, Jan-Dec.
The tblEmployeeProject is where I have the meat of my data.. If an employee(1) is assigned to a project(123) for 20 hours a month for 3 months(Jan-Mar 2008), then there will be 3 rows in there.
EmployeeID - ProjectID - Year - Month - Hours
1 - 123 - 2008 - 1 - 20
1 - 123 - 2008 - 2 - 20
1 - 123 - 2008 - 3 - 20
This seems to make sense to me, and seems to be normalized. However, I cannot seem to create an updatable query on this layout... I would like to pick a project, and then a few employees assigned to that project, and then enter in the hours for each employee, for any of the 12 months I need. The problem I'm having is that I cannot seem to get an updatable query out of this layout. The only way for me to get a listing of all employees attached to a project, and then all months for the year, regardless of an entry in tblEmployeeProject, is to use a Cartesian Product type join on tblEmployee and tblWorkingHours. And then do an outside join on tblEmployeeProject. However this is obviously not updatable.
The only other option I can think of, is to prefill the tblWorkingHours table with 12 entries per year, however this seems to add unnecessary data in the database. At that point, I might as well create a table that has 12 columns, one for each month.
Sorry for the long-winded post, but wanted to try to get all my points across. Any help would be greatly appreciated! Thanks all.
View 1 Replies
View Related
Oct 3, 2014
In the case of a system that keeps track of checking in and out (e.g. library books), where is the archive data stored? If a person makes 30 trips to the library, obviously one record will be the current visit - but where do you keep the 29 other visits?
Do you create a separate table for old check-outs: Current_Check_Out and Archive_Check_Out?
Or do you just leave the data in place and as-is: All_Check_Outs?
View 9 Replies
View Related
Sep 2, 2014
I want VBA code that will perform the following steps. The Table1 has following fields:
ScanDate,NewBatchNo,BatchNo,PolicyNo
1. First of all look into Table1 the scandate<=29082014 and then check if there is No NewBatchNo of the corresponding record then capture its corresponding BatchNo of each record whose scandate<=29082014
2.Then check for the batchno that we have captured in another table Table2 and if its present then return corresponding PolicyNo. Now keep on searching that policy no in Table2 as it could be present 50 times in the table and then return corresponding batch numbers in excel sheet of that Policy No as below:
BatchNo - Policy No - Batch No1 - Batch No2 - Batch No3 ---------------
View 1 Replies
View Related
Jan 3, 2006
ok basically im creating a quote system that will show users prices from one destination to the other destination based on the size of the vehicle..
you can see a similar quote system used on the following website..
http://www.londonairporttaxi.co.uk
there will be three main tables..
Airport Table
airportID - primary key, autonumber
airportNAME - text
the airport table will consist of 5 records..Heathrow,Gatwick,Stansted,London City, Luton
Town Table
TownID - primary key, autonumber
TownNAME - text
the town table will contain a list of postcodes, maybe 50 or so (n1,n2,n3,n4,e1,e2...)
Car Table
CarID - primary key, autonumber
CarNAME - text
the car table will consist of 4 records..Saloon,Estate,MPV,Executive
now what i need to do is create a similar quote system like there is on the url above..
my original idea would be to do something like this..
Have three tables that contain the quotes for each type of car..the first two listed here will work i think..
Airport2TownQuotes Table
quoteID - primary key, autonumber
carID - foreign key from car table
airportID - the airport Pickup, foreign key
townID - the town Destination, foreign key
price - currency
Town2AirportQuotes Table
quoteID - primary key, autonumber
carID - foreign key from car table
townID - the town Pickup, foreign key
airportID - the airport Destination, foreign key
price - currency
however.. for airport to airport quotes.. the resembling table would look something like this..
Airport2AirportQuotes Table
quoteID - primary key, autonumber
carID - foreign key from car table
airportID - the airport Pickup, foreign key
airportID - the airport Destination, foreign key
price - currency
but you cannot place the airport id twice in the same table..(this maybe a case of simply renaming the airport fields in this table such as airport1, and airport2.. but can you still link them and enforce integrity if the fields have different names?)
also one example may be Heathrow to n1 is £30... this does not mean however that n1 to heathrow will be £30.. this is why i need to separate the quotes in this way.. for the 3 possible journeys (airport to airport, town to airport, airport to town).. also the quotes will be different for each car type..
anyway.. i shall be using asp to create a similar quote system on my website.. but i just need to get the final layout of the database sorted.
thanks in advance for any help.
View 7 Replies
View Related
Dec 12, 2005
I'm running the following line:
dbs.Execute ("DROP TABLE [tbl_No Certs]")
the problem is that sometimes the table doesn't exist... Instead of trapping the error code, is there a way I can check to see if the table exists? If it doesn't I'll simply skip this step of my code and move on.
Thanks!
View 2 Replies
View Related
Dec 13, 2005
I have tried the attached code from a previous post http://www.access-programmers.co.uk/forums/showthread.php?t=98727&highlight=table+exists but some of the code doesn't work on Microsoft Access Project.
Does anyone have any ideas how I would amend this code so that it works?
Regards
Carly
View 2 Replies
View Related
May 12, 2013
I have made an unbound check box, named "myckeck" in a form which shows data from a table in tabular layout.
1- When I check one check box, all check boxes are checked. How can I solve that? I mean I want to check or clear check boxes independently.
2- I made a text box, named "jobdate" to sow todays date automatically upon checking a check box.
Code:
Private sub mycheck_afterupdate()
jobdate = date
End
But this is not working and checking a check box does not make anything to happen.
3- Then I made this expression in the default value of "jobdate" properties:
iif( mycheck = true, date(), null)
This not working either.
View 3 Replies
View Related
Mar 21, 2014
I have 3 tables:
- tbl_Positions {containing information on a specific job role}
- tbl_Office {containing information on an office}
- tbl_StaffDetails {containing information of all staff in the company, including a relationship with tbl_Office to say which office they are working out of}
I want to do a simple query saying what type of staff are working out of each office:
e.g.
If in Office 1 I might have a managing director, CEO and 2xOperations managers etc.
If in Office 2 I might have 5xAdmin, 3xHR, 6xIT Support etc.
What sort of query can I run to return just a list of job titles for those present in the office I choose.
View 2 Replies
View Related
Aug 17, 2005
Hi all. I want to check for not null value constraint for all tables in database . I tried the following but when i run it i get the following error:
Compile error:
Invalid Next control variable reference
I be happy if some one one help me fix this error.if i remove the bold lines the program workes well but it does not put not null for table fileds that requries value.
picture of the output without the bold part
http://i5.photobucket.com/albums/y1...007/notnull.jpg
Code:Option Compare Database''This module displays field name and type in a massage boxFunction ShowFields(pTable As String) As StringDim db As DatabaseDim rs As RecordsetDim I As Integer''Dim j As IntegerDim n As Integer''Dim NL As StringDim strHold As String, ST As String''Dim x As Variant''NL = Chr(13) & Chr(10) ' Define newline.Set db = CurrentDbSet rs = db.OpenRecordset(pTable)n = rs.Fields.CountReDim x(n - 1, 1) As String'''ST = "Create Table " & pTable & vbCrLf''adding Create table and table name to statementST = "Create Table " & pTable & vbCrLf & "("For I = 0 To (n - 1)For Each fld In tbl.Fields ST = ST & rs.Fields(I).Name & " " & FieldType(rs.Fields(I).Type) & "," & vbCrLf If fld.Required = True Then ST = ST & " NOT NULL" & " " Else ST = ST End IfNext Irs.Closedb.CloseSet db = NothingShowFields = ST '' returns the fields name to main functionEnd Function
on click even code
Code: For Each T In db.TableDefs '''Skip the system tables If Left(T.Name, 4) <> "MSys" Then ''' this line determines the primary key of the table ''' by calling GetPk function from module pk = Left(GetPK(T, db), InStr(1, GetPK(T, db), "<-") - 1) cont = cont & ShowFields(T.Name) & vbCrLf & " primary key " & "(" & pk & ")" & vbCrLf & ")" & vbCrLf End If Next T ''' This line of code post the generated table statment to a massage box MsgBox cont
View 1 Replies
View Related
Jan 7, 2015
I am developing a form where students have to enter their ID number but I want my form to check through a table of students and their IDs and flag up if the ID number does not exist in the table.
View 10 Replies
View Related
Sep 15, 2005
There is a text box in a form that user can enter the value.
How can I check if the value entered by user is already existed in the record of the linked table or not?
If the value (or record) do not exist, that new value/record will be added to
the table.
If the value do exist, just show the msgbox to indicate.
How can I do that ? :confused:
Thanks
View 1 Replies
View Related
Oct 26, 2005
I have subforms that are datasheet view, if the user rearranges the coloumns, or resizes etc , I would like to save what they have done so it is as they left it next time they open up the screen.
This works fine with an mdb (all my forms are set to close acsaveNo).
However in an mde this doesn't work.
Can I get this to happen in and mde.
(A button that saves a layout change would be fine for my purpose)
Thanks
Paul
View 1 Replies
View Related
Mar 16, 2006
Hello Everyone,
I'm pulling my hair off with this situation. I have an excel file that was given to us by the payroll department of our company, but my manager doesn't like the layout in which was given to us. I've attached a sample of the file, the file has two sheets the first one is how the original file looks like and the second sheet "final look" is how my manager want the file to look.The original file is pretty big, it's for 33 different employees and it contains 93,500 rows. I think it will take forever to do this manually, any ideas would be really appreciated.
Thanks,
Pablo
View 3 Replies
View Related
Mar 1, 2007
I had a couple of forms go corrupt in one of my databases this morning, which has around 50 or so tables in it. I repaired the database by creating a new db then importing all the db objects from the original and the 2 corrupted forms from yesterday's backup.
However, the relationships table is now a disorganized mess. Is there some way to import the layout of the relationships table from the original db or am I stuck with manually re-arranging?
View 4 Replies
View Related
Nov 20, 2007
Hi all,
How should I do if I want to display my records in Report by 3col x 5row instead of 1col x 15row.
View 3 Replies
View Related
Jan 13, 2005
I have an issue with a database layout and I am not sure which is the best way to proceed.
The problem is in an invoice part of a db . When a service is delivered, say 2 hours of a technicians time, it has to be assigned to a particular tech. No problem when there is only one tech however there could be two or more techs who have helped to supply this particular service and the time ( and income) needs to be divided up between them. However the invoice can only show one item the ie total time taken to deliver the service ( quite likely to be a fixed cost for a particular service ).
Possibilties I have considered are: to have a number of fields in the invoice detail table , one for each tech, however I would have to assume a maximum number of possible techs per job and also there would be a lot of redundant fields and trying to analyse how many hours each has worked would be painful.
Each tech could be assigned a code ( single number or letter ) and these could be stored in a single field . The code would then have to be manipulated to work out how many techs worked on each item and how much each had worked. Again this is rather messy.
I think the ideal would be for each tech to have their own table where each time an service was invoiced which they helped delivered the appropiate percentage is assigned but I cant quite see how to do this.
Any ideas or examples gratefully recieved
View 1 Replies
View Related
Aug 12, 2006
Hi,
I created a sub-form using the wizard; however, I can't get it to view as Calumnar even when I move its text boxes about and change their order. When I run it, it always show as a datasheet!
Any suggestions will be very much appreciated.
Bee
View 2 Replies
View Related
Jun 18, 2005
Is there a way of putting the checkbox to the right of the text other than dragging it over?
View 1 Replies
View Related
Jul 9, 2007
I have this very important question, I dont know how to do it but I am sure Access support this and it is doable.
I have the following recorset:
Name LeaveType LeaveDate LeaveYear
Frank Annual 25/06/2007 2007
Frank Annual 30/04/2007 2007
Frank Unpaid 11/09/2004 2004
James Unpaid 04/03/2006 2006
James Unpaid 19/09/2006 2006
Meco Paternity 17/12/2004 2004
Meco Paternity 18/12/2004 2004
Meco Annual 02/07/2006 2006
Paolo Annual 25/06/2007 2007
Paolo Annual 12/06/2007 2007
Paolo Annual 24/06/2004 2004
I want the data to be shown in a report as follow:
Name: Frank
Leave Type200720062004
Annual200
Unpaid001
Paternity 000
Name: James
Leave Type200720062004
Annual000
Unpaid020
Paternity 000
Name: Meco
Leave Type200720062004
Annual010
Unpaid000
Paternity 002
Name: Paolo
Leave Type200720062004
Annual201
Unpaid000
Paternity 000
I WILL HIGHLY APPRECIATE IF SOME ONE HELP ME DO THIS VERY IMPORTANT TASK.
REGARDS,
View 3 Replies
View Related
Nov 15, 2005
I have designed a few databases for my work. I have been recently assigned a new one that has me a little stumped. I am looking for some suggestions as to how to go about this.
Essentially, we have a training department that does training in the various aspects of the job. In the various classes, the "students" take a midterm exam to gauge the level of retention of the training midpoint in the class. Then at the end of the class, they need to take a final exam.
My department director would like the "students" to be able to take the exams using Access, since 99% of the questions are either multiple choice, yes/no, true/false, or fill in the blank, and Access would be able to "auto-grade" the tests, essentially.
I am at a loss, basically, as to the layout. The "student" information table would be the easy part. I am unsure of how to create the tables that would have the questions, depending on the class and whether it is a midterm or final, with the answer choices attached.
Any suggestions?
View 1 Replies
View Related
Sep 7, 2006
Ive been using the form wizard, with columnar but I find it squishes up some of the words, and doesnt use the whole screen width, is there a way I can change this? and is there anywhere that you can download more styles?
Any thoughts would be awesome.
Cheers Ezy
View 1 Replies
View Related
