473,847 Members | 1,445 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Sql Query Question

Help needed for project-Access 2002(office xp)

PROBLEM: Figuring out how to lookup a record and DDate field in Table1 -
Take that DDate-field data from record looked up and assign it to Date field
in a new record on a form using Table2.

BASIC
Table1
Fields: DID -Primary KEY
DDate --Information I need to move to new form

The Form uses :
Table2
Fields: IID -Autonumber Primary Key
ID
Date- -Where I need to move the data to
Name
Street
ECT-

I have the form that uses Table 2- It lookups the name,street etc from a
customer file (Table3, but not related to this problem), The form is an
invoice entry and the invoices are written sometimes days in advance.. So I
was going to write the date to a table and access it from a lookup on each
form. (Also it will help me access other data in other tables by learning
how to do this)

Any help is appreciated.. Also is there a good reference for understanding
how to get to data generated by a query, forms and tables? I have 3
reference books including Access 2002 Bible,How to do Everything with Access
2002, and Microsoft Access Inside Out-2002. None of these really have any
kind of simple and direct explaination of how to do this..
I haven't really used the visual programming, Old Basic programs seem
soooo... much easier to control-program.


Nov 12 '05 #1
3 1776
Is there a relationship between Table 1 and Table 1? If so, follow up here.

First thing... don't use "Date" as a Field name -- it is an Access reserved
word, and sooner or later, it's going to cause confusion that will be very
puzzling.

If not, then put a ComboBox on the Form, using Table 1 as the RowSource,
with the Date field in Table 2 as the Control Source. Use the Wizard to make
it easy to build and you can show whichever field will be meaningful to the
user, and both fields in the dropdown list.

Larry Linson
Microsoft Access MVP
"JC Mugs" <jc****@hotmail .com> wrote in message
news:vv******** ****@corp.super news.com...
Help needed for project-Access 2002(office xp)

PROBLEM: Figuring out how to lookup a record and DDate field in Table1 -
Take that DDate-field data from record looked up and assign it to Date field in a new record on a form using Table2.

BASIC
Table1
Fields: DID -Primary KEY
DDate --Information I need to move to new form

The Form uses :
Table2
Fields: IID -Autonumber Primary Key
ID
Date- -Where I need to move the data to
Name
Street
ECT-

I have the form that uses Table 2- It lookups the name,street etc from a
customer file (Table3, but not related to this problem), The form is an
invoice entry and the invoices are written sometimes days in advance.. So I was going to write the date to a table and access it from a lookup on each
form. (Also it will help me access other data in other tables by learning
how to do this)

Any help is appreciated.. Also is there a good reference for understanding how to get to data generated by a query, forms and tables? I have 3
reference books including Access 2002 Bible,How to do Everything with Access 2002, and Microsoft Access Inside Out-2002. None of these really have any
kind of simple and direct explaination of how to do this..
I haven't really used the visual programming, Old Basic programs seem
soooo... much easier to control-program.

Nov 12 '05 #2
Sorry, Mistyped the explanation,
The form & Table use IDate for "Invoice Date" As for relationship, only
that the DDate field will be imported(????) into the Form Record.. I am
Hoping to use a technique to lookup and update the field with no user
intervention. I thought a SQL procedure, would do it.
"Larry Linson" <bo*****@localh ost.not> wrote in message
news:mD******** **********@nwrd dc01.gnilink.ne t...
Is there a relationship between Table 1 and Table 1? If so, follow up here.
First thing... don't use "Date" as a Field name -- it is an Access reserved word, and sooner or later, it's going to cause confusion that will be very
puzzling.

If not, then put a ComboBox on the Form, using Table 1 as the RowSource,
with the Date field in Table 2 as the Control Source. Use the Wizard to make it easy to build and you can show whichever field will be meaningful to the user, and both fields in the dropdown list.

Larry Linson
Microsoft Access MVP
"JC Mugs" <jc****@hotmail .com> wrote in message
news:vv******** ****@corp.super news.com...
Help needed for project-Access 2002(office xp)

PROBLEM: Figuring out how to lookup a record and DDate field in Table1 -
Take that DDate-field data from record looked up and assign it to Date field
in a new record on a form using Table2.

BASIC
Table1
Fields: DID -Primary KEY
DDate --Information I need to move to new form

The Form uses :
Table2
Fields: IID -Autonumber Primary Key
ID
Date- -Where I need to move the data to
Name
Street
ECT-

I have the form that uses Table 2- It lookups the name,street etc from a
customer file (Table3, but not related to this problem), The form is an
invoice entry and the invoices are written sometimes days in advance.. So I
was going to write the date to a table and access it from a lookup on

each form. (Also it will help me access other data in other tables by learning how to do this)

Any help is appreciated.. Also is there a good reference for

understanding
how to get to data generated by a query, forms and tables? I have 3
reference books including Access 2002 Bible,How to do Everything with

Access
2002, and Microsoft Access Inside Out-2002. None of these really have any kind of simple and direct explaination of how to do this..
I haven't really used the visual programming, Old Basic programs seem
soooo... much easier to control-program.


Nov 12 '05 #3
You should use a query to get the Invoice Date to be displayed. Also, save
looping for last resort in any case - chances are high that you can always
accomplish it with a sql statement call.

For instance, in code you do this... (watch for word wrapping)
Me.txtInvDate = CurrentProject. Connection.Exec ute("Select IDate from Table1
INNER JOIN Table2 ON Table1.DID = Table2.ID Where Table2.ID = " & Me.IID &
"").Fields(0).V alue

The "CurrentProject .Connection.Exe cute" is a hot connection to your data and
allows you to execute sql syntax.
The ".Fields(0).Val ue" is asking for the value of field 0 (which is IDate)
and assigns back to a text field on the form.
This is a standard ADO connection method call, very simple to do.
Now... that said... ideally (I think... in your situation), your form
RecordSource property should be a query that gives you the IDate field.
Something like...
SELECT *
FROM Table1 INNER JOIN Table2 ON Table1.DID = Table2.ID;
--
Jerry Boone
Analytical Technologies, Inc.
http://www.antech.biz
Secure Hosting and Development Solutions for ASP, ASP.NET, SQL Server, and
Access

"JC Mugs" <jc****@hotmail .com> wrote in message
news:vv******** ****@corp.super news.com...
Sorry, Mistyped the explanation,
The form & Table use IDate for "Invoice Date" As for relationship, only
that the DDate field will be imported(????) into the Form Record.. I am
Hoping to use a technique to lookup and update the field with no user
intervention. I thought a SQL procedure, would do it.
"Larry Linson" <bo*****@localh ost.not> wrote in message
news:mD******** **********@nwrd dc01.gnilink.ne t...
Is there a relationship between Table 1 and Table 1? If so, follow up

here.

First thing... don't use "Date" as a Field name -- it is an Access

reserved
word, and sooner or later, it's going to cause confusion that will be very
puzzling.

If not, then put a ComboBox on the Form, using Table 1 as the RowSource,
with the Date field in Table 2 as the Control Source. Use the Wizard to

make
it easy to build and you can show whichever field will be meaningful to

the
user, and both fields in the dropdown list.

Larry Linson
Microsoft Access MVP
"JC Mugs" <jc****@hotmail .com> wrote in message
news:vv******** ****@corp.super news.com...
Help needed for project-Access 2002(office xp)

PROBLEM: Figuring out how to lookup a record and DDate field in Table1 - Take that DDate-field data from record looked up and assign it to Date

field
in a new record on a form using Table2.

BASIC
Table1
Fields: DID -Primary KEY
DDate --Information I need to move to new form

The Form uses :
Table2
Fields: IID -Autonumber Primary Key
ID
Date- -Where I need to move the data to
Name
Street
ECT-

I have the form that uses Table 2- It lookups the name,street etc from a customer file (Table3, but not related to this problem), The form is an invoice entry and the invoices are written sometimes days in advance..

So
I
was going to write the date to a table and access it from a lookup on

each form. (Also it will help me access other data in other tables by learning how to do this)

Any help is appreciated.. Also is there a good reference for

understanding
how to get to data generated by a query, forms and tables? I have 3
reference books including Access 2002 Bible,How to do Everything with

Access
2002, and Microsoft Access Inside Out-2002. None of these really have any kind of simple and direct explaination of how to do this..
I haven't really used the visual programming, Old Basic programs seem
soooo... much easier to control-program.



Nov 12 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

9
3414
by: majsen | last post by:
Hi, I have problem running this query. It will time out for me... My database are small just about 200 members. I have a site for swaping appartments (rental). my query should look for match in a triangle. Like this member A -> B->C A give his appartment to B. B gives his appartment to C and finally C gives his appartment to A Soo my query looks for matching parameters like rooms, location, size
8
3278
by: Együd Csaba | last post by:
Hi All, how can I improve the query performance in the following situation: I have a big (4.5+ million rows) table. One query takes approx. 9 sec to finish resulting ~10000 rows. But if I run simultaneously 4 similar queries it takes nearly 5 minutes instead of 4 times 9 seconds or something near of that. here is a sample query: select mertido, fomeazon, ertektipus, mertertek from t_me30 where fomeazon in (select distinct fomeazon...
3
14133
by: John Ortt | last post by:
> I have a table of dates in ascending order but with varying intervals. I > would like to create a query to pull out the date (in field 1) and then pull > the date from the subsequent record (and store it in field 2). > > I would like to run a query that returns all the data in the table plus the > record number, in a similar sense to the getuser() command to get the User's > Identity, I would like a GetRecord() command.
3
3098
by: Ekqvist Marko | last post by:
Hi, I have one Access database table including questions and answers. Now I need to give answer id automatically to questionID column. But I don't know how it is best (fastest) to do? table before rowID answID qryrow questionID datafield 1591 12 06e 06e 06e question 1593 12 06f 06f 06f question 1594 12 answer to the question 06f
7
3395
by: serge | last post by:
How can I run a single SP by asking multiple sales question either by using the logical operator AND for all the questions; or using the logical operator OR for all the questions. So it's always either AND or OR but never mixed together. We can use Northwind database for my question, it is very similar to the structure of the problem on the database I am working on. IF(SELECT OBJECT_ID('REPORT')) IS NOT NULL DROP TABLE REPORT_SELECTION
6
4855
by: jjturon | last post by:
Can anyone help me?? I am trying to pass a Select Query variable to a table using Dlookup and return the value to same select query but to another field. Ex. SalesManID SalesManName AT Alan Time
2
2046
by: mmitchell_houston | last post by:
I'm working on a .NET project and I need a single query to return a result set from three related tables in Access 2003, and I'm having trouble getting the results I want. The details: Question ------------ QuestionID QuestionText Question_MediaTypeID
22
31222
by: Stan | last post by:
I am working with Access 2003 on a computer running XP. I am new at using Access. I have a Db with a date field stored as mm/dd/yyyy. I need a Query that will prompt for the month, ie. 6 for June, and will return all records in that month.
3
2068
by: Richard Hollenbeck | last post by:
I am very sorry about the (almost) re-post, but you will see that my first question wasn't very clear; I have another question I posted this morning called, "in DAO: Run time error 3061 Too few parameters...." I have read many articles on the web about how to make a dynamic report based on a cross-tab query. But for some reason mine never works right. First, my saved query's criteria is the data in an open form's combo box. So the...
16
3529
by: ARC | last post by:
Hello all, So I'm knee deep in this import utility program, and am coming up with all sorts of "gotcha's!". 1st off. On a "Find Duplicates Query", does anyone have a good solution for renaming the duplicate records? My thinking was to take the results of the duplicate query, and somehow have it number each line where there is a duplicate (tried a groups query, but "count" won't work), then do an update query to change the duplicate to...
0
9734
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10991
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10653
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10718
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9490
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
7061
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5725
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
2
4129
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3168
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.