Help with new project

[Jm24Cule]

Hi, I'm not new in MS Access but never used for what I want now. First of all, my english is not so good so... sorry

My uncle have a little business, and he makes all the contracts manually, by hand ... He asked me for a program or something to do it easily. My question is:

Is Access good for creating contracts (with reports I suppose) taking data from querys made by him from a form selecting "buyer" and "seller" data (previously saved in records of couse)...

Is it a good idea or should I look for another app or solution for him?


Thanks!

 

[NauticalGent]

Access is exactly what you need and there are many examples database templates to get you started...

And welcome to AWF!

 

[theDBguy]

Hi. Welcome to AWF!


Access can do a lot of things, but it's primarily for storing and managing relational information. For example, maybe you can use Access to keep track of projects and customer information, but maybe use Word to generate the contracts using MailMerge.

 

[Jm24Cule]

Hi. Welcome to AWF!


Access can do a lot of things, but it's primarily for storing and managing relational information. For example, maybe you can use Access to keep track of projects and customer information, but maybe use Word to generate the contracts using MailMerge.

I'll check MailMerge thanks. But for a very unexperienced user like him, could I make what I said with forms (one for adding sellers, other for buyers, other for contracts...) and then by a form where he selects NIF (for example as secondary key "clave secundaria in spanish") he could make a query wich opens a report with the structure of a contract right? I've done the tables, forms for adding and making now the contracts form making. I'll post my results here thanks

 

[theDBguy]

I'll check MailMerge thanks. But for a very unexperienced user like him, could I make what I said with forms (one for adding sellers, other for buyers, other for contracts...) and then by a form where he selects NIF (for example as secondary key "clave secundaria in spanish") he could make a query wich opens a report with the structure of a contract right? I've done the tables, forms for adding and making now the contracts form making. I'll post my results here thanks

Right. I view "contacts" as documents. For instance, Contract #1 might say something like:

This contract is between ThisCompany and CustomerName. The purpose of which is to DoStuff. This contract will expire on CompletionDate. My lawyer and his lawyer agreed that we're cool.

In other words, all the verbose stuff. So, in your database, you might keep track info about CustomerName and have a history contracts performed for said customer, such as Contract#1 and Contract#2, and so on. But the details of the contract will be in the document. You can even "link" the document location in your database record, so it makes it easier to find.

 

[Jm24Cule]

Right. I view "contacts" as documents. For instance, Contract #1 might say something like:In other words, all the verbose stuff. So, in your database, you might keep track info about CustomerName and have a history contracts performed for said customer, such as Contract#1 and Contract#2, and so on. But the details of the contract will be in the document. You can even "link" the document location in your database record, so it makes it easier to find.

Interesting, ill give it a try tomorrow, thanks

 

[theDBguy]

Interesting, ill give it a try tomorrow, thanks

You're welcome. Good luck! Let us know how it goes.

 

[Deleted member 147267]

Hi. Welcome to AWF!
but maybe use Word to generate the contracts using MailMerge.

Not sure I'd agree with that sentiment without knowing more about what the contract form looks like. Given that the related data capabilities are of any db, it might be a perfect solution without adding the complexity of bringing Word into the mix. I have developed complicated reports that mirrored forms that mirrored shipping manifests, which permitted users to replicate a 5 carbon copy form that you usually couldn't read anything on the 5th layer. So there may be a similar opportunity here.

 

[Jm24Cule]

Not sure I'd agree with that sentiment without knowing more about what the contract form looks like. Given that the related data capabilities are of any db, it might be a perfect solution without adding the complexity of bringing Word into the mix. I have developed complicated reports that mirrored forms that mirrored shipping manifests, which permitted users to replicate a 5 carbon copy form that you usually couldn't read anything on the 5th layer. So there may be a similar opportunity here.

The document (currently a Word file) is a simple text where my uncle have to type manually everytime he prints one, the name, nif, and other data of both seller and buyer, and the stuff is sold. My idea is to create a report o use MailMerge to get the contract easy for him. Just one click and from a SQL query (macro implemented in form where he select the correct contract) direct to report or docx

 

[mike60smart]

Hi

Can you upload a copy of the Word Document for us to study?

 

[Jm24Cule]

This for example: The data is missing would be implemented automatically from a button (as I said before, with a macro) wich makes a SQL query from a table wich data "contract" where only appears NIF (with relation in another table and its complete data "buyer" and "seller").

 

[mike60smart]

Hi

So all of the paragraphs are static and the blanks would be fields from your database.

This is very easy to create in Access.

 

[mike60smart]

Hi

Can you explain in as much detail as possible how your process which includes Buyer , Seller and Contracts works currently.

Explain the Steps your Uncle goes through to generate a Contract.

If you think along these lines:-

A Customer Orders a number of Items and an Invoice is prepared.

 

[Jm24Cule]

Okay, I dunno very much about his job but the few he told me for this "program" he wants. An example:

Row "Buyer" (nif, name, address)
Row "Seller" (nif, name, address)
Row "Product" (id, description, price, quantity... (maybe more, but need to ask him))
Row "Contract" (buyer_nif, seller_nif, product_id... (these three are related with above), )

The idea is making a button with a macro in a form, wich executes a SQL Query taking any "Contract" (selected in that form) as base, but taking all relevant data as "nif, "name", "address" of both seller and buyer, just like the product they are trading (dont remember very well how to do in SQL but I'll study it again ... i was good in SQL at school hahaha).

What I've done:

- Rows of everything above mentioned
- Forms to add new seller, buyer, contract, and product (modal form)
- Viewer of current contracts with search box for easy finding (under construction, need to create the button I need now for create automated document with data)


Thanks for your replies!

 

[mike60smart]

Hi

Can you upload a zipped copy of your database?

 

[Jm24Cule]

Hi

Can you upload a zipped copy of your database?

I "translated" the rows and so for making understanding posible. If needed, i'll translate and upload when I can

 

[Jm24Cule]

There is:

mega[dot]nz/#!0FVzFawS!nDs2nsEMW-

I cant upload links with less than 10 messages so... hahaha

R15Suw0njA2Ikdb1uGKhI7CM3hpx68dAI

 

[Jm24Cule]

Okay I'm facing problems hahaha...

I made a query wich works (it takes the data I want to move to a report or word document) from "create query" ,menu. But there are two problems:

1 - Cannot see the way to make a simple button with macro wich creates that query

2 - Im interested in take the data of the only one contract im viewing... I mean, I have my form, wich indicates NIF for both seller and buyer, and i want to make the query taking [WHERE contracts.buyer = "49235224A" & contracts.seller= "49235224B"] this line direct from the data displayed on current form. In this case, the form shows me these NIF and make that inner join to take cross table query.

Im only capable of making as a single sql query but my uncle need a button, even that is "complicated" for his age hahaha. How can I move that query into a button, and then into a word document?

Need help


EDIT: Okay, I made a button wich opens a "dynamic query", but not with the data I want. I use:

SELECT DISTINCT buyers.*, sellers.*
FROM buyers, sellers, contracts
WHERE (contracts.buyer)=[forms]![my_contracts]![buyer]) AND (contracts.seller)=[forms]![my_contracts]![seller];

I want only get the data of ONE seller and ONE buyer, wich appears in current form ("(contracts.seller)=[forms]![my_contracts]![seller]") but it shows me multiple same results like this:

seller_name | buyer_name | anyotherrow
-------------------------------------------------
robin------------batman--------anything
robin------------batman--------anything
robin------------batman--------anything

 

[Deleted member 147267]

Post back just saying "I will post a db copy next." and that should get you the 10 posts you need to upload a db. This db should have data that we can use. Suggest you also add a Word document or something that allows us to distinguish between what's often called boiler plate text (the stuff that never changes) from the data that will come from your sample db. The data in the contract document should be what's in your table(s) if possible. As noted, what you've presented seems quite doable but I think there's a bit of an information gap on our end.