Creating a validation rule based on query results.

I have a table that stores personnel assigned to a project and if thy are removed from the project.

Each project has several people filling roles.

Several of the roles can be filled by multiple simultaneously but a few can only be filled by one person at a time.

If a person gets removed from the project, their record gets tagged with a date of removal in a specified date field.

[projectNumber][employee][role][removedDate]

[employee] and [roles] are lookup fields

I have a query that identifies the roles that should have one individual filling that role at a time.

SELECT first(projectNumber) AS pn, employee, role FROM [Table] WHERE projectNumber = "NUMBER" and removeddate is null and (role = 9 or role = 10 or role =2 or role =3 or role =1) GROUP BY sr, role; 

This returns the roles that can only have one person in that role, for a specific project number.

How can I create a validation rule that checks to see if the roles are filled, per project, and returns an error if yes?

submitted by /u/CynicalCanine0
[link] [comments]

Get the best Microsoft Access conultant

Get the best Microsoft Access programmer

Posted on

Relationships to granular for practicality?

Relationships to granular for practicality?

I have two working structures for a database I am beginning to create. One is very granular, and I think the more theoretical approach, and from what I understand of normalization, 3NF. The other structure seems more practical, and could be considered 3NF depending on interpretations. The fields in the tables are just working fields and are not final, and this is just the base structure, much more is planned for data involving employees, calculating payroll, and jobsite information.

Which one would be the best to pursue?

Description: This is for a cleaning service. Service is provided to domestic and commercial job sites (homes and businesses). Each jobsite can have many clients (contacts) that need to be tracked for various reasons (residents, billing, emergency) and these clients can also have different phones also. And some clients can have many jobsites (landlord, apt complex, client paying for personal and relative’s services). Each jobsite has only one address/physical location. 70% jobsites have only one contact and one address, 90% have one address and 2 contacts.

The less complicated diagram is based on following idea. The physical location and jobsite landlines are stored with the jobsite (if relevant), perhaps even a main contact name and personal info. All other contacts are related to the jobsite, and only when information is different than the jobsite information, would data be entered (or duplicated for off-site contacts – a possibility)). This seems conceptually simple enough, not complicated, and easier to manage. And might be considered 3NF if one considers addresses different than the jobsite to be different entities. I know that working with the more granular approach, it was a pain to manually enter data and navigate two junction tables, but I figure that could be rectified with forms, but I couldn’t say how complex those forms might have to be. When I run a query for prices based on names, I do get the same results for both structures, so I least I am duplicating some end results so far.

I am relearning Access. The last time I tried to learn, just did not have things like Reddit, youtube, many discussion groups, Udemy and so far I have jumped farther and faster than just reading books….). I am still rusty on forms and never got to reports and did little with queries. I do not know SQL, but am willing to learn it. I do not want the database to just be an ad hoc of data either, and I hope I could give this database to other people for them to use in their own business (other small franchise). I would also like a structure that would be less tedious to work with when creating reports, queries and forms. Please offer your suggestions.

simple one

too granular?

submitted by /u/Happy__Puppy
[link] [comments]

Get the best Microsoft Access conultant

Get the best Microsoft Access programmer

Posted on

Need advice about best format for a field – quarterly billing period

I’m thinking about how to move a billing details spreadsheet into Access and one of the questions I have is about how to treat a particular field. We enter data into a spreadsheet and then at certain times in the year (Mar, July, Sept, Dec) we take everything from that period and bill for it.

At the moment the field in excel is a date which has formatting/conditional formatting to make it display Dec-19 for example, and means that the filter displays by years and then months which is quite handy. However, it is really just a tag that can be used to filter data by which billing run it was included in.

So I’m trying to decide of I should make the field in Access a date like 01/12/19 or to have it as a text field with just ‘Dec 19’ or something similar.

submitted by /u/garamasala
[link] [comments]

Posted on

Importing very large Excel file into Access

Someone shared a very large (73 MB) Excel file with me on OneDrive. The file exceeds Excel’s row limit so i want to import the data into Access so I can analyze it. When I browse my files within Access to import the file, it doesn’t show up anywhere. Is this because the file is too large? Does anyone know a way around this?

submitted by /u/alexblt
[link] [comments]

Posted on

Moving from _BE data to SQL

I have an access database on a network that I split a while ago to a backend database _be.

I now want to move to SQL but most of the guides refer to splitting from a full database direct to SQL.

How can I move from an already split database to SQL ?

submitted by /u/bitstreams_red
[link] [comments]

Posted on

Moving from _BE data to SQL

I have an access database on a network that I split a while ago to a backend database _be.

I now want to move to SQL but most of the guides refer to splitting from a full database direct to SQL.

How can I move from an already split database to SQL ?

submitted by /u/bitstreams_red
[link] [comments]

Posted on

errorstr to check existing record, how to jump to record if it exists

I currently have a form to add new students to a database, it checks to see if their social security number exists in the database. If it does you get a message stating record exists, I would like to another message to appear and ask the user if they would like to view the record. This is what i have to check the ssn

If DCount("SSAN", "tbl_master_ait", "SSAN = '" & txtSSN & "'") > 0 Then

errorstr1 = "Record already exists. You are not allowed to duplicate records."

Errorint = Errorint + 1

End If

I don’t know where i would put the msgbox for this to only ask to view record if it exist.

Thank you.

submitted by /u/PJ505
[link] [comments]

Posted on

Multible choice form question

Hello. I am currently doing a small project in Access in which I am creating a database for a sportsclub.

The form needs to following questions:

Name (Got it)

Membernumber (Got it)

Birthday (Got it)

City (Made a referance to postal code)

Postal code (You choose city from a premade list and postal code is automatic)

Here comes the tricky one:

You choose one sport (I got this far – It is multible choice)

You need to pick one training time. (I wrote the different trainingtimes for each sport down in a seperet table. How do I make it so I can choose from one of the trainingtimes I in a different table. Like if I choose basketball I could pick between 1 of the 3 basketsessions. If I pick baseball I can pick between 1 of the 4 trainings and so on.

I hope you can help,

Balle 😀

Thank you for reading 😀

Your help is very much appriciated.

submitted by /u/Dypmeister
[link] [comments]

Posted on

Initial set up

Hi all!

I am developing a db for projects that we will populate the amount of individual entries (typically between 50-1500) and then information for those sites will be entered by multiple teams in multiple locations.

As this is my first db development, I am wondering if it is better to have one big table (300 fields) or 6 different tables (50 fields each). Entry wise, it won’t matter. From a structuring point of view, will 6 tables make it infinitely more complicated?

Similarly, will it be easier to create queries with 1 or 6 tables? And finally, if i want basic unique information for each entry form (that was prepopulated in the DB before project kickoff) pulled forward so the staff know some basic information and confirm they are working on the correct entry, is it easier with 1 table or 6?

Thanks guys! You’re the best!

submitted by /u/goblinofthechron
[link] [comments]

Get the best Microsoft Access conultant

Get the best Microsoft Access programmer

Posted on

Datasheet View disable Totals Row

Short explanation at the bottom.

So I’m working on a project that allows multiple users to edit assets, I’ve found/created a way to record the changes to the assets, who changed it, when they changed it, what it was before and after the change. It’s a simple audit trail as a safety precaution and a way to ensure anyone who creates an item or makes changes can be contacted if there was any issues.

So I have a form called Asset Details, it allows users to modify the details and such, within that form I have a history tab that allows a user to switch to that and see all the changes made to that asset.

I have a Subform created that shows all this information per asset in the Datasheet view and since it does not need a fancy appearance or anything like that I prefer the way it looks when it’s just a plain Datasheet view.

However as far as I can tell, it will auto calculate anything in the datasheet view to see either the total value, sum, average, all that stuff. I have been able to disable it counting and such but no matter what I do the first column is always set to Total and I cannot for the life of me figure out how to prevent that from happening.

I just don’t need a totals row because it’s completely irrelevant to the information I’m trying to provide. How do I disable that row from even appearing at all? It’s not relevant to any information I am providing and it’s just completely unnecessary.

TL;dr

The simple explanation is I have a table that I would prefer to have set as a Datasheet so that the information provided on it is simple to read but unfortunately it has a stupid totals row that is completely irrelevant to the information provided by the datasheet.

Is there a way to remove the Totals row from the Datasheet view?

submitted by /u/TakeBeyond
[link] [comments]

Get the best Microsoft Access conultant

Get the best Microsoft Access programmer

Posted on