Creating A Query Across 2 Tables
Mar 20, 2006Hey :)
is it possible to create a query for 1 table and for the same query to retrieve some data from another table
Cheers
Tom Evans
ADVERTISEMENT
Queries :: Creating A Query With Self Join Tables
Feb 11, 2014I have a table called MiscORders where all the orders are tracked. There is an ID, order number , other fields and ParentorderID fields in it. Whenever an order is modified they create a new order with ordernumber and have the previous order number as the parent. The next time it is amended or closed another order is created with the parent order attached. Here is some sample data
Order ID OrderNum Parent order id
1 MISC 2013-10
2 MISC 2013-10A MISC 2013-10
3 MISC 2013-10B MISC 2013-10
Now I am creating a query that shows the lifecycle of the order
MISC 2013-10 Issue and deadline date MISC 2013-10A issue and deadline date MISC 2013-10B issue and deadline date.I tried to create a query using self joins to the same table. SQL is attached
SELECT MISCORDER.OrderNum, MISCORDER.[Date Issued], MISCORDER.[Deadline Date], MISCORDER_1.OrderNum
FROM MISCORDER LEFT JOIN MISCORDER AS MISCORDER_1 ON MISCORDER.OrderNum = MISCORDER_1.RescindedOrderID
WHERE (((MISCORDER.OrderNum) Not Like '*A' And (MISCORDER.OrderNum) Not Like '*B' And (MISCORDER.OrderNum) Not Like '*C' And (MISCORDER.OrderNum) Not Like '*D') AND ((MISCORDER_1.OrderNum) Not Like '*B'));
How can I put a condition on the table to show only records with order num ending with A. It is not working if i use it in the where condition..
Tables :: Creating Table Via Into Query With Blank Numeric Field
Jun 13, 2014I am creating a table, using an INTO statement.
I require a blank numeric field, which users will be updating via a form.
How do I make the field numeric, as my current script makes it a text field
'' as Ticket_No
And you can't cast/convert text to numeric ....
Queries :: Creating Query To Pull Info From Multiple Tables
May 7, 2013I have DB used for inventory for many different categories. I have a table and form for the following: Location, Printers, Pc's and many more.
What i am trying to accomplish is to have a advanced search form that will display how many pc's and there makes and model from selecting the location name or Cost center from a combo box.
So an example would be I want to select MPP-WDF from the combo box click a button and it will return the number of PC and there makes of model's and some other information in a list of records.
Tables :: Creating Table Via INTO Query With Blank Numeric Field
Jun 13, 2014I am creating a table, using an INTO statement.
I require a blank numeric field, which users will be updating via a form.
How do I make the field numeric, as my current script makes it a text field
'' as Ticket_No
And you can't cast/convert text to numeric ...
Forms :: Form Produces Error After Creating Query From 3 Tables
Feb 11, 2015I have 3 tables:
tblMain
BalanceID PK
GasID FK
ElecID FK
tblGas
GasID FK? not sure
BalanceID
tblElectric
ElecID FK? not sure
BalanceID
I created 1 query from all 3 tables, then created the form from that 1 query.Now when I enter data into the form and click to save it , it produces this error message:The Microsoft Database Engine cannot find a record in the tblGas with key matching fields 'tblMain_GasID.The tblGas does contain a field GasID, but does it mean that the field should be tblMain_GasID
Creating A Query / Report That Displays Data From Two Tables And Total One Set Of Data
Aug 10, 2012I want a Text Box Query on my form to display the Status, Workshop, Time, Enrolled and Limit. The problem is these values come from two different tables and the Enrolled value comes from a single field that contains the different workshops.
What I mean is: In Table[Attendees] a row contains a customer's Number, First Name, Last Name, Workshop and Phone Number. The workshops vary for each customer so one row on the table could have John Doe attending Cover Letter Writing and the next row could have John Smith attending Resume Writing. What I want is to be able to count the different workshops within the Field[Workshop] and total them and then display the total in a Text Box Query. I have a Text Box Query set up displaying Status, Workshop, Time and Limit as these values all come from Table[Workshops].
So basically I need to Query to also display a result that is the Total for each workshop from Table[Attendees] and display the total for each workshop in a Query with data from Table[Workshops].
Here is a link to an Example Database [URL] ....
I'm trying to avoid putting things on different reports and the like because the people using this are basically computer illiterate and if they have to click a button (no matter how well labeled) they won't do it and the information might as well not exist.
And if there's a better way to do it, I'm all ears. The only thing is, I have to update these workshops month by month. Since they are dynamic, I want to avoid creating separate tables for each workshop.
Tables :: Creating Relationship Between Two Tables
Dec 11, 2012I created two tables, but i don't know what kind of relationship i should create.
In the first table, i would like to put all different tests (medical tests, such as EMG test, and so on), in the second table, i want to add the settingup for each test, say, recording site, stimulating site et.al, then i want to use one form to populate data into these two tables, what should i do?
Creating Tables
Jul 19, 2007Hiya iam new one here, and fairly new with access. I got my self into a mess to what is proberly very simple to solve. I have 5 different products, which have 18 components for each product, each component has a part number and a price. Iam finding it difficult to arrange this data into tables in 3rd normal form. Do I have a table for each product, then a table for components, then a table for part number and price, but how would I create relationships for these?? Please help its driving me mad, any help would be greatly appricated.
View 3 Replies View RelatedHelp Creating Tables
Oct 11, 2005Good day all,
up to now i only used simple databases with one table.
but now i am stuck.
here's what i need to do:
i have certain products that i put available to "borrow".
On certain products you can just borrow one particular product, but other products are linked. So if you need to borrow one, you're going to need another one as well.
So as soon as you book a certain product, it has to check whether there is linked products, and if all wanted products are available. Then it has to set a date for delivery and a date it needs to be sent back.
I'll use asp to link to the database.
i know i'll have 2 tables - 1 for the available products, and 1 for the custmer bookings.
What i'm asking though is how to set up these tables and then link them together. And also what type of info goes into which table.
Do i need other tables as well?
thanks
lee
Creating Tables In VB Using Enviroment
Nov 9, 2004I am trying to create a table using their login as the table name. I have captured their login but, how and can I apply that to a table name. :cool:
View 3 Replies View RelatedHelp Need Creating A Join Between 3 Tables
Sep 6, 2005Hi there
I have made a form I am trying to bound 3 tables to.
The 3 tables are
tblPersonalInfo
tblBookingInfo
tblSkills
How I am trying to set it up is that I enter someones personal details in once. But using the same personal details someone can have more then one booking.
With the skills....each person can have lots of different skills.
Basically I work for a recruitment agency and I have been asked to build a database that can add new people.add skills to that person.....then add different jobs to the same person.
I have made 3 tables
tblPersonalInfo
PersonalID
chrForename
chrSurname
dtmDOB
chrEmail
tblBookingInfo
BookingID
chrJobTitle
curPayRate
PersonalID
SkillsID
tblSkills
SkillsID
Skills
I have joined all tables togeather (I think wrong but trying) the tblbookinginfo is joined to tblbooking using a 1 - Many relationship using the primary key of tblPersonalInfo to the field PersonalID in the table tblBookingInfo
I have joined tblSkills to the table tblbookingID through SkillsID
I created a query using all 3 tables and have used this to bound the form....
The form contains 3 pages and the 1st page works ok (it only contains info from the tblPersonalInfo) the 2 other pages don't seem to work as I want them to.
I would like to be able to click to add a new person and also new booking and new skills
Currently I am able to add new personal details but nothing else.
Can anyone see what I am doing wrong?
Thanks for taking the time to read this
Problem Creating Tables
Oct 25, 2005I am having problems trying to create a new table in an existing database I am working on in Access 2002. When I try to save the new table I receive an error message saying 'Invalid Argument'.
I have checked if the database is locked but can't find anything to suggest that it is.
Can anyone suggest what might be the cause. :confused:
Thanks
Looking For Help Creating Access Tables
Apr 2, 2008Iam new to access and having problems create database tables and their relationships, from the following details without duplication.
Tape Identification Numberà unique tape identity
Date -à the date the backup is done .
Backup type à either Daily or weekly or monthly or Full System or Yearly or Special
Duration à either automatic or Manual
Technician name à Person performing the Backup
Comment à comments about the Tape’s history
Location à tape is either located onsite or offsite.
Number of use à Number of times the tape has been used
Restoration à tapes restoration Details, either yes/no
Requirements
1.perform backup everyday
2.Perform restoration everyday
3.Search for tape from offsite storage
a.Tape should be located offsite
b.If it was used for daily backup, it should be at least 60 days old from current date.
c.If it was used for Weekly backup, it should be at least 6 months old.
d.If it was used for any other backup type, do not use.
** the database should provide history
Creating One Table From Many Tables
Aug 24, 2007Hi All.
Need union three TableA, TableB and TableC into one table called TableX? All table has same column name. TableX shouldn't has dublications.
Thanks
Creating Tables Thru Forms
Feb 23, 2005Hi,
If i have a new entry from the user, and i want access to create a table on its own, with the table name defined by the user and the elements of the tables already pre-defined by a sample table that i already do out... Is there any way of doing it? Thanks for any helpful souls around...
Creating Links In Tables
Dec 2, 2004Hi all.
As i'm still what can be called a "newbie" in MS Access, maybe one of you wizards
can help me out with this.
I'm having a product table in ms access, containing a few columns. The regular stuff (type, ID, ..).
It also contains a column i want to store the links to their documentation to, called "download'.
The fields of that table are loaded into a data access page.
Now the data access page is loaded into the frame of an HTML page. Everything works well, but when it
comes to the download-field, i get no link, but a piece of code instead.
Now these links are relative links (not absolute ones). What i'm searching for (for quiete some time now), is a way to make the links show as normal links.
Is there anyone who could give me a hint or who can help me out with this?
Thank you very much in advance,
Kind regards
Creating A Report From Two Or More Tables
May 18, 2006Hi all,
I am a complete newbie so please beware/be patient.
What I am trying to do is to create a report from two table, using the wizard it starts complaining about the relationships between the two tables, though I only want to produce the table on one report.
Please help.
Thanks,
The Gurkentopf
Creating Relationships Between Tables
Oct 30, 2012I was wondering how to create relationships between tables?
View 2 Replies View RelatedCreating Two Tables And Having Them Get Info From Each Other
Apr 2, 2012I am very new to access and I am trying to create two tables called Customers and Orders.
The fields in Customers are:
CustomerID (Primary Key), First Name, Last Name, Address.
The fields in Orders are:
Order# (Primary Key), CustomerID, Product Purchased, Address.
There is a One to many relationship between CustomerID in the table Customers, to the CustomerID in Orders.
The problem I am having now is with the Address field.
GOAL: What I am trying to do is have the Orders table automatically get the Address from the Customers Table.
If that doesnt make sense, whenever I create a new Order, I want to enter the customerID, and have it get the Address data from the Customers table automatically put into the proper field in the Orders table.
Creating Rows In Multiple Tables
Nov 3, 2004Hello:
I'm new to Access but not SQL, and am having difficulty coming up with a solution to the following problem:
I have one table for all Strings used in all other tables (to make Internationalization easier). The fields are: zId, zType, and zString. zId is an AutoNumber field. zType is a number which defines what type of string it is ( Item Name, Person Name, etc. ). zString is a text field with the actual text string (can be empty).
I have another table for Items, which have (among other things) Names (field name= zName). zName is actually a lookup into the Strings table and stores just the ID.
I want to create a form for people to enter a new Item. When creating a new item using this form, they will have to give it a Name, which will need to add a new entry into the Strings table with the appropriate type (ie, Item Name).
So, my question is "How to do the following..."
* on my form to enter Item Data, when a user goes to create a new Item, I must first create a new entry in the Strings table with the correct value for zType. Then, I must get the value for zId for this new entry and assign it to the zName field in the Items table for the new record that I am creating.
Any help is appreciated.
_C
Creating Columns Into Access Tables Using ASP?
Jan 1, 2005I am having trouble getting ASP code to create a new column in an Access database.
This is what is currently coded, but does not work. Something tells me that the code should be Create instead of Alter, but not sure what the correct terms would be. I need the code to automatically create this column called PCName with the listed properties in the table called member. Any help would be greatly appreciated.
re.Open "Alter Table in member Add Column " & PCName & " varchar(50) default None",conn2
sql = "Update member Set " & PCName & " = 'None'"
Creating A Form Lookup From 2 Tables
Mar 20, 2008Hey all.
I'm having a few issues with creating a Lookup on an Access form. I'll recreate the scenario below as best i can to help with my explanation.
I have 2 data entries. The first is a Post Code (Zip code), and the second is an Area Rating that applies to the Post code. For example:
Post code AB1 8 is awarded a rating of A
Post code SO1 3 is awarded a rating of F
I have already created a table in Excel with all 2,922 Post Codes (AB1 1 to ZE3 9) and the corresponding Area Rates (A to F)
What i'm making is a form that holds customer details. I'd like a combo box or text box that when you type in the Post Code, another text box automatically displays it's corresponding Area Rating.
Code:Post Code: Area Rating:------------- --------------| CM2 7 | | A |------------- --------------
I apologise for my really very poor diagram :P
So the Post Code box is either typed into or a combo box with the list of Post Codes, and the Area Rate box cannot be typed in to, but it automatically displays the record dependant on what is in the Post Code box.
Ideally, these will also be recorded onto the main table (IE not the table with the list of post codes or area ratings, but a 3rd table holding the rest of the details that will be used to create statistics and graphs)
Can anyone help me with how to do this? I've been trying to work it out all afternoon!
Any help is really appreciated.
Tables :: Creating Expression On A Report
Mar 4, 2013I have a database of students that have taken tests. That database includes how many attempts it took them to pass. I have successfully generated reports that sort the exams by type, date, student, etc. I would like to also somehow insert a formula to show the pass rate of those exams on the top of the report. I know I can get into design view and build an expression, but how would I build an expression that would create a pass rate percentage based on whether or not a student passed an exam on the first attempt.
I have a DB with a column titled "Attempt" and the options "First, Second, Third, Fourth". I would like the expression to generate a percentage of students that have "First" in their box (as opposed to anything else) out of the total number of student records.
We need to track student pass rates in my school for a re-certification process.
Tables :: Creating ID With Text And Autonumber
Dec 1, 2014My database exports data to one of our customers who then inputs the data into their system.
One field i have is "RO Number" which i have as an autonumber which works for the purposes of my part of the system.
However they have it set as "RO9999", (Their system has been set up for years, we are a new "branch") but they aren't in access.
They want the RO number to be a running serial from RO30000, RO30001 etc
The data in entered on a form.
Tables :: Creating A Secondary Table
Oct 27, 2014I have an Access database, where one of the tables is near the maximum amount of fields (241 of 255), was just wondering if I could set up a secondary table that would be linked to the original table by a specific field and would store the other fields that would like to add to the table.
View 7 Replies View Related