Help a query or VBA

[smtazulislam]

I am working HR dept. and we have govt. approval visa for hire worker from 11 countries right now. A visa numbers have different category and defferent country. In this visa for stamp in the worker passport, we give authorise a local an agency. and Visa work processing all of the cost is credit we will pay when worker is arrival.

So, I want a query for show the form1 that,
Enter Visa Numbers to show
How much visa we Authurised to company and country name ?
How much visa we have still balance ?, which category is balance ?

Also
Total Visa:
Total Authorise :
Total Arrival :
Balance :

Here I attached db. If you thinking I have mistake to arrange table or Form you have permission to edit anywhere ?
Any help will appreciate

 

[arnelgp]

you must put on the the table each visa number and to what country it is supposed to be used?

 

[Pat Hartman]

There are structural issues starting with your use of table level lookups. This might seem to be convenient if you've never developed an Access database but there are many issues you will run into later. The expert opinion is DON'T use them. Put combos on your forms if you want to pick from lists. Do NOT put them on the table. You can find lots of advice to avoid these abominations so I'll leave it at that.

Once you remove the lookups, change the column names of the FK's so that they match the ID name rather than the name of a data field.

Now we get to the schema. I don't really understand it. Some country issues you some number of visas and you get to assign them to individuals. If you want tblVisaDetails, it should contain the common data. Visa quantity should be calculated rather than stored. Then you need tblVisaNumbers. It has an autonumberID and a FK to visaDetails. it also has the visa number and who/when it was assigned.

So you can tell which visas are available by counting the visas with no one assigned to them. I don't know where Agent comes in to this. I am also confused regarding why visas are assigned to companies rather than individuals. I can't tell if YOU are assigned the visas or they belong to some agent.

We really need to know more about where visas come from and how they are consumed.

 

[smtazulislam]

you must put on the the table each visa number and to what country it is supposed to be used?

Thanks for your reply.
How can I do it, Because a visa numbers have many coutries and different visacategory available. See the VisaDetails Table screenshot.

VisaDetails VisaDetails

VDID	VisaID	Country	Approval City	Category	VisaQty
1	1301652881	India	Mumbai	عامل تنظيف مباني عامه​	44​
2	1301652881	Egypt	Cairo	مشرف عمال​	6​
3	1301720226	India	Mumbai	عامل​	30​
4	1301720226	Egypt	Cairo	مشرف عمال​	4​
5	1301835328	Phillippine	Manila	عامل عادي​	18​
6	1301835328	Phillippine	Manila	مهندس مشروع​	2​
7	1301835328	Egypt	Cairo	مراقب عام​	1​
8	1301835328	India	Mumbai	عامل عادي​	15​
9	1301652881	Nepal	Kathmandu	عامل تنظيف مباني عامه​	30​

exp: 1301835328 numbers of Visa have 3 country and 4 categories.
I not understand how I create each visa. if you want to see the original copy of visa I can send to you.

 

[smtazulislam]

There are structural issues starting with your use of table level lookups. This might seem to be convenient if you've never developed an Access database but there are many issues you will run into later. The expert opinion is DON'T use them. Put combos on your forms if you want to pick from lists. Do NOT put them on the table. You can find lots of advice to avoid these abominations so I'll leave it at that.

Once you remove the lookups, change the column names of the FK's so that they match the ID name rather than the name of a data field.

Now we get to the schema. I don't really understand it. Some country issues you some number of visas and you get to assign them to individuals. If you want tblVisaDetails, it should contain the common data. Visa quantity should be calculated rather than stored. Then you need tblVisaNumbers. It has an autonumberID and a FK to visaDetails. it also has the visa number and who/when it was assigned.

So you can tell which visas are available by counting the visas with no one assigned to them. I don't know where Agent comes in to this. I am also confused regarding why visas are assigned to companies rather than individuals. I can't tell if YOU are assigned the visas or they belong to some agent.

We really need to know more about where visas come from and how they are consumed.

Thank you so much for your time and reply.
Actually, before I used it by Excel Sheet. seet attached excel screetshot.

But I now really want to a database to completed this process.
For that Just import first excel file that some edit. So that maybe somewhere have mistake.

Please dont leave. I upload new version and here all of table no more lookups. all of lookup is deleted. change column I does not guess it. I am sorry.

Visadetails table can calculted total visa. You are right. I will deleted this VisaReleaseDetails Table. This is good point your suggestion. Before I never thinking like that.

Those visa has work permit Visa. When we applied for workers to the govt. then they're percentices to calculted and provided visa, But which country give we don't know. After that this visa we will give authorise to a agency for allow to visit saudi embassy for stamp visa in the worker passport. Before authorise we selected worker and contract signed. Still we work 23 agency 11 country. This is short process i explain.

 

[mike60smart]

Hi

Is this what you want to see?

 

[smtazulislam]

Hi

Is this what you want to see?

Thank you so much for reply.
I want to see in the form1 when I enter Visa Numbers that show me as :
* How much visa we Authurised to company and country name ?
* How much visa we have still balance ?, which category is balance ?
But i don't know how to display this.....

Also down need to show
Total Visa:
Total Authorise :
Total Arrival :
Balance :

 

[Gasman]

Hmm, seems o/p did not like the responses here, so cross posted to https://www.accessforums.net/showthread.php?t=82634

 

[Pat Hartman]

How can one visa be for three countries? Countries issue visas. The US issues a visa for entry into the US to visit or to work. This visa would not also get you entry to Canada and Mexico. Are you also saying that the visa is for a specific city? Given the visas I've been issued, including work visas, they don't limit me to a specific city. A tourist visa allows country-wide access but a work visa might be controlled by my employer though. That means that if I stop working for CompanyA, I can't just go to CompanyB because the visa might be assigned to me but it isn't issued to me, it is issued to my employer and I don't actually "own" it so it is not transferrable. I also think that some classes of work visas are probably issued directly to individuals allowing them to work for any company that will hire them.
Please explain.

I am not a subject matter expert but based on my actual experience with visas, I don't believe the schema is correct. We need more information regarding your companies position in this process. Are you hiring subcontractors? Are you hiring people who actually work for a different company?

 

[smtazulislam]

How can one visa be for three countries? Countries issue visas. The US issues a visa for entry into the US to visit or to work. This visa would not also get you entry to Canada and Mexico. Are you also saying that the visa is for a specific city? Given the visas I've been issued, including work visas, they don't limit me to a specific city. A tourist visa allows country-wide access but a work visa might be controlled by my employer though. That means that if I stop working for CompanyA, I can't just go to CompanyB because the visa might be assigned to me but it isn't issued to me, it is issued to my employer and I don't actually "own" it so it is not transferrable. I also think that some classes of work visas are probably issued directly to individuals allowing them to work for any company that will hire them.
Please explain.

I am not a subject matter expert but based on my actual experience with visas, I don't believe the schema is correct. We need more information regarding your companies position in this process. Are you hiring subcontractors? Are you hiring people who actually work for a different company?

Thank you so much for reply. Yes, you are right for Europe Visa processing like that. But Middle East (Arab Country) work visa is little different, and some condition added here.
First time we applying for work Visa to govt. office. After that they're review company work and projects then provide some visa. Sometime A VisaNumbers have 3 or 4 or 5 Country work Visa added. Sometime one country. Its all of defended on local govt. minded. we just required a quantity Visa only. but Approved visa which country & city we dont know before published visa in the online.

Then when we get visa then we finding same country the workers via a local Manpower agency. Sometime we will personality visit this country for review CV and interview worker. and sometime we social network to take interview.

Why we find manpower agency ? because, selected worker stamping visa in they're passport.
Stamp visa needs some document to processing those work. we give them POWER OF ATTORNEY. Actually this agency is work same like our representative.

 

[Pat Hartman]

What countries are issuing visas for work in other countries. I just don't see how that happens. How can the UAE offer visas for Bahrain? The UAE is multiple countries. Is that what you are talking about? If so, treat them as individual visas. One for Dubai. The second for Abu Dhabi.