Tables Refering To More Than One Table
Nov 11, 2007
So I've been able to link in a table to my orders table. The thing is that my employer needs me to link to two tables. One that holds the tasks that needs to be done. This is completed. Now I need to link the orders table into a version history table with a sub linked table of features related to each version. The problem is that when I link in the version table then when I alter the table and try to access the table view then I can only asign one relation. Is there a way around this?
View Replies
ADVERTISEMENT
Feb 9, 2005
Searching returned too many relationship questions so appols if the answer was staring in my face.
A = big company database (updated monthly by the big cheeses)
B = small "team" database (continually, manually updated by ground level staff)
on the first of the month A is updated
B contains various records from A, but doesn't link in any way (nothing changes when A changes)
I want to change this - I want a query whereby B looks in A, and updates:
1: the balance (how much the customer is in credit or debt)
2: the identifier (a number used to identify customer treatment - e.g 1 - customer is blind, send brail info, 2- customer always pays on time, 5% discount - this changes day in day out)
So lets say, for simplicities sake there are 3 fields in both databases, the 2 above and a number primary key.
How would I do it? bearing in mind both databases are seperate.
(note/edit)
also note I only want to run the query at the start of the month - as reflecting the info of A would result in the data in A+B being 31 days out of date at the end of the month (as opposed to just A)
(useless info :) )
The idea of this is to reinforce integrity in B at the start of the month - ensuring everything is correct, whilst still allowing workers to update the info during the month (allowing me to make sure the highest value debt is worked first)
View 13 Replies
View Related
Jul 11, 2006
After checking on the validity of data entered in a contol on a sub form using the beforeupdate event focus is moved to a password field on the main form. After entering a valid password I would like the focus to be returned to the control on the subform. How would I do this. There are a number of controls on the subform that would be validated in this way so writing the code once with a variable that holds the name of the referring control would be best.
View 1 Replies
View Related
Aug 1, 2005
I have a custom menu bar that has a couple of menu lists, with a list of five custom commands that i want to run,depending on what is selected.I know you can assign a macro to each command,but is there a way to know which command has been selected.
What i want to do is to have 1 macro that will run several functions depending on what was selected, rather than 1 macro for each button
View 1 Replies
View Related
Mar 15, 2006
Hi All,
Can anybody tell me whether it is possible to use a global variable defined in a vb module as part of the criterea in a query, if not whether there is a work around.
I suppose I could temporarily write the data to a table and use it from there, I thought there would be less messing around this way though.
Cheers
View 2 Replies
View Related
Jun 12, 2007
It is possible to refer in a SQL statement to the "current" or "actual" record?I want to base the querry in the present value of two related tables. I will try to show you what I have to do, maybe you can give an alternative solution.These are the tables:tblTeam (idTeam, TeamName, DayCreated)tblTeamPerson (idPerson, idTeam)tblPerson (idPerson, PersonName, MusicPreference)Every person have a music preference, and the teams have to be formed by people with similar music preference.So, one way to filter that is to use the following method: everytime I create a team, I choose a person, ok. That firts guy is the one that will give the team his "MusicalPreference". The next members with different MusicalPreferences will be excluded from the query.But in order to do that, I need to refer to the current team that have 1 member, and use the value of the field MusicPreference in that Person in particular.I hope you have understand what I have to do, and I hope you can help me with this.P.S.: I´m using a combo-box with a special query in a subform to select the team members. In the main form I create the team. So it would be great to be able to use that "idTeam" value in relation with the firts team member of that team.
View 6 Replies
View Related
May 30, 2014
What I have is a database setup with multiple tables in which different areas of my DC can input information simultaneously into their respective tables. I then have another database linked to it for myself to have a live view of each updated record. I would like to see all the records of each table in 1 single table (possibly just sorted ascending by time). Each table has the same Field headings but may have different qtys of records. As I will then have it linked to an Excel table to VLOOKUP from it.
I have tried Union coding but always get Syntax Error etc.
View 8 Replies
View Related
May 29, 2014
I have two tables of data, each relating to three business branches (branches A, B and C).
Table 1 shows the expenditure of each branch (by fuel, premises and wages).
Table 2 shows a number of units for each branch (mileage, floorspace and sales).
What I would like to do is calculate unit costs, based on the expenditure in Table 1, divided by a relevant unit in Table 2. The catch is that I want to have a third table which allows the user to specify which expenditure (from Table 1) is combined with which unit (from Table 2) to generate the calculated unit costs. I've been able to do this in Excel, and have attached an example. I've also attached an incomplete Access version with the first two tables. Given the complexity of my actual data, I feel this could be better handled in Access than Excel.
View 6 Replies
View Related
Feb 18, 2013
I have an Access Table with about 28,000 Automobile dealerships across the country shown. I've joined a new/small phone contact table to this to keep up with our phone contacts with the dealership and followup efforts. When I search/filter on the dealership table all is fine. However when I search/filter on the phone contact table with a few test entries, I get nothing at all. I supposed that after joining the tables, I'd be able to do a search on the field named follow up date and find/filter today's date or other dates and locate which dealerships to contact when the correct date arrives. But nothing.
View 14 Replies
View Related
Mar 29, 2013
I have a feed from ODBC to Access DB. I need to populate several new tables with the contents of the original table. I know I'm being stupid but it's been a long week.
View 1 Replies
View Related
Jul 8, 2013
I need to combine two tables that have identical structure to one table. Different people have used them to insert data from different parts of our project. I have tried to find information about how to do this but so far with little luck.
View 13 Replies
View Related
Jul 30, 2013
I have three tables. I want to combine all the records in these tables into one table. I need VBA code to do this. The first table is called down1, the second table is called down2 and the third table is called down3. All these tables contain the same fields so I don't think combining them will be a problem.
View 3 Replies
View Related
Apr 12, 2013
I've got a working database with pretty good structure etc. How I would go about creating 3 prepopulated records on a many side of a relationship on entering a new record in the one side of the relationship.
View 6 Replies
View Related
Jul 16, 2014
I'm trying to split a table up because I now realize it won't be able to do what I need in the future. It wasn't designed properly at the outset, and I'm trying to correct it now.
This is a database of pregnancies and deliveries.
The single table does not cater well for multiple pregnancies (twins, triplets, etc), and also I foresee problems when mothers come back in future for another delivery.
Therefore I am trying to separate data into 3 tables: Mother, Delivery and Baby.
This is because each mother can deliver more than once, and each delivery can have more than one baby.
I have set up a trial database, with just a few fields in each table to see if this works. An screenshot of the table relationships is attached.
The primary key of the Mother table is linked to the Delivery table, and ditto the primary key of the Delivery table and the Baby table.
I'm not sure how to migrate the data over, in terms of the primary keys of each table, because in the new tables, these should be an AutoNumber field, so that they are unique numbers.
View 14 Replies
View Related
Oct 27, 2014
I have a db with 2 tables one with 10 fields and the other with 11 fields. In these tables there are 4 and 5 non common fields in tables respectively.
How can I join these tables in a 3rd table with all the fields from both tables.?
View 1 Replies
View Related
Oct 26, 2012
I am working on a complex database, as i have to call sub tables in the parent table. but i don't know how i can create it. My database format is as below
Village name, UC Name, District, village basic information, village disasters history, village school safety plan, village DMC members, Hazard Map of the village,
After the field of District i have to call sub tables against each field with its details. How I can create it. in Hazard Map field i have to attach the map img files.
View 1 Replies
View Related
Nov 4, 2014
I have a make-table query that pulls all the fields from 1 table (MainTable), and creates a new table with a date stamp based apon a form value entered (New Table = MainTableWithDate).
Currently, I setup the query to pull info from the form field like this:
DateField: [Forms]![frmmain]![DateField]
However, when the make-table query is done - all date fields are blank (all other fields are correctly created), and when I look at the new created table (mainTableWIthDate), the typeassigned to the date field is "Binary" (in the form, I've specified LongDate).
View 6 Replies
View Related
May 28, 2015
I have a table for a multiple parents linked to a child table. I need to figure out a way to only allow 1 parent to be coded as primary, 1 as secondary, and then the rest as other... I thought about making Primary/Secondary/Other a primary key. But then I can only have 1 other. I would have to make a finite number of parents that could be entered and I want an infinite number.... My end goal is to have a report that only has a primary and second parent on it, but the rest of the parents still exist in the table...
*Child_ID
Parent_First_Name
*Primary/Secondary/Other
View 8 Replies
View Related
Jun 4, 2015
I have three tables that contain different columns but linked by a primary column call Name. I want to create a table where all these different columns in the three tables join to form a master table which can be updated regularly either through the master table or the smaller tables. The master table also has the primary column as Name.
If I update the master table with records, it should update the respective linked table and vice versa. I also want to link these tables to my SharePoint site.
Note: except the Name column, none of these tables have any other columns in common How do I go about this?
View 7 Replies
View Related
May 13, 2015
I have what is probably a stupid question but I've been struggling with this one for a while. I have an ordering database which has an Orders table (containing Order ID, Date, Supplier etc) and an Order Line table within which I have a combo box for Product name, supplier, price, VAT rate, Line price etc. At the moment, I have the order line table as a subform within the Order form (run from the Orders table). The problem I'm having is the subtotal and total fields. At the moment these are in the Order Line table as I cannot figure out how to get these in the Orders table. In summary, can I make a calculated field in one table that calculates values from another table (linked by Order ID)?
View 3 Replies
View Related
Sep 14, 2012
Some days ago I made it by using "query", but now I forget it how I make the relation on this situation. Here is my problem.I have 3 Table on mdb file, named Table: A, B, Status.Table Status have One Filed with 1 Data: Dishonor
Table A have three fields
Sl Number: (Auto Number)
Status: Lookup wizard-data of (Table-Status)-Default Value is "Honor"
Amount:Number
Table B have two Fileds
Sl Number: Number
Status: Lookup wizard-data of (Table-Status)
After Entering Some data on Table A it's Look like as:
Asl numberstatusamount
1Honor5222
2Honor855
3Honor988
4Honor7777
5Honor777
6Honor9999
[code]...
Now I want to change the Status of SL Number Honor to Dishonor so I fillup data on Table B is as like following
sl numberstatus
5Dishonor
Now how I can get the result as following by using query:sl numberstatusamount
1Honor5222
2Honor855
3Honor988
4Honor7777
5Dishonor777
6Honor9999
7Honor6666
8Honor7777
9Honor666
View 1 Replies
View Related
Mar 6, 2014
I have three tables: Vehicles; Vehicle Reallocated; and Vehicles Retired. I have a form that runs a query to find all the info in the Vehicles tbl that is not "Retired", not visible in the form. I then have the option to toggle to a Reallocated or Retired form. When i toggle to the reallocated form, i have the like fields in that table (ie Van #, Vin, Make etc) pulling the info from the hidden subform with the vehicle query, so i do not need to fill in repeat data. However, when i add a reallocated date and the new clinic that vehicle is for, i get the record ID for the vehicle reallocated table as expected, but when i save none of the data moved over from the query saves in the record?
How to get all the data on the reallocated form to save?
View 6 Replies
View Related
Jan 12, 2013
I have a keys table and a keysctivity table. I need keys to not be available if they are currently signed out (return_date is null) or if they have been marked as lost (lost_key = true)
This is my activity table. URL....If for example signin_id 1 was not returned or was lost, key_id 1 should no longer be available. Is this something that can be done?This is the access file I am working on: URL....
create two new tables "lostKeys" "unreturned_keys" and have records moved to their respective tables based on whether they are indicated to be lost or currently not returned.
View 4 Replies
View Related
Nov 21, 2014
I am working from an existing database which is just two table. The main table has a massive amount of redundancy and duplication and needs splitting into, at first glance, 5 tables.
After I have run my various make table queries and added a Primary Key and FK field to the new tables how do I populate the FK with the Parent PK.
I thought I could simply add all the fields from the new table and then create an adhoc join in an update query to populate the PK to the FK. When I do this however I get "You are about to update 0 records"
I have tried the table analyzer but it doesn't give the correct options to split the table the way I need.
View 6 Replies
View Related
Aug 23, 2013
I have 10 tables. None of them have any entries in them. Just field names. Is there a way to combine them all into one table.
View 14 Replies
View Related
Nov 11, 2013
I have two table
1 is name master containing EMPID(Primary Key)
2 is Saving which also contain EMPID
i want to entered data in Saving with each and every EMPID with Master
View 1 Replies
View Related
