Form that pulls from query but submits to a table

[Guest]

The setting: Access XP, two tables: IRMAIN and DATECLOSED, one query: IRMAIN Since October

Background: IRMAIN includes many records with fields: IRNum, IRDATE. DATECLOSED has no records but fields: IRNum, DATECLSD. Query filters the data from IRMAIN to just show records after October

The goal: Somehow I need to populate the DATECLOSED table with the filtered IRNUM's from the query as well as be able to manually enter dates to the DATECLSD field. In the end, I want to run a report based on the IRMAIN Since October query which includes the DATECLSD field which relates to the IRNum

Can you help?

 

[Nikos Yannacopoulos]

Carpie,

I have a question on your database design:
I don't know what yor data represents, but trying to read between the lines
of your note I get the feeling that (a) IRNum is a PK in the IRMAIN table,
and (b) you will only ever have one record for each IRNum in your DATECLOSED
table?
Well, if my guess is correct, it makes a lot more sence to add field
DATECLSD to your IRMAIN table and drop DATECLOSED altogether. This will
spare you redundant duplication of data (the PK), and make all amnipulation
of your data (queries, forms, reports) simpler.

HTH,
Nikos

The setting: Access XP, two tables: IRMAIN and DATECLOSED, one query: IRMAIN Since October.

Background: IRMAIN includes many records with fields: IRNum, IRDATE.

DATECLOSED has no records but fields: IRNum, DATECLSD. Query filters the
data from IRMAIN to just show records after October.

The goal: Somehow I need to populate the DATECLOSED table with the

filtered IRNUM's from the query as well as be able to manually enter dates
to the DATECLSD field. In the end, I want to run a report based on the
IRMAIN Since October query which includes the DATECLSD field which relates
to the IRNum.

 

[Guest]

Your guesses are correct. And your solution makes good sense as well but my trouble is that the IRMAIN table is a part of a company Oracle database that I do not have update access to (nor should I). I am using Access to link to the table so that I can create reports based on the information.
Trouble is that I need just a little more info for the report than the IRMAIN has to offer.

 

[Nikos Yannacopoulos]

Carpie,

OK, got you.
First question (new records in DATECLOSED):
Make a copy of your query (to keep the original intact) and in design view
change it to Append query. You will be prompted for a table name; that is
the destination table name, so put DATECLOSED there. Now, in the design
grid, in the column for field IRNum type in the name of the corresponding
field in DATECLOSED in line Append to (if the field name is the same it
comes up automatically). Save the query and run it, and oyu've got your new
records.

Second question (manual entry of dates):
You can do it directly in the table, but you might find it's easier to use a
form. The fastest way to get it is to select your original query (since
October)in the database window and click on button AutoForm in the toolbar
(or from the menu: Insert > Autoform) and let Access do the rest for you.

Third question (Report):
Make a new query combining the two tables (join on fileds IRNUm) and select
both fields from IRMAIN and the date field from DATECLOSED, and use this as
the recordsource for your report. You can also include any filters you may
want (date etc.).
Note: if you want IRMAIN records to appear on the report even before a
record has been added for them in DATECLOSED, then in the query design
double-click on the join line and choose "Select all records from IRMAIN and
only those from DATECLOSED where..." (you will notice that the line becomes
an arrow pointing to DATECLOSED).

HTH,
Nikos

Your guesses are correct. And your solution makes good sense as well but

my trouble is that the IRMAIN table is a part of a company Oracle database
that I do not have update access to (nor should I). I am using Access to
link to the table so that I can create reports based on the information.

Trouble is that I need just a little more info for the report than the

IRMAIN has to offer.