what is the difference between creating a table using SQL and creating a table using MAOL (access object library)? I ran into these 2 methods when taking a tutorial on the internet? What are the advantages of each method?
View RepliesI am using data from a sales and a finance system and essentially need to understand the difference between them and account for this "balancing" row.
My data is as follows
TBL_Sales
Month, Product_Type, Factory, Quantity, Sales, Margin
January, A, F123, 100, 1000, 10
January, A, F123, 800, 8000, 80
January, B, F123, 10, 100, 1
January, A, F124, 500, 10000, 1000
February, A, F123, 100, 1000, 10
February, A, F123, 800, 8000, 80
February, B, F123, 10, 100, 1
February, A, F124, 500, 10000, 1000
TBL_Finance
Month, Factory, Quantity, Sales, Margin
January, F123, 1000, 10000, 100
January, F124, 550, 10500, 1000
February, F123, 950, 9500, 95
February, F124, 600, 10000, 1000
I would like a query which would look at the difference between TBL_Sales and TBL_Finance and will then add rows to TBL_Sales to make them balance. In the example above I would want it to add the following (I've used C as a Product_Type to show that it's a manually entered value) -
TBL_Sales
Month, Product_Type, Factory, Quantity, Sales, Margin
January, C, F123, 90, 900, 9
January, C, F124, 50, 500, 0
February, C, F123, 40, 400, 4
February, C, F124, 100, 0, 0
Hi 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
I 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 RelatedWe have an MS Access 2010 Database that uses Local Tables, External MS Access Tables, and ODBC Linked Oracle Tables (Accessed for Read Only). The unsual issue occurs with the ODBC Linked Oracle Tables.
One of the more important aspects of the project is to modify the ODBC Links to point to upgraded Oracle Database Tables. Up until today, all of the Links had been able to be remediated with a simple refresh and test.
This morning, however, it was determined that one of the Database Tables did not exist in the Schema. Instead, it was an Oracle Synonym for a Table that existed in a different Schema. We believe that we have the proper authorization for access to all of the Schemas involved, and despite this fact, MS Access was unable to link to the Table properly.
Hi,
I have built an Access DB containing 3 tables: dimensions, time, companies. The tables are not linked and are to be used to look up values for the new form. The goal is to create an Access form that would allow the user to select distinct values from all 3 tables, enter some own data and then execute an append query to add the record to the main table.
Something like this:
Initially I have 3 tables:
Prepopulated Dimensions table with fields:
dimension
....
Prepopulated Time table with fields:
Date
Day
Month
Year
Prepopulated Companies table with fields:
Company
....
My form is to be able to select distinct values (combobox) from all three fields:
Company
Dimension
Day
Month
Year
Value (data entered by user)
The record then is appended to the Main table containing:
Company
Dimension
Day
Month
Year
Value (data entered by user)
Thanks a lot for your time and help!
Polar
I am creating a table that is a master list of all of my company's product. Each "customer" that we have will always be ordering the same items, but not all of the items that we have available. I need a way to go through the master list and click a yes or no and have that item added to the "customer's list of items on a new table.
I need to create a sublist for each "customer" like individual shopping cats for each customer. These individual lists need to link back to the master list in case of product changes, description changes, and cost changes.
I would like to create a form where the end user can type in a product number, description, or manufacturer number and have that item added to the "customer's" list.
My Main Table in my Database is "Quotation", what do i need to do in order to create a new Main Table called "Quotation Tracker" and "Quotation" would now be under "Quotation Tracker"
View 8 Replies View RelatedI was asked to create a table within an existing database using these SQL commands;
CREATE TABLE Vehicle (reg_no TEXT(10)
CONSTRAINT VehicleKey PRIMARY KEY,
type TEXT(10),
purchase_date DATETIME,
last_service_date DATETIME, mileage_at_last_service INTEGER);
I was told to save the query and check the new table had the required records in it.
I had huge problems when I tried to save the query and kept getting error messages like: "query must have at least one destination field" and "syntax error in field destination". This was strange since I was sure I typed the query exactly as it had been written on the worksheet.
Having not changed the SQL command at all it eventually worked when I skipped the 'save query" stage and just went to the "run query" stage. I still don't know why it actually worked in the end and why I kept getting error messages.By running the query am I supposed to just click on "RUN" or can I check the information first by looking at datasheet view?
I have a Client database table in Access. I now need to add a simple order table (related) to the client table. I have a client ID field set to autonumber in the client table. As I start to create the orders table I'm not sure how to link the two so that I'm not entering data twice and have assurance that they are tied together.
View 4 Replies View RelatedMy application is a series of checklists, represented each by a table. They all have a common "Job ID" which is entered by the user in the first form/table. This "Job ID" is a common value with all other tables.
What I want, is that the user doesn't have to enter the "Job ID" every in every form. I just want him to enter it once in the first menu, and then it's copied automatically to all the other tables (creating a new record in each table).
I 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 ....
I 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 ...
Hi, I have two tables, one being a subset of the other. What I want to do is write a query that lists data in the larger table that is not in the smaller one. In effect I want to subtract the data in the smaller table from the larger table and list the remainder.
I have tried something like:
Select a.oem from a,b where a.oem not in (select b.man from b);
This query does not complete and I get 100% CPU usage :( . The largest of the tables has roughly 4000 records in it. Any ideas? Am I doing something wrong?
Thanks
struggling with this, hope someone can help.
I have a table of timesheets that contains records for staff but it only contains weekly record if they have actually created that weeks record.
I also have a table of weeks that contains all of the available weeks that time can be booked against.
How can I query the timesheet table against the weeks table and get a list of staff that have not created a record for that week ?
I have a field that will only get numeric values, numbers. And it will always have four characters or less. I do have times when I worry my data by over eighty's certain size or I average value, or both. Is this possible when using a text field.
View 14 Replies View RelatedI have a database with two tables, one for the amount that was estimated in each cost section, and one for the actual amount billed for each cost section. The tables have the same number of fields, all with the same names. They can be linked together with event ID. Each table has over 100 fields and I would like to find the difference between what was estimated and what the actual was for each event. I would also like to see which cost section has the most and least variance. I am trying to do this without going through each cost and putting [tEst].[CostName]-[tActual].[CostName].
View 2 Replies View RelatedI have a table that contains a significant amount of data. The columns are as follows;
ID, WeekNo, Ref, Cost
What I want a query to do is only show me differences in Cost value where the Ref is the same
For example
ID, WeekNo, Ref, Cost
1, 1, ABC, 123
2, 1, DEF, 456
3, 2, ABC, 123
4, 2, DEF, 789
The query I would like, would only show me the result of Rows 2 & 4 because the value of cost is different between the weeks.
At the moment I have two queries, One that pulls out the last weeks value (WeekNo 1), and the other that pulls out the same information but for the comparison week (WeekNo 2). In the third query I created I linked the two columns from the Ref fields, but could not work the logic to only show me ones where the differences in cost were displayed.
Thanks in advance of the resolution to this. I know it is basic query work, but just have a logical block in getting it to work.
Regards
I'm trying to make a query where it can calculate the difference between a table field value and a query field value.
Ex. Table INVOICE - fields
invoice no, invoice value,
Query TOTAL - fields
invoice no total invoice value
I need to get the difference between invoice value and total invoice value.
How can I put this into a query (invoice value - total invoice value)?
I am struggling with calculate difference between two query table values, I first created a make a table query(current meter reading) which contains one column called "meter read" , and I created a second make a table query(previous meter reading) which also contains "meter read" column, I linked those two make table queries to calculate the value difference between two date, how can I create a form to calculate diff between any two date?
View 3 Replies View RelatedI want to add three fields in a table namely from, to and diff .
How to add them in a form so that I can enter
The time in (xx:xx) 24hr format in the "from" and "to" fields and calculate and save the time difference in minutes to the "diff" field automatically.
I am using access 2010.
What is the difference between "short text" and "long text"? Both seem to have 255 characters limit. I have Access 2013, and I found a solution, that you can use Memo instead of Short text. But the problem is, that there is no "memo" data type in Access 2013. So that won't work for me.
View 8 Replies View RelatedI 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?
Hiya 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 RelatedGood 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
I 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 Related