I am currently creating a multiple table database, for a project, in which jobs done can be entered.
Here is how it i set out
CustomerInfo (Name, Address, ID (autonumber), etc)
Rooms (Information on colour, paint on room walls)
Ceiling (info on colour and paint on ceiling)
Woodwork (info on colour and paint on all woodwork)
Dates (starting and completoin date, month and year)
I have set all the table relationships throuhg the id, yet the id is never the same (on customer info it is 5, whilst everywhere else it is 2) I want it so that it is forced to one I.D. I have set up a form to enter all this information in. It works well, and allows you to just enter information, as no previous info is dispalyed.
Now however, I need to be able to search the whole database (by either the month/year in the dates table, or by the unique ID, first name, last name etc.)
How can i set this up in access? It will need to search all forms, and needs to be set up as either a form / whatever is easiest, but can be a new form, as long as I am told how to.
So, how do I solve both of my problems?
I am using Access 2010 and Excel 2010. I need to have VB script to export the access table 502 records by 38 fields into Multiple Excel workbooks each having multiple tabs. In the Access table each record has two fields: Div and Tab that will be used to name each workbook and each tab (sheet). There are 6 unique "Div"'s to name the 6 workbooks and there are several "Tab" names for each Div (workbook).
Note: These 6 workbooks with multiple tabs were originally imported into Access from one common folder on my desktop by this routine:
Option Compare Database Option Explicit Private Sub Command1_Click() Dim blnHasFieldNames As Boolean, blnEXCEL As Boolean, blnReadOnly As Boolean Dim lngCount As Long
I am using Access 2010 and Excel 2010. I need to have VB script to export the access table 502 records by 38 fields into Multiple Excel workbooks each having multiple tabs. In the Access table each record has two fields: Div and Tab that will be used to name each workbook and each tab (sheet). There are 6 unique "Div"'s to name the 6 workbooks and there are several "Tab" names for each Div (workbook).
Excel workbooks would take names from the "Div" field and the tab names would come from the "Tab" field in the Access table. First need to find workbook name (Div - Field) then the look for each sheet name (Tab - Field) to create 1st Excel workbook with all the sheets (Tab) and repeat the process. I think you need to approach of read the Access table one record at a time keying on the "Div" and "Tab" fields in creating each Excel workbook with the associated multiple tabs (sheets) that are written to a common folder.
Note: These 6 workbooks with multiple tabs were originally imported into Access from one common folder on my desktop by this routine.
Option Compare Database Option Explicit Private Sub Command1_Click() Dim blnHasFieldNames As Boolean, blnEXCEL As Boolean, blnReadOnly As Boolean Dim lngCount As Long Dim objExcel As Object, objWorkbook As Object Dim colWorksheets As Collection
I HAVE A DATASETS cSV TEXT FORMAT WHICH HAS A SELECTION OF FIELDS THAT NEED TO BE IMPORTED INTO A TABLE. USING THE ADVANCED IMPORT FACILLITY I HAVE BEEN UNABLE TO IMPORT THE DATA PLEASE HELP AS THIS IS FOR MY A-LEVEL STUDENTS.
THE FIELDS REQUIRED IN THE SESSION TABLE ARE, Booking ID, Customer ID, Date Booking Made, Pickup Point, Payment Made
I have 10 tables, 30+ fields on each table (every table has the same 'account number' field). I only need from 5 - 20 fields from each table. How do I get the certain fields from each table and put them in a table, query or report?
All retailers have at least one distributor BUT a retailer may or may not have ordered any products.
I have created my form but the query linked to the form is having some trouble. It is only selecting those records that have ordered products. For example, if I query a retailer name only and it does not have any ordered products, it will not display. Is there a problem with the table joins? The SQL for the query is displayed here:
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...
In a situation where I imported an excel file with so many columns and split them into two temp tables and they are linked using a key.
the data has a fixed part lets say
Field1....Field2.....Filed3.....Field4...then Field5.....Field6.....Field7....Field8 is the same data range as Field9...Field10...Field11...Field12. I would want to split this data into multiple rows like this
Field 1 Field2 Field3 Field4 Field5 Field6 Field7 Field8 Field 1 Field2 Field3 Field4 Field9 field10 field11 field12 and so own...
I've got a table tblPatienten, a table tblRechnung and a form frmRechnung.
The primary key in tblPatienten is KundenNummer, the primary key in tblRechnung is RechnungsNummer. The relationship between tblPatienten and tblRechnung is one to many.
Now, every patient (stored in tblPatienten) is allowed to make multiple orders (stored in tblRechnung).
How can I assign each new order entry in frmRechnung to an existing patient in tblPatienten?
I am building a simplified re-order point system - if inventory position drops below a certain level (the yellow level is this case) one or more purchase order lines has to be created in another table.
I have one table with the following field and data:
ItemId Red Yellow Green Multiple Inventory position 0001 10 30 50 5 45 0002 5 40 47 5 23 0003 11 20 30 10 5
I would like to generate new records (in another table) based on the above fields and three records.Basically the end result should look as the following:
The logic is quite simple - if inventory position is less than the yellow value new order lines should be created in multiple qty (based on the multiple field) until the aggregated value (in table 2) is above the green value.The priotization value should be based on the start inv (in tbl 2) compared to the values in red, yellow and green in tbl 1.
I have a parent table (tblLabels) and a child table (tblRevision) where the revision history for the parent table is kept.
The parent table is populated via an excel import and may have several records imported at once. Instead of having the user manually enter a new record note in the child table for each record imported into the parent table, I've created a form that collects the necessary data (date, person who added the record, person who authorized the record, and notes) and then creates a revision history for each new record.
This is what I have so far:
Code:
Private Sub cmdAddNotes_Click() Dim strSQL As String Dim RevisionDate As String Dim RevisionRevisedBy As String Dim RevisionDesc As String
[Code] ....
When I run the code nothing happens. No error, no new records create, etc. My suspicion is that I may need to use an INSERT INTO query instead of an UPDATE query, but I'm not sure how to go about matching up the foreign keys if that's the case.
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).
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?
I have a question which is not so easy to discribe but maybe easy to answer, so i'm giving my best shot. I'm making something to store information about patients. If made the forms en table with the wizard within access and some extra functions with VBA. The first problem i have is this: I have to many control elements on 1 form (control boxes, I get a error message) so I need to split this form up into 2 forms. Other control elements or no option, because the input should be like this. The information form both forms need to be kept together in the linked table as 1 record. But now I have split the form up into 2 forms I get 2 separated records. 1 record with the information from the first form and the other record with information from the second form. My question is what can I do the merge both records to one or maybe better to make sure that both forms only generate one complete filled record, so my table won't grow unnecessary big.
Please keep your answer as easy as possible so I can keepup. Thanks!
I'm making a database of spare parts for machinery. But I came upon the following problem : A part can sometimes be used is more that 1 machinery. But I do not understand how I put the ID of all the machines in 1 field of that spare part
Well I'm hoping someone here can point me in the right direction. This is my problem... We've got about 50 Access databases scattered across the network. Each network database contains the same tables and fields, but of course, each one contains different information (one network database is for DepartmentA, another network database is for DepartmentB, another network database is for DepartmentC, and so on).
What my boss wants is a single table/view that displays all the information contained in every network database table. The other part of this is that my boss then wants to be able to make changes in this single, all encompassing table/view and have those changes be pushed back out to the individual network databases. Is this possible? And if so, can someone help point me in the right direction? I'm still a little new at Access, but I can manipulate VBA code and all that if need be (I'm learning more and more as I get deeper and deeper into this project).
I inherented a bunch of db from someone else, I've created / maintained db before, but this one has me stumped, the creater has a db, dbA, but dbB and dbC and dbD get the login info from dbA, has anyone done this? How is it done? I guess I get the concept, only one has to be maintained..... would appreciate any ones thoughts/comments on this
Hey guys- I am importing data on a daily basis- some of it contains existing records, some of those existing records have updated information, and then the rest will be new records. I am importing into a table, say, tbImport.
I have 3 tables I am splitting this info out to- tbListings, tbAgent, and tbAgentOffice. Each record can have up to 3 Sales Agents on the file- Agent 1, Agent 2, and Agent 3. Each Agent has their own Employee ID, as well as Office ID, Mobile, Home, Pager numbers. Each Agent can belong to only ONE Office, but every office can have multiple agents. I am using the Agent's Employee ID (it's assigned by another SQL db- and I will never be assigning this freely to anyone else, or new agents- they come into the DB already assigned a unique code).
Agent 1, 2 and 3 can freely change positions (Agent 1 can be Agent 3 at times, etc etc) from record to record. However, I want to store all their contact info into a separate table, going for 3rd Level Normalization...
So what I have is this-
tbImport _____________________ fldInfoID fldWhatever fldAgent1Code fldAgent1Name fldAgent1Mobile fldAgent1Pager fldAgent1Home fldAgent1OfficeName fldAgent1OfficeCode fldAgent1OfficeFax fldAgent1OfficeNumber fldAgent2Code fldAgent2Name fldAgent2Mobile fldAgent2Pager fldAgent2Home fldAgent2OfficeName fldAgent2OfficeCode fldAgent2OfficeFax fldAgent2OfficeNumber fldAgent3Code fldAgent3Name fldAgent3Mobile fldAgent3Pager fldAgent3Home fldAgent3OfficeName fldAgent3OfficeCode fldAgent3OfficeFax fldAgent3OfficeNumber **plus a ton of other data- but you get the idea here
So- the tbAgent table will store all the contact info for every agent, and tbAgentOffice stores info for their offices. I know I'll have relationships from tbListings.fldAgent1Code (as well as Agent 2 and 3 at the same time) to the tbAgent.fldAgentCode. I'll also have a relationship from tbAgent.fldAgentOfficeCode to tbAgentOffice.fldAgentOfficeCode. So, when I do daily imports- I will need to run an update query from the tbImport table into the other 3 tables at the same time. My question is this- How do I cram the individual fields for all 3 agents, into one universal field in the tbAgent table at the same time? I can't get my mind around the concept here. How will it take all the fields for each agent in the tbImport table- and cram them into 1 record set (while checking for existing/update records) in the tbAgent table? How do I write that in the sql code to tell it to link Agent2Mobile into the same Record as Agent2Code, and not into the Agent1Code or Agent3Code? Right now it seems to me that there's no way to distinguish between the Mobile Number for Agent 1 2 and 3 compared to their own Codes to identify the records with. Does that make sense?
Any help you can provide is EXTREMELY appreciated!
Another month, a new database, more questions... a quick thankyou to all those who helped with my last database, much appreciated!
I am trying to set up a query run from 12 tables, each table is supposed to be the same but we have had a number of people changing things when they shouldnt have been. Each table is a month of posts in our employee headcount, each post has a ID Key which shuold stay the same throughout, some of these IDs have been deleted when they shouldnt have been, some more have been added when, again, they shouldnt have been. Each post ID has a set FTE number (dictates the hours they do a week), some of these have changed, again when they shuoldnt have, some have changed when they shuold have and we have a record of these ones.
I need to be able to check which post IDs have been deleted, what month (or table) they were first deleted from. What post IDs have been added and what month/table these were first added. I guess i use the first table as the reference point and also that maybe layered queries (compare tables 1st month/second month and then do a query on those results against 3rd month and so forth but not sure how this would pick up changes from the first month...) but not sure quite how to do this and whether this is the right way to do things. The next thing to do will be to check the FTEs but will wait until i sort this query out first.
I am pretty bad at trying to describe things but let me give this a shot. I have inheareted a BEAST of a DB to try and maintain; not to mention add all sorts of new requests I get handed from mgmt.
I need build a query that takes information from a table of monthly quota data, pull out how many of each we have repaired from a work table, and pull description information from a third table. All this needs to be sorted by month.
Here's sort of what I am trying to get out of the query:
I would think this would be easy; but I just can't seem to get it to work. First off; just pulling out repairs by date I cannot get the wildcards to work. I have to run the query with the date being >=#01 Dec 07#; I cannot get it to pull Jan/Feb/Mar/etc... Secondly; when I try to take the quota data w/repair data w/descript data I get results that just do not make any sense. Not sure how/where that is all messing up.
Anyhow; I don't know what else I should post with this that could give anyone an idea on how to make things work. If you have a thought, please let me know :) If you need information to help understand what I'm trying to ask, just ask away and I'll do my best to answer.
I am trying to pull information from 2 seperate tables and store that information in a 3rd table. I need to autofill information from both tables when I select the information from the field that joins them. Then that information needs to be saved in the 3rd table. I have to store this "duplicate" copy of the informaion becuase one table's inforamtion changes quite frequently and I needs to be able to find historical data. Here is a simplified version of my tables for example.
Again, all information has to be stored in ProductsOrdered because things like Quote.Price can change frequently.
Another important feature is I have to beable to add records with what ever method I use. This Is being done in a sub form so that many products can be placed on an order form. I can get the tables to combine in a way that will allow me to view the information, but not add to it.
Hi, I'm new to Access, but I think I've been doing fairly well. At my job, we use multiple Word tables and excel spreadsheets to keep track of information on journals we subscribe to. (I work in the periodicals/serials department of the university library.) We have a LOT of journals and a lot of information on each one.
I have already imported our "superholdings" file, which includes the names of the journals and a few bits of information on each one. What I want to do is import other bits of information from the other files into that same table. I tried importing the files into the existing table, but rather than merging all of the data for each journal title, it simply added the new records to the bottom. I tried importing the files as new tables and joining the tables with the title fields, but that didn't do what I wanted.
Really, I just want to merge all of the data. If the Title's match exactly, then I want all of that information in one record for that title. I have been VERY consistent with field names.
I'm new to Access, but I learn fairly quickly. I notice on this forum (I've read a LOT of the posts looking for answers to this question, and got answers for other questions) that most of the solutions involve coding or programming of some sort. I have NEVER done this in access. If the solution to my problem involves this, please tell me where I'm supposed to type those commands. I'm somewhat familiar with softcode (from a mux (talker environment) I started frequenting in college), but never have programmed anything. (Not since basic on our old IBM XT!!)
Okay, an example is forthcoming, once I figure out how to do it in this little window.
I have a table tblLineItem contains materials/line item records assign to different buyers. Each buyer/user needs to access the same table tlbLineItem and choose certain line items and works with them to create PO (purchase order) .
How do I lock the table while whoever access first then after that person finish it release the table to avoid each process of one buyer's items overlap with other buyers' line items. And if the table is being used have a message box alert to inform the buyers, so they can wait?
My problem with this database is right now I placed a check box on each line item on the form that have the data source is the tblLineItem. Each buyer filters only their line items, check the box of the line items they wish to create PO at a time and click the button "Create PO". However, if buyer A uses the same form and check the boxes on his/her own line items, while coincidently buyer B also work on the same form then when they click "Create PO", their line items cross with each other!!
I have a database to handle both purchase orders and customer orders,
My problem is with the products.
there are six tables all with identical fields, but with different data in each, I need to be able to merge these six table into one and allow the original tables to be updated, inturn updating the merged table.
I know this is a rather elementary Access function, but i cannot get it going: Whenever I run a query combining fields from two tables, the records come up empty. What could i have done wrong? the tables are in same db., with common fields. One thing is, when I try in the relationship window to enforce referential integrity I get an error message which reads 'no unique index.' I am baffled because the primary key in the parent table related to the same field in the child table.
