allocation exercises

G

gadflyjs

Dear All,

I am working for a education organisation. I want to create a database to
store applications, and services allocated to these applicants in separated
tables, and all other related information.

I store the application in one table, and when allocate services to that
applicant, a new entry will be add to the "services" table. But how could I
"allocate" the servces faster, as there maybe over a thousand application and
services? I mean I don't want to copy some information of application to the
"services" table one by one by hand, and if possible, allocate services to
several similiar applications at te same time. Any suggestion? any samples I
could follow? I don't know anything about VBA, I can only use macro, and
function of access.

Thank you for your attention.

gadfly
 
B

Brendan Reynolds

gadflyjs said:
Dear All,

I am working for a education organisation. I want to create a database to
store applications, and services allocated to these applicants in
separated
tables, and all other related information.

I store the application in one table, and when allocate services to that
applicant, a new entry will be add to the "services" table. But how could
I
"allocate" the servces faster, as there maybe over a thousand application
and
services? I mean I don't want to copy some information of application to
the
"services" table one by one by hand, and if possible, allocate services to
several similiar applications at te same time. Any suggestion? any samples
I
could follow? I don't know anything about VBA, I can only use macro, and
function of access.

Thank you for your attention.

gadfly
Click to expand...


It's difficult to provide a specific answer without knowing a lot more about
your application and your database, but generally speaking to insert or
update multiple records you might want to consider using an append or update
query. Here's a link to an online help topic on creating append queries ...

http://office.microsoft.com/client/helppreview.aspx?AssetID=HA100765261033&ns=MSACCESS&lcid=2057

.... and here's a link to the online help topic on creating update queries
.....

http://office.microsoft.com/client/helppreview.aspx?AssetID=HA100765271033&ns=MSACCESS&lcid=2057
 
L

Larry Daugherty

Your haven't specified much of anything but could you use Update
Queries in those situations?

HTH
 
B

Bob Quintal

Dear All,

I am working for a education organisation. I want to create a
database to store applications, and services allocated to these
applicants in separated tables, and all other related information.

I store the application in one table, and when allocate services
to that applicant, a new entry will be add to the "services"
table. But how could I "allocate" the servces faster, as there
maybe over a thousand application and services? I mean I don't
want to copy some information of application to the "services"
table one by one by hand, and if possible, allocate services to
several similiar applications at te same time. Any suggestion? any
samples I could follow? I don't know anything about VBA, I can
only use macro, and function of access.

Thank you for your attention.

gadfly
Click to expand...

You should consider a three table setup, your applicant table, a
service definition table and a table which just creates a many to
many link between the two. This third table would contain only two
fields, the Primary keys of each of the other two tables.

You could then build a form with the service definitions in a
listbox, and the applixcants in a second listbox.
Then a small amount of code takes the multiple selections from each
of the two listboxes and creates the rows required in the third
table.

Google many to many relations MS-Access for lots of examples
 
G

gadflyjs

Thank you Brendan. Your soltion match my sitution very much.
But one of the most troublesome thing is, not all the applications (even
after using query to sort and filter) would be allocated with services even
they are all qualify for the services. Than means if I use append query to
append, some applicatoin that will not be allocated with any services would
be assigened.
I am picturing a form or something, that allow me to put a tick on the
application that would be allocated with services (also a "tick all" box)
after doing some queries, then press a "append" button to append all the
selected records. Is that any more suggestion?

Thanks again to teach me something new to me. Also thank you for the others
who response my question.
 
G

gadflyjs

Guys, the prblem solve (sort of).
I added a "Yes/No" filed in the application table, and use it to select the
records I want to append after filtering.

But still, other more appropriate methods are welcome.
 
Top